From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthew Denny <mdenny@CS.Berkeley.EDU>
Cc: pgsql-sql@postgresql.org
Subject: Re: question on UPDATE rules
Date: Sat, 22 Apr 2000 20:06:07 -0400
Message-ID: <29468.956448367@sss.pgh.pa.us> (raw)
In-Reply-To: <14592.60375.884848.74935@caracas.CS.Berkeley.EDU>
References: <14592.60375.884848.74935@caracas.CS.Berkeley.EDU>
Matthew Denny <mdenny@CS.Berkeley.EDU> writes:
> I have a rule defined as the following:
> CREATE RULE fooBar AS ON
> UPDATE TO VectorMessageTable WHERE new.acks = (SELECT
> numReplicas FROM VectorTable vt WHERE vt.ID =
> new.ID) DO (DELETE FROM VectorMessageTable WHERE ID = new.ID AND
> versionNum = new.versionNum);
> if I call:
> UPDATE VectorMessageTable SET acks =
> <corresponding-numReplicas-from-VectorTable>
> Then I the update goes through, but none of the records are
> deleted. However, if I run any other subsequent update on these tuples in
> VectorMessageTable (even ones that change the acks value) then the
> rule fires and the tuples are deleted.
Not sure, but maybe you want DO INSTEAD DELETE ... rather than just
DO DELETE ... ? As it stands, you're commanding the machine to both
delete and update the tuples for which the rule fires. I think that
will result in the old tuple being marked deleted (twice!), but the
update will still produce a new tuple that's not marked deleted.
That seems to match your symptoms...
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, mdenny@CS.Berkeley.EDU
Subject: Re: question on UPDATE rules
In-Reply-To: <29468.956448367@sss.pgh.pa.us>
References: <14592.60375.884848.74935@caracas.CS.Berkeley.EDU> <29468.956448367@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