From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Matthew Denny <mdenny(at)CS(dot)Berkeley(dot)EDU> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: question on UPDATE rules |
Date: | 2000-04-23 00:06:07 |
Message-ID: | 29468.956448367@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Matthew Denny <mdenny(at)CS(dot)Berkeley(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2000-04-23 13:47:48 | Re: Date_part & cast. |
Previous Message | Tom Lane | 2000-04-22 23:47:51 | Re: Date_part & cast. |