Thread: Issues with Information_schema.views
Hello
The following was tested in a PostgreSQL (16) database. In my opinion queries based on Information_schema.views sometimes give unexpected results.
CREATE TABLE Dept(deptno SMALLINT NOT NULL,
dname VARCHAR(50) NOT NULL,
CONSTRAINT pk_dept PRIMARY KEY (deptno));
CREATE TABLE Emp(empno INTEGER NOT NULL,
ename VARCHAR(50) NOT NULL,
deptno SMALLINT NOT NULL,
CONSTRAINT pk_emp PRIMARY KEY (empno),
CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES Dept(deptno) ON UPDATE CASCADE);
CREATE VIEW emps AS SELECT *
FROM Dept INNER JOIN Emp USING (deptno);
UPDATE Emps SET ename=Upper(ename);
/*ERROR: cannot update view "emps"
DETAIL: Views that do not select from a single table or view are not automatically updatable.
HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.*/
SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into
FROM Information_schema.views
WHERE table_name='emps';
/*is_updatable=NO and is_insertable_into=NO*/
CREATE OR REPLACE RULE emps_insert AS ON INSERT
TO Emps
DO INSTEAD NOTHING;
/*After that: is_insertable_into=YES*/
CREATE OR REPLACE RULE emps_update AS ON UPDATE
TO Emps
DO INSTEAD NOTHING;
/*After that: is_updatable=NO*/
CREATE OR REPLACE RULE emps_delete AS ON DELETE
TO Emps
DO INSTEAD NOTHING;
/*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.
2. is_updatable=YES only after I add both UPDATE and DELETE DO INSTEAD NOTHING rules.
My question is: are 1 and 2 the expected behaviour or is there a mistake in the implementation of the information_schema view?
Best regards
Erki EessaarOn 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 changethe 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
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.
On Sun, Oct 29, 2023 at 4:05 PM Erki Eessaar <erki.eessaar@taltech.ee> wrote: > > Hello > > Thank you! I know that. > > > 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 > per test, it's the expected behavior. https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/updatable_views.out#n569 https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/updatable_views.out#n603 https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/updatable_views.out#n637 you need CREATE RULE AS ON DELETE and CREATE RULE AS ON UPDATE to mark the view as is_updatable.
Erki Eessaar <erki.eessaar@taltech.ee> writes:
> My question is - is all of this the intended behaviour by the implementers?
Yes, I'd say so. If you are expecting that the is_updatable flag
will check to see if the behavior provided by the view's rules
corresponds to something that a human would call a corresponding
update of the view's output, you're out of luck. There's a little
issue called the halting problem. So the actual check just looks
to see if there's unconditional DO INSTEAD rules of the appropriate
types, and doesn't probe into what those rules do.
regards, tom lane