Errors when update a view with conditional-INSTEAD rules

From: Pengzhou Tang <ptang(at)pivotal(dot)io>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Errors when update a view with conditional-INSTEAD rules
Date: 2019-12-03 11:05:34
Message-ID: CAG4reAQn+4xB6xHJqWdtE0ve_WqJkdyCV4P=trYr4Kn8_3_PEA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Hackers,

I hit an error when updating a view with conditional INSTEAD OF rules, the
reproduce steps are list below:

CREATE TABLE t1(a int, b int);

CREATE TABLE t2(a int, b int);

CREATE VIEW v1 AS SELECT * FROM t1 where b > 100;

INSERT INTO v1 values(1, 110);

SELECT * FROM t1;

CREATE OR REPLACE rule r1 AS

ON UPDATE TO v1

WHERE old.a > new.b

DO INSTEAD (

INSERT INTO t2 values(old.a, old.b);

);

UPDATE v1 SET b = 2 WHERE a = 1;

*ERROR: no relation entry for relid 2*

With some hacks, It is because, for conditional INSTEAD OF rules
conditional, the original UPDATE operation also need to perform on the
view, however, we didn't rewrite the target view for any view with INSTEAD
rules.

There should be only two cases that you can skip the rewrite of target view:
1) the view has INSTEAD OF triggers on the operations, the operations will
be replaced by trigger-defined
2) the view has INSTEAD OF rules and it is non conditional rules, the
operations will be replaced by actions.

It should be a typo in commit a99c42f291421572aef2, there is a description
in documents:
"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."

Commit a99c42f291421572aef2 explicitly change the description that the
restriction only applies to complex view, conditional INSTEAD rule should
work for a simple view.

I attached a patch to fix it, please take a look,

Thanks,
Pengzhou

Attachment Content-Type Size
0001-Rewrite-the-target-view-if-it-has-conditional-INSTEA.patch application/octet-stream 3.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2019-12-03 11:08:54 Re: Update minimum SSL version
Previous Message John Naylor 2019-12-03 11:02:17 Re: benchmarking Flex practices