On Thu, Dec 18, 2008 at 9:34 PM, Thomas Woinke <thomas.woinke@...> wrote:

> Hi,
>
> I just noticed something strange and thought I might ask here if this
> is expected behaviour.
>
> Sometimes I do something like
> insert into <table> (<field1>,<field2>, <field3>, ...) select * from <table>
> in Oracle to quickly generate tables with lots of test data.
>
> Now I tried the same in Firebird (2.5.0 Alpha 1 SuperClassic and 2.1.1
> SuperServer and Classic)
>
> SQL> create database '/var/db/firebird/foo.fdb' page_size 8192 default
> character set UTF8;
> SQL> create table foo (id integer not null);
> SQL> insert into foo (id) values (1);
> SQL> insert into foo (id) values (2);
> SQL> insert into foo (id) values (3);
> SQL> commit;
> SQL> select * from foo;
>
> ID
> ============
> 1
> 2
> 3
>
> SQL> insert into foo (id) select id from foo;
> At this point, Firebird hung, and the file /var/db/firebird/foo.fdb
> kept growing rapidly. I had to kill the fbserver process.
>
> I am not sure if this is works like expected, since it is easy to
> bring a Firebird Server down that way.

I don't really know how Firebird processes this internally, but I
would guess that supposing that it does execute in the following
order:

1) select row from foo
2) insert the resulting row into foo

you will get into an infinite loop because the number of records in
your table will grow, and your select will continue to get new records
as they are added. But that is just a wild guess. I think someone that
knows how the engine works internally would be the best to explain
that.

Now, there is something you could do to solve your problem. You could
create a stored procedure that generates the data for you. You can
create a loop that generates teh data to be inserted into your table.
Or you can try some tool that generates the data for you. If you try
to ask firebird-tools, someone may be able to help you with that.

In the meantime, I would suggest you to give it a go with stored procedures.

Fidel.