0

I try to create updatable views with check option.
When I have joins in the filtering criteria, I use a where in clause to simplify the queries.
However if I declare the view with both check option and where in, data validation doesn't seem reliable. I don't see this restriction in the doc: https://www.postgresql.org/docs/current/sql-createview.html

Is it a bug? Is there a workaround?

create table tableA (
    db_record_id serial NOT null PRIMARY KEY,
    "name" varchar(60) NOT null UNIQUE
);

create or replace view viewSimpleA
as select * from tableA where name like 'a%'
with check option;

create or replace view viewA
as select * from tableA
where db_record_id in (
    select db_record_id from tableA
    where name like 'a%'
) with check option;

insert into viewSimpleA(name) values('abc'); -- OK
delete from tableA;

insert into viewA(name) values('abc'); -- KO, SQL Error [44000]: ERROR: new row violates check option for view "viewa"
delete from tableA;

insert into viewSimpleA(name) values('abc');
update viewSimpleA set name = 'fine'; -- OK: update is prevented by check option
delete from tableA;

insert table viewA(name) values('abc');
update viewA set name = 'fine'; -- KO: update is executed, view is now empty
delete from tableA;

select version(); -- PostgreSQL 13.5 (Debian 13.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

EDIT: I created a bug report here

Kiruahxh
  • 1,276
  • 13
  • 30

2 Answers2

0

PostGreSQL does not support INSERT/UPDATE/DELETE in views that are constituted of many table. This include JOIN and subqueries.

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • Wrong: `viewSimpleA` is defined as automatically updatable by postgres. Otherwise, this error would happen: `SQL Error [0A000]: ERROR: WITH CHECK OPTION is supported only on automatically updatable views Indice : Views that do not select from a single table or view are not automatically updatable.` – Kiruahxh Dec 15 '21 at 13:38
  • Extract from : https://www.postgresql.org/docs/14/sql-createview.html Updatable Views Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions: The view must have exactly one entry in its FROM list, which must be a table or another updatable view. – SQLpro Dec 16 '21 at 15:03
  • I am talking about it on postgres bug mailing list. This view is updatable, as we can see with this request : `SELECT table_name, is_updatable FROM information_schema.views WHERE table_name LIKE 'view%a';` => `YES YES` If I remove check option, insert and update work fine. However, with "check option", the subquery ignores the new record value existence during validation step. That's why validation criteria is completely bypassed (they call it "non-immutable check") – Kiruahxh Dec 16 '21 at 15:59
0

A PostgreSQL view is updatable when it meets the following conditions:

-The defining query of the view must have exactly one entry in the FROM clause, which can be a table or another updatable view. -The defining query must not contain one of the following clauses at the top level: GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT. -The selection list must not contain any window function, any set-returning function, or any aggregate function such as SUM, COUNT, AVG, MIN, and MAX.

Mahboubeh
  • 1
  • 2