pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAre 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