Re: Update rules on views

On 05/26/2018 11:32 AM, Maroš Kollár wrote:
> Hello,
>
> I am currently evaluating multiple ways of denying certain updates on
> record AND indicating whether an update was denied because it did not
> match some criteria or if it simply was not found.
>
> One of these methods is using the rule system and behaves in an odd
> way.The unexpected results are indicated below.
>
> -- Function for debugging
> CREATE OR REPLACE FUNCTION public.test_debug(
> message text
> ) RETURNS text
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> RAISE NOTICE 'Calling test_debug: %', message;
> RETURN message;
> END; $function$;
>
> DROP TABLE test CASCADE ;
> -- Main table
> CREATE TABLE test(id int primary key, animal text NOT NULL, sound text
> NOT NULL);
> -- Populate table
> INSERT into test VALUES (1,'rabbit','purr'),(2,'fox','shriek');
> -- Create simple view
> CREATE VIEW test_view AS SELECT * FROM test;
> -- Unconditional fallback update rule
> CREATE OR REPLACE RULE test_fallback AS ON UPDATE TO test_view
> DO INSTEAD NOTHING;
> -- Rule to deny update if animal is the same
> CREATE RULE test_deny AS ON UPDATE TO test_view
> WHERE NEW.animal = OLD.animal
> DO INSTEAD (SELECT test_debug('deny'));
> -- Rule to allow update if animal was changed
> CREATE OR REPLACE RULE test_allow AS ON UPDATE TO test_view
> WHERE NEW.animal <> OLD.animal
> DO INSTEAD (
> UPDATE test SET
> animal = NEW.animal,
> sound = NEW.sound
> WHERE id = OLD.id;
> SELECT test_debug('allow');
> );
>
> -- Test an update that should be denied by the rule since animal is the same
> UPDATE test_view SET animal = 'rabbit', sound = 'bark' WHERE id = 1;
> -- NOTICE: Calling test_debug: deny
> -- test_debug
> -- --------
> -- deny
> -- (1 row)
> --
> -- UPDATE 0
>
> -- Check if the record was not altered
> SELECT * FROM test WHERE id = 1;
> -- id | animal | sound
> -- ----+--------+-------
> -- 1 | rabbit | purr
> -- (1 row)
>
> -- Test an update that should be accepted by the rule since animal is different
> UPDATE test_view SET animal = 'bear',sound = 'roar' WHERE id = 2;
> -- NOTICE: Calling test_debug: deny <--- expecting allow instead !
> -- test_debug
> -- ------------
> -- deny <--- expecting allow instead !
> -- (1 row)
> --
> -- UPDATE 1
>
> -- Check if the record was altered
> SELECT * FROM test WHERE id = 2;
> -- id | animal | sound
> -- ----+--------+-------
> -- 2 | bear | roar <--- record was updated as expected,
> although it returned 'deny'
> -- (1 row)
>
> -- Test an update on a record that does not exist
> UPDATE test_view SET animal = 'dog',sound = 'bark' WHERE id = 3;
> -- test_debug
> -- ------------
> -- (0 rows)
> --
> -- UPDATE 0
>
> Why do I see 'deny' on an update that was handled by the 'test_allow' rule?

RULEs are weird?

I have not used any in a long time. If you want to continue with this then:

https://www.postgresql.org/docs/9.6/static/sql-createrule.html

"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.)"

I would go with triggers though:

https://www.postgresql.org/docs/9.6/static/sql-createtrigger.html
"
INSTEAD OF INSERT/UPDATE/DELETE Views —
TRUNCATE — —
"

Their behavior is more predictable.

>
> All tests were run on postgres 9.6.7.
>
> Cheers
> Maroš
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com