Thread: Atomicity?
I am getting an error that I think I understand, but that I didn't think
should happen.
Below is the output from psql that I am getting to trigger this error.
If the violation of the constraint really is being caused WITHIN the
query, doesn't that violate the principle of atomicity? I.e., operations
and entities should be considered a single entire construct rather than
a collection of smaller, discrete parts. Or do I have my understanding
all wrong?
In any case, how do I get around this problem?
Regards,
- Naz.
conwatch=# \d replies;
Table "conwatch.replies"
Column | Type | Modifiers
-----------+--------------------------+-----------------------------------------------------------
replyid | integer | not null default
nextval('replies_replyid_seq'::regclass)
postid | integer | not null
lft | smallint | not null
rgt | smallint | not null
poster | integer | not null
posted | timestamp with time zone | not null default now()
title | character varying(100) | not null
body | text |
anonymous | boolean | not null default false
Indexes:
"replies_pkey" PRIMARY KEY, btree (replyid)
"replies_lft_postid" UNIQUE, btree (lft, postid)
"replies_rgt_postid" UNIQUE, btree (rgt, postid)
"replies_lft_index" btree (lft)
"replies_rgt_index" btree (rgt)
Foreign-key constraints:
"replies_poster_fkey" FOREIGN KEY (poster) REFERENCES users(userid)
"replies_postid_fkey" FOREIGN KEY (postid) REFERENCES posts(postid)
conwatch=# select replyid, postid, lft, rgt, title from replies where
postid = 18 order by lft;
replyid | postid | lft | rgt | title
---------+--------+-----+-----+----------------------
24 | 18 | 1 | 14 | Invisible root post.
25 | 18 | 2 | 7 | Re: Pronto
26 | 18 | 3 | 6 | Re: Pronto
27 | 18 | 4 | 5 | Re: Pronto
29 | 18 | 8 | 13 | Re: Pronto
31 | 18 | 9 | 12 | Re: Pronto
32 | 18 | 10 | 11 | Re: Pronto
(7 rows)
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >= 11;
ERROR: duplicate key violates unique constraint "replies_rgt_postid"
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 14;
UPDATE 1
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 13;
UPDATE 1
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 12;
UPDATE 1
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 11;
UPDATE 1
conwatch=#
Naz Gassiep wrote: > If the violation of the constraint really is being caused > WITHIN the query, doesn't that violate the principle of atomicity? > I.e., operations and entities should be considered a single entire > construct rather than a collection of smaller, discrete parts. The principle of atomicity merely says that transactions are either performed entirely or not at all. If the transaction is not performed, then there is no violation of atomicity. > conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt > >= 11; > ERROR: duplicate key violates unique constraint "replies_rgt_postid" This is a well-known deficiency in PostgreSQL. You will have to work around it somehow (by changing the query, the schema, or the index). -- Peter Eisentraut http://developer.postgresql.org/~petere/
Naz Gassiep wrote:
> I am getting an error that I think I understand, but that I didn't think
> should happen.
>
> Below is the output from psql that I am getting to trigger this error.
> If the violation of the constraint really is being caused WITHIN the
> query, doesn't that violate the principle of atomicity? I.e., operations
> and entities should be considered a single entire construct rather than
> a collection of smaller, discrete parts. Or do I have my understanding
> all wrong?
>
> In any case, how do I get around this problem?
If you do not specify the beginning of a transaction, all statements are
run within their own transaction.. e;g:
Your example actually means:
begin;
> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >=
> 11;
commit;
begin;
> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 14;
commit;
What you want is:
begin;
> UPDATE 1
> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 13;
> UPDATE 1
> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 12;
> UPDATE 1
> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt = 11;
> UPDATE 1
> conwatch=#
commit;
Joshua D. Drake
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Peter Eisentraut wrote:
Naz Gassiep wrote:
If the violation of the constraint really is being caused
WITHIN the query, doesn't that violate the principle of atomicity?
I.e., operations and entities should be considered a single entire
construct rather than a collection of smaller, discrete parts.
The principle of atomicity merely says that transactions are either
performed entirely or not at all. If the transaction is not performed,
then there is no violation of atomicity.
conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
= 11;
ERROR: duplicate key violates unique constraint "replies_rgt_postid"
This is a well-known deficiency in PostgreSQL. You will have to work
around it somehow (by changing the query, the schema, or the index).
Do we have an ETA on fixing it? Or is it a long term outstanding issue
with no ETA as yet?
Thanks for the reply,
- Naz
On Aug 29, 2006, at 4:46 , Peter Eisentraut wrote:
> Naz Gassiep wrote:
>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
>>> = 11;
>> ERROR: duplicate key violates unique constraint "replies_rgt_postid"
>
> This is a well-known deficiency in PostgreSQL. You will have to work
> around it somehow (by changing the query, the schema, or the index).
One such workaround is:
BEGIN;
UPDATE replies
SET rgt = -1 * (rgt + 2)
WHERE postid = 18
AND rgt >= 11;
UPDATE replies
SET rgt = -1 * rgt
WHERE rgt < 0;
COMMIT;
Michael Glaesemann
grzm seespotcode net
No, the subsequent UPDATEs were just there to show you they worked... I was only interested in the failed update, and why it failed. The DB was consistent before the query, and it would have been after the query, so I did not understand why the query failed unless the query made teh DB inconsistent at some point DURING its execution. This seems odd to me, as queries should not trigger errors like that if the DB is only out of consistency DURING its execution, as long as it is consistent before and after. Regards, - Naz. Joshua D. Drake wrote: > Naz Gassiep wrote: >> I am getting an error that I think I understand, but that I didn't >> think should happen. >> >> Below is the output from psql that I am getting to trigger this >> error. If the violation of the constraint really is being caused >> WITHIN the query, doesn't that violate the principle of atomicity? >> I.e., operations and entities should be considered a single entire >> construct rather than a collection of smaller, discrete parts. Or do >> I have my understanding all wrong? >> >> In any case, how do I get around this problem? > > If you do not specify the beginning of a transaction, all statements > are run within their own transaction.. e;g: > > Your example actually means: > > begin; > >> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >> >= 11; > > commit; > > begin; >> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >> = 14; > commit; > > What you want is: > > begin; > >> UPDATE 1 >> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >> = 13; >> UPDATE 1 >> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >> = 12; >> UPDATE 1 >> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >> = 11; >> UPDATE 1 >> conwatch=# > > commit; > > Joshua D. Drake > > >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> > >
I would like more information on this deficiency and what causes it so I know when to anticipate it. This resulted in a rather nasty bug which took me ages to track down. Is anyone able+willing to explain a little here or should I ask in -hackers ? Regards, - Naz. Michael Glaesemann wrote: > > On Aug 29, 2006, at 4:46 , Peter Eisentraut wrote: > >> Naz Gassiep wrote: >>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >>>> = 11; >>> ERROR: duplicate key violates unique constraint "replies_rgt_postid" >> >> This is a well-known deficiency in PostgreSQL. You will have to work >> around it somehow (by changing the query, the schema, or the index). > > One such workaround is: > > BEGIN; > > UPDATE replies > SET rgt = -1 * (rgt + 2) > WHERE postid = 18 > AND rgt >= 11; > > UPDATE replies > SET rgt = -1 * rgt > WHERE rgt < 0; > > COMMIT; > > Michael Glaesemann > grzm seespotcode net > > > >
Naz Gassiep wrote:
> No, the subsequent UPDATEs were just there to show you they worked... I
> was only interested in the failed update, and why it failed. The DB was
> consistent before the query, and it would have been after the query, so
> I did not understand why the query failed unless the query made teh DB
> inconsistent at some point DURING its execution. This seems odd to me,
> as queries should not trigger errors like that if the DB is only out of
> consistency DURING its execution, as long as it is consistent before and
> after.
Yeah I misunderstood your question. See PeterE's response.
Sincerely,
Joshua D. Drake
> Regards,
> - Naz.
>
> Joshua D. Drake wrote:
>> Naz Gassiep wrote:
>>> I am getting an error that I think I understand, but that I didn't
>>> think should happen.
>>>
>>> Below is the output from psql that I am getting to trigger this
>>> error. If the violation of the constraint really is being caused
>>> WITHIN the query, doesn't that violate the principle of atomicity?
>>> I.e., operations and entities should be considered a single entire
>>> construct rather than a collection of smaller, discrete parts. Or do
>>> I have my understanding all wrong?
>>>
>>> In any case, how do I get around this problem?
>>
>> If you do not specify the beginning of a transaction, all statements
>> are run within their own transaction.. e;g:
>>
>> Your example actually means:
>>
>> begin;
>>
>>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
>>> >= 11;
>>
>> commit;
>>
>> begin;
>>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
>>> = 14;
>> commit;
>>
>> What you want is:
>>
>> begin;
>>
>>> UPDATE 1
>>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
>>> = 13;
>>> UPDATE 1
>>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
>>> = 12;
>>> UPDATE 1
>>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
>>> = 11;
>>> UPDATE 1
>>> conwatch=#
>>
>> commit;
>>
>> Joshua D. Drake
>>
>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: explain analyze is your friend
>>>
>>
>>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Naz Gassiep <naz@mira.net> writes:
> I would like more information on this deficiency and what causes it so I
> know when to anticipate it.
The uniqueness constraint is checked on a row-by-row basis, so if you
update one row to hold the same value as another row holds, you get an
error immediately. It doesn't matter that if the query had been allowed
to finish, it would have updated that other row to some non-conflicting
value. (You might be able to work around this if you could control the
order in which rows are updated, but you can't.)
This is not what the SQL spec says should happen, but so far no one has
proposed a reimplementation that doesn't give up unreasonable amounts
of performance. It's on the TODO list ...
regards, tom lane
On Tue, Aug 29, 2006 at 06:17:39AM +1000, Naz Gassiep wrote: > I would like more information on this deficiency and what causes it so I > know when to anticipate it. This resulted in a rather nasty bug which > took me ages to track down. Is anyone able+willing to explain a little > here or should I ask in -hackers ? Sure, UNIQUE constraints are not deferrable. With normal constraints you can defer the check until the end of transaction and be in an inconsistant state for while. However, PostgreSQL doesn't support this for uniqueness checks. However, temporary inconsistancy in unique columns doesn't some up that often so it's not that big a deal. This has been a problem for a long time and will quite possibly be for a while still, mainly because no-one really has any idea how to fix it... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
> Naz Gassiep <naz@mira.net> writes: > > I would like more information on this deficiency and what causes it so I > > know when to anticipate it. > > The uniqueness constraint is checked on a row-by-row basis, so if you > update one row to hold the same value as another row holds, you get an > error immediately. It doesn't matter that if the query had been allowed > to finish, it would have updated that other row to some non-conflicting > value. (You might be able to work around this if you could control the > order in which rows are updated, but you can't.) > > This is not what the SQL spec says should happen, but so far no one has > proposed a reimplementation that doesn't give up unreasonable amounts > of performance. It's on the TODO list ... Is this related to the current limitations of "SET CONSTRAINTS"? http://www.postgresql.org/docs/8.1/interactive/sql-set-constraints.html Regards, Richard Broersma Jr.
Martijn van Oosterhout <kleptog@svana.org> writes:
> Sure, UNIQUE constraints are not deferrable. With normal constraints
> you can defer the check until the end of transaction and be in an
> inconsistant state for while. However, PostgreSQL doesn't support this
> for uniqueness checks.
Actually, what the spec says is (SQL92 4.10.1)
The checking of a constraint depends on its constraint mode within
the current SQL-transaction. If the constraint mode is immedi-
ate, then the constraint is effectively checked at the end of
each SQL-statement. If the constraint mode is deferred, then the
constraint is effectively checked when the constraint mode is
changed to immediate either explicitly by execution of a <set con-
straints mode statement>, or implicitly at the end of the current
SQL-transaction.
So even for a non-deferred unique constraint, it should be legal to
update multiple rows to new non-conflicting values within a single
UPDATE command. Plus, as Martijn says, we have no support at all
for the defer-to-end-of-transaction case.
We've discussed this before, and I thought it was on the TODO list,
but AFAICS the only entry there is
* Allow DEFERRABLE UNIQUE constraints?
which is misfiled under "Triggers" and doesn't cover the existing
spec violation anyway. Bruce?
regards, tom lane
Richard Broersma Jr wrote: > Is this related to the current limitations of "SET CONSTRAINTS"? Only in a vague and nonspecific way. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Martijn van Oosterhout wrote: > Sure, UNIQUE constraints are not deferrable. With normal constraints > you can defer the check until the end of transaction and be in an > inconsistant state for while. However, PostgreSQL doesn't support > this for uniqueness checks. Note that even a nondeferred unique constraint would let the command proceed because a nondeferred constraint is checked after the statement, not at seemingly random points during it. -- Peter Eisentraut http://developer.postgresql.org/~petere/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I would like more information on this deficiency and what causes it so I > know when to anticipate it. This resulted in a rather nasty bug which > took me ages to track down. Is anyone able+willing to explain a little > here or should I ask in -hackers ? This has some workarounds and explanations that may help: http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html - -- Greg Sabino Mullane greg@endpoint.com greg@turnstep.com End Point Corporation 610-983-9073 PGP Key: 0x14964AC8 200608281703 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFE81pHvJuQZxSWSsgRAvETAJ9u22as0X76y9XHGyhfPyOEa70RNQCgxsjA IPV3jK0DAHzr7OD3xY2jFMA= =2JJL -----END PGP SIGNATURE-----
Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > Sure, UNIQUE constraints are not deferrable. With normal constraints
> > you can defer the check until the end of transaction and be in an
> > inconsistant state for while. However, PostgreSQL doesn't support this
> > for uniqueness checks.
>
> Actually, what the spec says is (SQL92 4.10.1)
>
> The checking of a constraint depends on its constraint mode within
> the current SQL-transaction. If the constraint mode is immedi-
> ate, then the constraint is effectively checked at the end of
> each SQL-statement. If the constraint mode is deferred, then the
> constraint is effectively checked when the constraint mode is
> changed to immediate either explicitly by execution of a <set con-
> straints mode statement>, or implicitly at the end of the current
> SQL-transaction.
>
> So even for a non-deferred unique constraint, it should be legal to
> update multiple rows to new non-conflicting values within a single
> UPDATE command. Plus, as Martijn says, we have no support at all
> for the defer-to-end-of-transaction case.
>
> We've discussed this before, and I thought it was on the TODO list,
> but AFAICS the only entry there is
>
> * Allow DEFERRABLE UNIQUE constraints?
>
> which is misfiled under "Triggers" and doesn't cover the existing
> spec violation anyway. Bruce?
TODO updated:
* Allow DEFERRABLE and end-of-statement UNIQUE constraints?
This would allow UPDATE tab SET col = col + 1 to work if col has
a unique index. Currently, uniqueness checks are done while the
command is being executed, rather than at the end of the statement
or transaction.
and moved to "referential integrity" section.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Greg Sabino Mullane wrote: [ There is text before PGP section. ] > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > > I would like more information on this deficiency and what causes it so I > > know when to anticipate it. This resulted in a rather nasty bug which > > took me ages to track down. Is anyone able+willing to explain a little > > here or should I ask in -hackers ? > > This has some workarounds and explanations that may help: > > http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html URL added to TODO. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Naz Gassiep wrote:
> I am getting an error that I think I understand, but that I didn't think
> should happen.
>
> Below is the output from psql that I am getting to trigger this error.
> If the violation of the constraint really is being caused WITHIN the
> query, doesn't that violate the principle of atomicity? I.e., operations
> and entities should be considered a single entire construct rather than
> a collection of smaller, discrete parts. Or do I have my understanding
> all wrong?
>
> In any case, how do I get around this problem?
>
> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt >=
> 11;
Another work-around would be a loop.
Pseudo-code:
BEGIN
FOR :X IN
SELECT RGT, REPLYID
FROM REPLIES
WHERE POSTID = 18
AND RGT >= 11
DO
UPDATE REPLIES
SET RGT = RGT + 2
WHERE REPLIYID = :X.REPLYID;
END FOR;
END;
- --
Ron Johnson, Jr.
Jefferson LA USA
Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFE9GSjS9HxQb37XmcRArqiAJ90r+NPOzs312kav/682DiH16YBzgCgriDt
pCy0mK/74NvnHim5uaLeYrU=
=hJ1s
-----END PGP SIGNATURE-----