
Moved to pgsql-hackers@postgresql.org, where it should have been moved
*ages* ago


On Wed, 21 Jan 1998, Igor Sysoev wrote:

> > The result you're seeing is, IMHO, *correct*.
> > 
> > The first row in the table, when the update is undertaken, produces a 
> > duplicate key.  So you are getting a complaint which you SHOULD receive,
> > unless I'm misunderstanding how this is supposed to actually work.
> > 
> > The "update" statement, if it is behaving as an atomic thing, effectively
> 
> > "snapshots" the table and then performs the update.  Since the first 
> > attempted update is on the first row it "finds", and adding one to it 
> > produces "3", which is already on file, I believe it should bitch - 
> > and it does.
> 
> I'm not SQL guru and cannot tell how it must be.
> But it seems that Oracle and Solid allows update primary keys such way.

Connected to:
Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.3.0.0 - Production

SQL> create table one ( a integer primary key not null );

Table created.

SQL> insert into one values (2);

1 row created.

SQL> insert into one values (3);

1 row created.

SQL> insert into one values (1);

1 row created.

SQL> select * from one;

         A
----------
         2
         3
         1

SQL> update one set a=a+1;

3 rows updated.

SQL> select * from one;

         A
----------
         3
         4
         2

SQL> 



Wed, 21 Jan 1998 13:01:10 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [QUESTIONS] Business cases
 > 
> 
> Moved to pgsql-hackers@postgresql.org, where it should have been moved
> *ages* ago
> 
> Connected to:
> Oracle7 Server Release 7.3.3.0.0 - Production Release
> With the distributed, replication and parallel query options
> PL/SQL Release 2.3.3.0.0 - Production
> 
> SQL> create table one ( a integer primary key not null );
> 
> Table created.
> 
> SQL> insert into one values (2);
> 
> 1 row created.
> 
> SQL> insert into one values (3);
> 
> 1 row created.
> 
> SQL> insert into one values (1);
> 
> 1 row created.
> 
> SQL> select * from one;
> 
>          A
> ----------
>          2
>          3
>          1
> 
> SQL> update one set a=a+1;
> 
> 3 rows updated.
> 
> SQL> select * from one;
> 
>          A
> ----------
>          3
>          4
>          2
> 

Man, how do you implement that behavior?  No wonder MySQL fails on it
too.


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Wed, 21 Jan 1998 13:35:38 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [QUESTIONS] Business cases
On Wed, 21 Jan 1998, Bruce Momjian wrote:

>  > 
> > 
> > Moved to pgsql-hackers@postgresql.org, where it should have been moved
> > *ages* ago
> > 
> > Connected to:
> > Oracle7 Server Release 7.3.3.0.0 - Production Release
> > With the distributed, replication and parallel query options
> > PL/SQL Release 2.3.3.0.0 - Production
> > 
> > SQL> create table one ( a integer primary key not null );
> > 
> > Table created.
> > 
> > SQL> insert into one values (2);
> > 
> > 1 row created.
> > 
> > SQL> insert into one values (3);
> > 
> > 1 row created.
> > 
> > SQL> insert into one values (1);
> > 
> > 1 row created.
> > 
> > SQL> select * from one;
> > 
> >          A
> > ----------
> >          2
> >          3
> >          1
> > 
> > SQL> update one set a=a+1;
> > 
> > 3 rows updated.
> > 
> > SQL> select * from one;
> > 
> >          A
> > ----------
> >          3
> >          4
> >          2
> > 
> 
> Man, how do you implement that behavior?  No wonder MySQL fails on it
> too.

	I don't know...the one suggestion that was made seemed to make
about the most sense...

	If update is atomic, then it should allow you to change all the
resultant fields and then try to commit it.  After all the fields are
changed, then it becomes 3,4,2 instead of 2,3,1, and, therefore, is all
unique...



Wed, 21 Jan 1998 13:38:07 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [QUESTIONS] Business cases
The Hermit Hacker wrote:
> 
> 
> Moved to pgsql-hackers@postgresql.org, where it should have been moved
> *ages* ago
> 
> 
> On Wed, 21 Jan 1998, Igor Sysoev wrote:
> 
> > > The result you're seeing is, IMHO, *correct*.
> > > 
> > > The first row in the table, when the update is undertaken, produces a 
> > > duplicate key.  So you are getting a complaint which you SHOULD receive,
> > > unless I'm misunderstanding how this is supposed to actually work.
> > > 
> > > The "update" statement, if it is behaving as an atomic thing, effectively
> > 
> > > "snapshots" the table and then performs the update.  Since the first 
> > > attempted update is on the first row it "finds", and adding one to it 
> > > produces "3", which is already on file, I believe it should bitch - 
> > > and it does.
> > 
> > I'm not SQL guru and cannot tell how it must be.
> > But it seems that Oracle and Solid allows update primary keys such way.
> 
> Connected to:
> Oracle7 Server Release 7.3.3.0.0 - Production Release
> With the distributed, replication and parallel query options
> PL/SQL Release 2.3.3.0.0 - Production
> 
> SQL> create table one ( a integer primary key not null );
> 
> Table created.
> 
> SQL> insert into one values (2);
> 
> 1 row created.
> 
> SQL> insert into one values (3);
> 
> 1 row created.
> 
> SQL> insert into one values (1);
> 
> 1 row created.
> 
> SQL> select * from one;
> 
>          A
> ----------
>          2
>          3
>          1
> 
> SQL> update one set a=a+1;
> 
> 3 rows updated.
> 
> SQL> select * from one;
> 
>          A
> ----------
>          3
>          4
>          2
> 
> SQL> 

I have been "lurking" on the pgsql-hackers list for a couple of days,
but thought I'd help where I can.  I tried your above example on
Sybase, and got the same results.  The only difference was that the
items were always returned from the table "one" in sorted order rather
than in insertion order.

I also tried a slight modification to your query:

update one set a=a+1 where a<3;

This produces an error as would be expected:

Attempt to insert duplicate key row in object 'one' with unique index
'one_a_8473420831'
Command has been aborted.
(0 rows affected)


Ocie Mitchell


Wed, 21 Jan 1998 12:15:13 -0800 (PST)
ocie@paracel.com
Re: [HACKERS] Re: [QUESTIONS] Business cases