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.klaver@xxxxxxxxxxx
