It's not a bug, just your misunderstanding of how rules work. Rules
rewrite queries. What happen in your case is because of the condition
your query will be split into two: once with your INSERT with a NOT
EXISTS (subquery) and once as an UPDATE with the condition EXISTS
(subquery).
So the first query will insert with id=1 and then the update sees this
row and updates it to 2.
What this says is that rules are the wrong tool for what you're trying
to do. Conditional rules are powerful but not appropriate here.
Have a nice day,
Thanks to clarify, if it's a misunderstanding of rule, then I'll some how
confuse with DO INSTEAD vs. DO ALSO rule, isn't DO INSTEAD will
replace original INSERT with the one provided in CREATE RULE?
...after some rethinking, can I understand what happened as below step?
1, INSERT will be rewrite into a INSERT with a EXIST condition clause
and a UPDATE statement;
2, when NOT EXISTS, INSERT succeed, and the query tree in 1 proceed to
UPDATE;
3, the UPDATE saw the INSERT in 2, then UPDATE it;
if so, then I understand what happed there, and it's surly not a bug but
a mis-use of rule.
thanks and best regards
-laser
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
