On Wed, May 28, 2008 at 04:59:33PM +0800, laser wrote:
> >Yes, use a TRIGGER instead a RULE for such tasks.
>
> thanks, good to know it's a bug and we'll try TRIGGER approach.
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,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.