Обсуждение: Re: [QUESTIONS] Business cases
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>
> > > 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
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...
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