Thread: Atomicity?

Atomicity?

From
Naz Gassiep
Date:
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=#

Re: Atomicity?

From
Peter Eisentraut
Date:
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/

Re: Atomicity?

From
"Joshua D. Drake"
Date:
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/



Re: Atomicity?

From
Naz Gassiep
Date:
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

Re: Atomicity?

From
Michael Glaesemann
Date:
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




Re: Atomicity?

From
Naz Gassiep
Date:
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
>>
>
>

Re: Atomicity?

From
Naz Gassiep
Date:
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
>
>
>
>

Re: Atomicity?

From
"Joshua D. Drake"
Date:
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/



Re: Atomicity?

From
Tom Lane
Date:
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

Re: Atomicity?

From
Martijn van Oosterhout
Date:
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

Re: Atomicity?

From
Richard Broersma Jr
Date:
> 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.

Re: Atomicity?

From
Tom Lane
Date:
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

Re: Atomicity?

From
Peter Eisentraut
Date:
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/

Re: Atomicity?

From
Peter Eisentraut
Date:
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/

Re: Atomicity?

From
"Greg Sabino Mullane"
Date:
-----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-----



Re: Atomicity?

From
Bruce Momjian
Date:
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. +

Re: Atomicity?

From
Bruce Momjian
Date:
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. +

Re: Atomicity?

From
Ron Johnson
Date:
-----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-----