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