pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Are SQL commands "atomic" ?
4+ messages / 3 participants
[nested] [flat]

* Are SQL commands "atomic" ?
@ 2001-06-07 06:10 Gerald Gutierrez <pozix@home.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Gerald Gutierrez @ 2001-06-07 06:10 UTC (permalink / raw)
  To: pgsql-sql


I'm using 7.1.1 right now, and have the following table:

  id |   s
----+-------
   1 | alpha
   2 | beta
   3 | gamma
   4 | delta
(4 rows)

I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip" 
them). Since id is the PK, it must remain unique and so I can't just set 
the two lines using two UPDATEs.

My solution is:

UPDATE t1 SET id=id#1 WHERE id=2 OR id=3; -- # is the XOR operator

where 2#1=3 and 3#1=2. One statement will change both values as I want. But 
when I run the statement, the server replies with:

ERROR:  Cannot insert a duplicate key into unique index t1_pkey

If the statement is "atomic", then if the statement succeeds, the IDs will 
be unique and the error is incorrect. Does this imply that SQL statements 
are not actually atomic?




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Are SQL commands "atomic" ?
@ 2001-06-07 16:59 Gerald Gutierrez <gml1@coldresist.com>
  2001-06-07 17:39 ` Re: Are SQL commands "atomic" ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-06-07 19:05 ` Re: Are SQL commands "atomic" ? Gerald Gutierrez <gml1@coldresist.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Gerald Gutierrez @ 2001-06-07 16:59 UTC (permalink / raw)
  To: pgsql-sql


I'm using 7.1.1 right now, and have the following table:

  id |   s
----+-------
   1 | alpha
   2 | beta
   3 | gamma
   4 | delta
(4 rows)

I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip" 
them). Since id is the PK, it must remain unique and so I can't just set 
the two lines using two UPDATEs.

My solution is:

UPDATE t1 SET id=id#1 WHERE id=2 OR id=3; -- # is the XOR operator

where 2#1=3 and 3#1=2. One statement will change both values as I want. But 
when I run the statement, the server replies with:

ERROR:  Cannot insert a duplicate key into unique index t1_pkey

If the statement is "atomic", then if the statement succeeds, the IDs will 
be unique and the error is incorrect. Does this imply that SQL statements 
are not actually atomic? 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Are SQL commands "atomic" ?
  2001-06-07 16:59 Are SQL commands "atomic" ? Gerald Gutierrez <gml1@coldresist.com>
@ 2001-06-07 17:39 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Stephan Szabo @ 2001-06-07 17:39 UTC (permalink / raw)
  To: Gerald Gutierrez <gml1@coldresist.com>; +Cc: pgsql-sql

On Thu, 7 Jun 2001, Gerald Gutierrez wrote:

> 
> I'm using 7.1.1 right now, and have the following table:
> 
>   id |   s
> ----+-------
>    1 | alpha
>    2 | beta
>    3 | gamma
>    4 | delta
> (4 rows)
> 
> I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip" 
> them). Since id is the PK, it must remain unique and so I can't just set 
> the two lines using two UPDATEs.
> 
> My solution is:
> 
> UPDATE t1 SET id=id#1 WHERE id=2 OR id=3; -- # is the XOR operator
> 
> where 2#1=3 and 3#1=2. One statement will change both values as I want. But 
> when I run the statement, the server replies with:
> 
> ERROR:  Cannot insert a duplicate key into unique index t1_pkey
> 
> If the statement is "atomic", then if the statement succeeds, the IDs will 
> be unique and the error is incorrect. Does this imply that SQL statements 
> are not actually atomic? 

Not exactly.  It's a bug in the implementation of the unique constraint.
The unique constraint is being checked per-row rather than per-statement.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Are SQL commands "atomic" ?
  2001-06-07 16:59 Are SQL commands "atomic" ? Gerald Gutierrez <gml1@coldresist.com>
@ 2001-06-07 19:05 ` Gerald Gutierrez <gml1@coldresist.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Gerald Gutierrez @ 2001-06-07 19:05 UTC (permalink / raw)
  To: pgsql-sql

At 10:39 AM 6/7/2001 -0700, Stephan Szabo wrote:

>Not exactly.  It's a bug in the implementation of the unique constraint.
>The unique constraint is being checked per-row rather than per-statement.

Is this bug on a todo list, or should I submit a bug report?




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-06-07 19:05 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-07 06:10 Are SQL commands "atomic" ? Gerald Gutierrez <pozix@home.com>
2001-06-07 16:59 Are SQL commands "atomic" ? Gerald Gutierrez <gml1@coldresist.com>
2001-06-07 17:39 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 19:05 ` Gerald Gutierrez <gml1@coldresist.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox