--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:53 PM 15/08/2004 -0400, you wrote:
> > > the statement
> > > INSERT INTO T1 SELECT * FROM T1 (duplicating T1)
> > > causes (in Firebird 1.5.1, maybe in IB too) closed loop.
> > >
> > > PostgreSQL works correctly!
>
> At 12:53 PM 15/08/2004 -0400, Sean Leyne wrote:
>
> >Yes, this an old/long standing bug with FB and IB (all versions).
>
> ...while others would say it was a crippling bug if database rules
> prevented a transaction from seeing its own uncommitted work.
>
> What does "PostgreSQL works correctly" really indicate here?

Relational

> rules don't encourage exact duplicate records: what can a query

engine do

> with them? From the point of view of relational integrity, PG

breaks the

> rule by permitting it and Fb/IB honours it by allowing it to

recurse until

> it inevitably fails. In PG you end up with duplicate rows. In

Fb/IB you

> end up with no duplicate rows and an exception.
>
> On a keyed table, the constraint would prevent you from duplicating

any row

> using this statement.
>
> Where there's a need to duplicate data, there are techniques to

enable you

> to do it without jeopardising the integrity of the table. For

example,

> it's not a problem where you have an auto-incrementing primary key

on the

> table and use an appropriate WHERE clause to limit the rows

selected.

>
> INSERT INTO T1 (<list of columns excluding the primary key>)
> SELECT <list of columns excluding the primary key> FROM T1
> WHERE ThePrimaryKey <= <maximum key value you want duplicates for>
>
> There are other techniques for doing INSERT...SELECT duplication
> safely. Depending on the requirements, you might need to apply

protective

> conditions to the transaction as well.
>
> Erti, if you're seriously looking for a solution to an actual

requirement,

> ask again and provide a contextual description of what you want to

do.

>
> /heLen

Helen,

It's not really a problem, thus I don't need any solution. You are
right from the point of view of relational integrity. But, I find not
OK, if a sql statement (his implementation) create closed loop.
It was only a hint, no more.

Thanx for your detailed answer.

Erti