Re: Bug in PL/pgSQL GET DIAGNOSTICS? - Mailing list pgsql-hackers

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > Well, let's look at the common case.  For proper view rules, these would
> > all return the right values because the UPDATE in the rule would be
> > returned.  Is that what you mean?
> 
> I guess that really depends on whether the rules are written to properly
> constrain the writes to the view to the set of rows visible by the view.
> For example, if a view v1 selects from a single table t1 constrained by a
> search condition, and I do UPDATE v1 SET ...; without a condition, does
> that affect all rows in t1?  If not, then both our proposals are
> equivalent, if yes, then the it's the user's fault, I suppose.

Well, since we found that we can't get a perfect solution, I started to
think of the common cases.  First, there is the "log changes" type of
rule, but that isn't INSTEAD, so it doesn't even apply here.  We already
know we want to return the result of the main query.CREATE RULE service_request_update AS     -- UPDATE ruleON UPDATE
TOservice_request DO     INSERT INTO service_request_log (customer_id, description, mod_type)    VALUES
(old.customer_id,old.description, 'U');CREATE RULE service_request_delete AS     -- DELETE ruleON DELETE TO
service_requestDO    INSERT INTO service_request_log (customer_id, description, mod_type)    VALUES (old.customer_id,
old.description,'D');
 

Second, there is the updatable view rule, that is INSTEAD, and relies on
the primary key of the table:CREATE RULE view_realtable_insert AS     -- INSERT ruleON INSERT TO view_realtable DO
INSTEAD    INSERT INTO realtable     VALUES (new.col);CREATE RULE view_realtable_update AS     -- UPDATE ruleON UPDATE
TOview_realtable DO INSTEAD     UPDATE realtable     SET col = new.col     WHERE col = old.col;CREATE RULE
view_realtable_deleteAS     -- DELETE ruleON DELETE TO view_realtable DO INSTEAD     DELETE FROM realtable     WHERE
col= old.col;
 

It is my understanding that the proposed rule result improvements will
return the proper values in these cases.  That is why I like the current
proposal.  It also makes any extra non-tag matching queries in the rule
not affect the result, which seems best.

Does anyone else have a common rule that would return incorrect results
using the proposed rules?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073