Atomicity? - Mailing list pgsql-general

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=#