Skip site navigation (1) Skip section navigation (2)

Re: Multiple-statement Rules Incompatible With Constraints

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Curt Sampson <cjs(at)cynic(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Multiple-statement Rules Incompatible With Constraints
Date: 2005-05-27 14:20:23
Message-ID: 14498.1117203623@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Curt Sampson <cjs(at)cynic(dot)net> writes:
>      CREATE VIEW offer AS
>  	SELECT * FROM offer_immutable NATURAL JOIN offer_mutable;

> In a transaction, when I try to commit, this does not work:

>      CREATE OR REPLACE RULE offer_delete AS
>  	ON DELETE TO offer DO INSTEAD (
>  	    DELETE FROM offer_mutable WHERE offer_id = OLD.offer_id;
>  	    DELETE FROM offer_immutable WHERE offer_id = OLD.offer_id;
>  	);

Same old same old: as soon as you've deleted from offer_mutable, there
is no row in the view with the given offer_id; and since OLD is a macro
for the view, the second delete finds nothing to do.

You might be able to fix this by making the view an outer join, and
deleting from the nullable side first.  The whole data structure seems a
tad weird though ...

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Neil ConwayDate: 2005-05-27 14:21:18
Subject: Re: foreign keys and RI triggers
Previous:From: Mark Cave-AylandDate: 2005-05-27 14:20:04
Subject: Re: Cost of XLogInsert CRC calculations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group