Re: Issues with Information_schema.views - Mailing list pgsql-hackers
| From | Erki Eessaar |
|---|---|
| Subject | Re: Issues with Information_schema.views |
| Date | |
| Msg-id | AM9PR01MB82688424D4ADFEB9D3784545FEA2A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com Whole thread Raw |
| In response to | Re: Issues with Information_schema.views (jian he <jian.universality@gmail.com>) |
| Responses |
Re: Issues with Information_schema.views
Re: Issues with Information_schema.views |
| List | pgsql-hackers |
Hello
Thank you! I know that.
DO INSTEAD NOTHING rules on updatable views could be used as a way to implement WITH READ ONLY constraint (one can define such constraint in Oracle). However, one could accidentally add such rule to non-updatable view as well.
I tried to construct a system-catalog based query to find database rules that are unnecessary. Thus, for the testing purposes I added a DO INSTEAD NOTHING rule to already non-updatable view and was a bit surprised that INFORMATION_SCHEMA-based check showed that the view had become updatable. A possible reasoning is that I can update the view without getting an error. However, I still cannot change data in base tables.
Secondly, the rule you demonstrated does not alone change IS_UPDATABLE value to YES. I have to create two rules:
CREATE OR REPLACE RULE emps_update AS ON UPDATE
TO Emps
DO INSTEAD UPDATE emp SET
empno = NEW.empno,
ename = NEW.ename,
deptno = NEW.deptno; CREATE OR REPLACE RULE emps_delete AS ON DELETE
TO Emps
DO INSTEAD DELETE FROM Emp WHERE empno=OLD.empno; My question is - is all of this the intended behaviour by the implementers?
Best regards
Erki Eessaar
From: jian he <jian.universality@gmail.com>
Sent: Saturday, October 28, 2023 13:38
To: Erki Eessaar <erki.eessaar@taltech.ee>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: Issues with Information_schema.views
Sent: Saturday, October 28, 2023 13:38
To: Erki Eessaar <erki.eessaar@taltech.ee>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: Issues with Information_schema.views
On Sat, Oct 28, 2023 at 5:27 PM Erki Eessaar <erki.eessaar@taltech.ee> wrote:
>
> Hello
>
>
> /*After that: is_updatable=YES*/
>
> 1. Indeed, now I can execute INSERT/UPDATE/DELETE against the view without getting an error. However, I still cannot change the data in the database through the views.
https://www.postgresql.org/docs/current/sql-createview.html
"
A more complex view that does not satisfy all these conditions is
read-only by default: the system will not allow an insert, update, or
delete on the view. You can get the effect of an updatable view by
creating INSTEAD OF triggers on the view, which must convert attempted
inserts, etc. on the view into appropriate actions on other tables.
For more information see CREATE TRIGGER. Another possibility is to
create rules (see CREATE RULE), but in practice triggers are easier to
understand and use correctly.
"
You CAN get the effect of an updateable view. But you need to make the
rule/triggers correct.
the following RULE can get the expected result.
CREATE OR REPLACE RULE emps_update AS ON UPDATE
TO Emps
DO INSTEAD UPDATE emp SET
empno = NEW.empno,
ename = NEW.ename,
deptno = NEW.deptno;
you can also look at src/test/regress/sql/triggers.sql,
src/test/regress/sql/rules.sql for more test cases.
>
> Hello
>
>
> /*After that: is_updatable=YES*/
>
> 1. Indeed, now I can execute INSERT/UPDATE/DELETE against the view without getting an error. However, I still cannot change the data in the database through the views.
https://www.postgresql.org/docs/current/sql-createview.html
"
A more complex view that does not satisfy all these conditions is
read-only by default: the system will not allow an insert, update, or
delete on the view. You can get the effect of an updatable view by
creating INSTEAD OF triggers on the view, which must convert attempted
inserts, etc. on the view into appropriate actions on other tables.
For more information see CREATE TRIGGER. Another possibility is to
create rules (see CREATE RULE), but in practice triggers are easier to
understand and use correctly.
"
You CAN get the effect of an updateable view. But you need to make the
rule/triggers correct.
the following RULE can get the expected result.
CREATE OR REPLACE RULE emps_update AS ON UPDATE
TO Emps
DO INSTEAD UPDATE emp SET
empno = NEW.empno,
ename = NEW.ename,
deptno = NEW.deptno;
you can also look at src/test/regress/sql/triggers.sql,
src/test/regress/sql/rules.sql for more test cases.
pgsql-hackers by date: