public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erki Eessaar <[email protected]>
To: [email protected] <[email protected]>
Subject: Issues with Information_schema.views
Date: Sat, 28 Oct 2023 09:27:33 +0000
Message-ID: <AM9PR01MB8268A67C433FA16541AABBEDFEA3A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com> (raw)

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 Eessaar

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Issues with Information_schema.views
  In-Reply-To: <AM9PR01MB8268A67C433FA16541AABBEDFEA3A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox