Re: Deprecating non-select rules (was Re: Last gasp)

On Monday, April 09, 2012 05:32:43 PM Noah Misch wrote:
> On Mon, Apr 09, 2012 at 03:35:06PM +0200, Andres Freund wrote:
> > On Monday, April 09, 2012 03:25:36 PM Robert Haas wrote:
> > > contrib/xml2 isn't doing us much harm beyond being an ugly wart, but
> > > non- SELECT rules are a land mine for the unwary at best.
> >
> > Which we could start deprecating now btw. since INSTEAD triggers landed
> > in 9.1. There were quite some use-cases for non-select rules that
> > couldn't be fullfilled before but I think saying that we won't support
> > those rules for more than 3 releases or so might be a good idea. I have
> > seen too many bugs being caused by experienced people not realizing the
> > pitfalls of rules.
>
> A new documentation section "Pitfalls of the Rule System" discussing the
> known hazards would help users immediately and be far easier to adopt.
Youre right. The pitfalls should at least be hinted at. Writing a
comprehensive guide on whats safe and whats not isn't exactly easy without
going into quite a bit of detail.

Here is what I know and what comes to my mind right now:

1. anything but INSTEAD rules are unsafe
2. the use of volatile functions (like nextval()) directly or indirectly
(DEFAULT) needs to be vetted very carefully even if 1. is adherred to
3. the snapshots behaviour of an expanded statement is a bit confusing if it
contains multiple statements which causes problems with the rather frequent
attempts to build rules with upsert'is behaviour

While documenting it is sensible a hidden note somewhere doesn't help very
much. I personally think the rule system is too broken to be salvageable for
anything but internals of views.

A very trivial, seemingly innocuous, but totally broken usage of rules:

CREATE TABLE data(data_id serial primary key, data int);
CREATE TABLE audit(audit_id serial primary key, data_id int NOT NULL, data
int);

CREATE RULE data_audit AS ON INSERT TO data DO ALSO INSERT INTO audit(data_id,
data) VALUES(NEW.data_id, NEW.data);

INSERT INTO data(data) VALUES(1);

SELECT * FROM data;
SELECT * FROM audit;

Greetings,

Andres

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services