Atomicity?
От | Naz Gassiep |
---|---|
Тема | Atomicity? |
Дата | |
Msg-id | 44F342C6.9010708@mira.net обсуждение исходный текст |
Ответы |
Re: Atomicity?
Re: Atomicity? Re: Atomicity? |
Список | 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=#
В списке pgsql-general по дате отправления: