Re: A little RULE help? - Mailing list pgsql-general

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