From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wiebe Cazemier <halfgaar@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Delete rule chain stops unexpectedly
Date: Fri, 21 Oct 2005 12:06:31 -0400
Message-ID: <23325.1129910791@sss.pgh.pa.us> (raw)
In-Reply-To: <435902DC.3000807@gmail.com>
References: <4358F79B.1000409@gmail.com>
<21436.1129905772@sss.pgh.pa.us>
<435902DC.3000807@gmail.com>
Wiebe Cazemier <halfgaar@gmail.com> writes:
> Tom Lane wrote:
>>> The rule that actually deletes the rows from the underlying has to fire
>>> last, since the rows are gone from the view (and hence from OLD) the
>>> moment you delete them.
> A quote from the postgresql manual:
> "But for ON UPDATE and ON DELETE rules, the original query is done after the
> actions added by rules. This ensures that the actions can see the to-be-updated
> or to-be-deleted rows"
Yes, I know that quote. I wrote it. It's not relevant here because the
original query (the delete against the view) is never done at all, since
you have a DO INSTEAD rule. What is relevant is the timing of the
delete issued against the underlying table, and you have that scheduled
to occur too early because the rule that does it is first in
alphabetical order.
> So, the actual delete should be done after all the rules. And even if
> it does delete before anything else, that does not explain why "step2"
> is not inserted into the debuglog table.
Because the rule converts those inserts into, effectively,
INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row);
and there are no longer any matching OLD rows in the view. (If it
didn't act that way then the INSERTs would execute even for a "DELETE
WHERE false". If you find any of this surprising or not what you want,
you should probably be using triggers not rules.)
> Or, that all the rules _are_
> executed when I call the query with "explain analayze".
Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should
bump the CommandCounter between plan trees, but fails to ...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, halfgaar@gmail.com
Subject: Re: Delete rule chain stops unexpectedly
In-Reply-To: <23325.1129910791@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox