Update rules on views

Поиск
Список
Период
Сортировка
От Maroš Kollár
Тема Update rules on views
Дата
Msg-id CALu__wUDPhidAJM_MVuRb8QV9LVRxoUp5ps-37bDN5uz3kQDmg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Update rules on views  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
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?

All tests were run on postgres 9.6.7.

Cheers
Maroš


В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: Fast logical replication jump start with PG 10
Следующее
От: Olivier Gautherot
Дата:
Сообщение: Re: Fast logical replication jump start with PG 10