Re: Bug in PL/pgSQL GET DIAGNOSTICS?

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 rule
ON UPDATE TO service_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 rule
ON DELETE TO service_request
DO
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 rule
ON INSERT TO view_realtable
DO INSTEAD
INSERT INTO realtable
VALUES (new.col);

CREATE RULE view_realtable_update AS -- UPDATE rule
ON UPDATE TO view_realtable
DO INSTEAD
UPDATE realtable
SET col = new.col
WHERE col = old.col;

CREATE RULE view_realtable_delete AS -- DELETE rule
ON 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073