Re: [HACKERS] Re: [QUESTIONS] Business cases

The Hermit Hacker wrote:
>
>
> Moved to pgsql-hackers(at)postgresql(dot)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