Thread: A little RULE help?
Hi all,
I’m trying to create a temporal table, from which data is never really deleted but each row “exists” for only a certain period of time, and a view of this table showing the currently “live” rows.
I would also like the view to accept INSERT, UPDATE, and DELETEs, and perform the relevant operations on the parent table.
I have got everything working by using RULEs on the view, except for UPDATES, where every attempt has failed – my UPDATE rule seems to have the same effect on the table as a DELETE, but no rows are returned when using a RETURNING clause. I can’t see what I’m doing wrong.
I could use a TRIGGER instead (as shown below) but I’d rather not incur the extra overhead if there is a RULE out there that works.
I’m running v10.1, but this example should be compatible with at least v9.6.
Any help would be greatly appreciated!
Thanks,
Steve.
Here is some code to set up the example
CREATE EXTENSION IF NOT EXISTS btree_gist;
DROP TABLE IF EXISTS rule_test CASCADE;
CREATE TABLE rule_test (
id serial PRIMARY KEY,
tt tstzrange NOT NULL,
foo integer NOT NULL,
bar double precision NOT NULL,
EXCLUDE USING gist (tt WITH &&, foo WITH =)
);
CREATE TABLE rule_test_view ( LIKE rule_test );
CREATE RULE "_RETURN" AS ON SELECT TO rule_test_view DO INSTEAD
SELECT * FROM rule_test WHERE tt @> CURRENT_TIMESTAMP;
CREATE RULE rule_test_insert AS ON INSERT TO rule_test_view DO INSTEAD
INSERT INTO rule_test (tt, foo, bar) VALUES (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *;
CREATE RULE rule_test_delete AS ON DELETE TO rule_test_view DO INSTEAD
UPDATE rule_test SET tt = tstzrange(lower(tt), CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id RETURNING *;
-- What I'd like to be able to do here is this:
-- CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD (
-- UPDATE rule_test SET tt = tstzrange(lower(tt), CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id;
-- INSERT INTO rule_test (tt, foo, bar) VALUES (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *;
-- );
-- or, using the rules already defined, this:
-- CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD (
-- DELETE FROM rule_test_view WHERE id = OLD.id;
-- INSERT INTO rule_test_view (foo, bar) VALUES (NEW.foo, NEW.bar) RETURNING *;
-- );
-- but I can only get the desired effect using this trigger:
CREATE OR REPLACE FUNCTION rule_test_update ()
RETURNS trigger
AS
$$
BEGIN
DELETE FROM rule_test_view WHERE id = OLD.id;
INSERT INTO rule_test_view (foo, bar) VALUES (NEW.foo, NEW.bar) RETURNING tt into NEW.tt;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER rule_test_update_trig INSTEAD OF UPDATE ON rule_test_view FOR EACH ROW EXECUTE PROCEDURE rule_test_update();
…And here is some code to test DML on the view
-- ###########################
-- 1. Insert some data, and check that RETURNING works
INSERT INTO rule_test_view (foo, bar) VALUES (1, 1.0), (2, 2.0), (3, 3.0) RETURNING *;
-- 2. Check that tt has been populated for each row
TABLE rule_test;
-- 3. Check that all rows are visible in the view
TABLE rule_test_view;
-- 4. "Delete" one row, and check that RETURNING works
DELETE FROM rule_test_view WHERE id = 1 RETURNING *;
-- 5. Check that the row still exists in the table, but the upper limit of tt is now set
TABLE rule_test;
-- 6. Check that the "deleted" row is no longer visible in the view
TABLE rule_test_view;
-- 7. "Update" one row, and check that RETURNING works
UPDATE rule_test_view SET bar = 3.141 WHERE foo = 3 RETURNING *;
-- 8. Check that the old version still exists in the table, but the upper limit of tt is now set,
-- and a new version now exists, holding the new value
TABLE rule_test;
-- 9. Check that the old version of the row is no longer visible in the view
TABLE rule_test_view;
On 01/25/2018 09:53 PM, Steven Winfield wrote: > Subject: A little RULE help? Don't use RULEs. HTH. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
> Don't use RULEs. HTH.
Why not?
On 01/25/2018 12:53 PM, Steven Winfield wrote: > Hi all, > > I’m trying to create a temporal table, from which data is never really > deleted but each row “exists” for only a certain period of time, and a > view of this table showing the currently “live” rows. > > I would also like the view to accept INSERT, UPDATE, and DELETEs, and > perform the relevant operations on the parent table. > > I have got everything working by using RULEs on the view, except for > UPDATES, where every attempt has failed – my UPDATE rule seems to have > the same effect on the table as a DELETE, but no rows are returned when > using a RETURNING clause. I can’t see what I’m doing wrong. > > I could use a TRIGGER instead (as shown below) but I’d rather not incur > the extra overhead if there is a RULE out there that works. > > I’m running v10.1, but this example should be compatible with at least v9.6. > > Any help would be greatly appreciated! Maybe this?: https://www.postgresql.org/docs/10/static/sql-createrule.html "There is a catch if you try to use conditional rules for complex view updates: there must be an unconditional INSTEAD rule for each action you wish to allow on the view. If the rule is conditional, or is not INSTEAD, then the system will still reject attempts to perform the update action, because it thinks it might end up trying to perform the action on the dummy table of the view in some cases. If you want to handle all the useful cases in conditional rules, add an unconditional DO INSTEAD NOTHING rule to ensure that the system understands it will never be called on to update the dummy table. Then make the conditional rules non-INSTEAD; in the cases where they are applied, they add to the default INSTEAD NOTHING action. (This method does not currently work to support RETURNING queries, however.)" FYI, I gave up on RULE's a while back. Triggers are a lot easier to figure out and maintain. > > Thanks, > > Steve. > > Here is some code to set up the example > > CREATE EXTENSION IF NOT EXISTS btree_gist; > > DROP TABLE IF EXISTS rule_test CASCADE; > > CREATE TABLE rule_test ( > > id serial PRIMARY KEY, > > tt tstzrange NOT NULL, > > foo integer NOT NULL, > > bar double precision NOT NULL, > > EXCLUDE USING gist (tt WITH &&, foo WITH =) > > ); > > CREATE TABLE rule_test_view ( LIKE rule_test ); > > CREATE RULE "_RETURN" AS ON SELECT TO rule_test_view DO INSTEAD > > SELECT * FROM rule_test WHERE tt @> CURRENT_TIMESTAMP; > > CREATE RULE rule_test_insert AS ON INSERT TO rule_test_view DO INSTEAD > > INSERT INTO rule_test (tt, foo, bar) VALUES > (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *; > > CREATE RULE rule_test_delete AS ON DELETE TO rule_test_view DO INSTEAD > > UPDATE rule_test SET tt = tstzrange(lower(tt), > CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id RETURNING *; > > -- What I'd like to be able to do here is this: > > -- CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD ( > > -- UPDATE rule_test SET tt = tstzrange(lower(tt), > CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id; > > -- INSERT INTO rule_test (tt, foo, bar) VALUES > (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *; > > -- ); > > -- or, using the rules already defined, this: > > -- CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD ( > > -- DELETE FROM rule_test_view WHERE id = OLD.id; > > -- INSERT INTO rule_test_view (foo, bar) VALUES (NEW.foo, > NEW.bar) RETURNING *; > > -- ); > > -- but I can only get the desired effect using this trigger: > > CREATE OR REPLACE FUNCTION rule_test_update () > > RETURNS trigger > > AS > > $$ > > BEGIN > > DELETE FROM rule_test_view WHERE id = OLD.id; > > INSERT INTO rule_test_view (foo, bar) VALUES (NEW.foo, NEW.bar) > RETURNING tt into NEW.tt; > > RETURN NEW; > > END; > > $$ > > LANGUAGE plpgsql; > > CREATE TRIGGER rule_test_update_trig INSTEAD OF UPDATE ON rule_test_view > FOR EACH ROW EXECUTE PROCEDURE rule_test_update(); > > …And here is some code to test DML on the view > > -- ########################### > > -- 1. Insert some data, and check that RETURNING works > > INSERT INTO rule_test_view (foo, bar) VALUES (1, 1.0), (2, 2.0), (3, > 3.0) RETURNING *; > > -- 2. Check that tt has been populated for each row > > TABLE rule_test; > > -- 3. Check that all rows are visible in the view > > TABLE rule_test_view; > > -- 4. "Delete" one row, and check that RETURNING works > > DELETE FROM rule_test_view WHERE id = 1 RETURNING *; > > -- 5. Check that the row still exists in the table, but the upper limit > of tt is now set > > TABLE rule_test; > > -- 6. Check that the "deleted" row is no longer visible in the view > > TABLE rule_test_view; > > -- 7. "Update" one row, and check that RETURNING works > > UPDATE rule_test_view SET bar = 3.141 WHERE foo = 3 RETURNING *; > > -- 8. Check that the old version still exists in the table, but the > upper limit of tt is now set, > > -- and a new version now exists, holding the new value > > TABLE rule_test; > > -- 9. Check that the old version of the row is no longer visible in the view > > TABLE rule_test_view; > -- Adrian Klaver adrian.klaver@aklaver.com
On 01/25/2018 03:21 PM, Steven Winfield wrote: >> Don't use RULEs. HTH. > > Why not? > See: https://www.postgresql.org/docs/10/static/rules.html If you can understand what is going on then you are one up on me. -- Adrian Klaver adrian.klaver@aklaver.com
Maybe this?:
https://www.postgresql.org/docs/10/static/sql-createrule.html
"There is a catch if you try to use conditional rules for complex view
updates: there must be an unconditional INSTEAD rule for each action you
wish to allow on the view. If the rule is conditional, or is not
INSTEAD, then the system will still reject attempts to perform the
update action, because it thinks it might end up trying to perform the
action on the dummy table of the view in some cases. If you want to
handle all the useful cases in conditional rules, add an unconditional
DO INSTEAD NOTHING rule to ensure that the system understands it will
never be called on to update the dummy table. Then make the conditional
rules non-INSTEAD; in the cases where they are applied, they add to the
default INSTEAD NOTHING action. (This method does not currently work to
support RETURNING queries, however.)"
Thanks, I saw that – but none of my rules are conditional, and they are all INSTEAD OF, so I didn’t think that was the problem.
FYI, I gave up on RULE's a while back. Triggers are a lot easier to
figure out and maintain.
These rules (including the non-working UPDATE rule) seemed fairly small and quite readable,
so I didn’t think maintenance would be a problem.
Debugging this is a bit harder, however…
Steve.
On 01/25/2018 03:34 PM, Steven Winfield wrote: > Maybe this?: > > https://www.postgresql.org/docs/10/static/sql-createrule.html > > "There is a catch if you try to use conditional rules for complex view > updates: there must be an unconditional INSTEAD rule for each action you > wish to allow on the view. If the rule is conditional, or is not > INSTEAD, then the system will still reject attempts to perform the > update action, because it thinks it might end up trying to perform the > action on the dummy table of the view in some cases. If you want to > handle all the useful cases in conditional rules, add an unconditional > DO INSTEAD NOTHING rule to ensure that the system understands it will > never be called on to update the dummy table. Then make the conditional > rules non-INSTEAD; in the cases where they are applied, they add to the > default INSTEAD NOTHING action. (This method does not currently work to > support RETURNING queries, however.)" > > Thanks, I saw that – but none of my rules are conditional, and they are > all INSTEAD OF, so I didn’t think that was the problem. My mistake I saw: UPDATE rule_test SET tt = tstzrange(lower(tt), CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id; and in my head transformed that into a conditional RULE. > > FYI, I gave up on RULE's a while back. Triggers are a lot easier to > figure out and maintain. > > These rules (including the non-working UPDATE rule) seemed fairly small > and quite readable, > > so I didn’t think maintenance would be a problem. > > Debugging this is a bit harder, however… The reason I gave up on rules, figuring out how the query rewrite system works is a bear. I understand(most of the time) what triggers do, so I stick with what I understand. > > Steve. > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 01/25/2018 03:14 PM, Steven Winfield wrote:
>
> https://www.postgresql.org/docs/10/static/rules-triggers.html
>
> “For the things that can be implemented by both, which is best depends
> on the usage of the database. A trigger is fired once for each
> affected row. A rule modifies the query or generates an additional
> query. So if many rows are affected in one statement, a rule issuing
> one extra command is likely to be faster than a trigger that is called
> for every single row and must re-determine what to do many times.
> However, the trigger approach is conceptually far simpler than the
> rule approach, and is easier for novices to get right.”
>
Well like I said, it may be better for what you are doing. I am not sure
but I can say from personal implementation experience that for old
school partitioning (e.g; everything before 10), triggers were so much
faster than rules that the general rule was, "don't use rules". That may
have changed since that experience.
JD
/
------------------------------------------------------------------------
///
--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
***** Unless otherwise stated, opinions are my own. *****
There have been comments on- and off-list about rules generally being slower than rules, which seemed counterintuitive (for my case at least) so I’ve done some timings.
(Also note that I haven’t done any partitioning here, new- or old-style - a few people have mentioned RULEs in relation to partitioning).
These compare the INSERT and UPDATE rules against equivalent INSTEAD OF triggers. Best of three runs each time, times are in seconds.
rows 10^4 10^5 10^6
insert rule 0.9 15.0 179
insert trigger 1.3 19.7 224
delete rule 1.8 22.8 282
delete trigger 2.3 28.0 331
…so the rules are about 20% faster than the triggers. Significant, but not game-changing.
Note that this is on quite close to a “real life” table too - there is the maintenance of the primary key index and the gist index that supports the exclude constraint in all those timings, so a table without those would have shown a bigger disparity between the two methods.
This makes sense - the RULEs just have one query to rewrite before it is planned and executed, whereas the TRIGGERs have to be re-executed for each row.
Back to my original attempt at writing an UPDATE rule…
CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD (
UPDATE rule_test SET tt = tstzrange(lower(tt), CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id;
INSERT INTO rule_test (tt, foo, bar) VALUES (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *;
);
…I wondered whether the pseudo relations NEW and OLD were somehow being modified by the first command (the UPDATE), such that the second command (INSERT) could not function properly. That would fit with what I observe, but I’m not sure how I go about proving or fixing it.
Best,
Steven.
On 01/26/2018 04:19 PM, Steven Winfield wrote: > Back to my original attempt at writing an UPDATE rule… > > CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD ( > > UPDATE rule_test SET tt = tstzrange(lower(tt), > CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id; > > INSERT INTO rule_test (tt, foo, bar) VALUES > (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *; > > ); > > …I wondered whether the pseudo relations NEW and OLD were somehow being > modified by the first command (the UPDATE), such that the second command > (INSERT) could not function properly. No. It's because your view's WHERE clause is being added to your INSERT and of course nothing matches, so nothing gets inserted. > That would fit with what I observe, but I’m not sure how I go about > proving EXPLAIN > or fixing it. Don't use RULEs. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
No. It's because your view's WHERE clause is being added to your INSERT
and of course nothing matches, so nothing gets inserted.
> That would fit with what I observe, but I’m not sure how I go about
> proving
EXPLAIN
Thanks. You’re right - I should have thought to use EXPLAIN before posting here.
Don't use RULEs.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Repeating this, as if I should already know this, is less helpful though. It’s not as if the docs tells users to steer clear of RULEs.
If there is something I don’t understand, such as RULEs, I prefer to experiment with it and ask questions if there is something I still don’t follow. I thought this was the correct forum for those questions.
Don't use RULEs.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Repeating this, as if I should already know this, is less helpful though. It’s not as if the docs tells users to steer clear of RULEs.
If there is something I don’t understand, such as RULEs, I prefer to experiment with it and ask questions if there is something I still don’t follow. I thought this was the correct forum for those questions.
Don't use RULEs.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Repeating this, as if I should already know this, is less helpful though. It’s not as if the docs tells users to steer clear of RULEs.
If there is something I don’t understand, such as RULEs, I prefer to experiment with it and ask questions if there is something I still don’t follow. I thought this was the correct forum for those questions.
The docs do suggest that rules are more difficult to do correctly.Few people use them which means they generally get less exposure to a variety of circumstances during feature testing and use and the long-term maintenance dynamics are larger.That all said, if you can justify your use of them in a particular context you should probably just ignore those saying not to use them without the same knowledge of your situation. The reward may be worth the risk/cost but the people yelling out for you to be careful are not doing so for the fun of it. You should at least heed it to the point of saying, "yes, I'm sure I want to do this".
We have used RULEs to handle insertions / deletions from non-trivial views. And I can give this example if anyone wants.
Also we have used views to ignite smth like :
CREATE RULE account_credit_ins AS
ON INSERT TO account_credit DO INSTEAD SELECT public.rule_disable('INSERT'::text) AS rule_disable;
where rule_disable is a function that just raises an exception with some diagnostic text.
We dont make heavey use of RULEs, but I think they have their place.
David J.
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt