Updatable view (where in) with check option doesn't validate data properly

From: Nicolas M <kiruahxh(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Updatable view (where in) with check option doesn't validate data properly
Date: 2021-12-15 14:09:35
Message-ID: CAJcNL1JZaFRZUM0r3SATJiuzvDArZtshHawMHXtqxh48BahmQQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

https://stackoverflow.com/questions/70362651/postgres-updatable-view-where-in-with-check-option

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 is not reliable. I don't see this restriction in the doc:
https://www.postgresql.org/docs/current/sql-createview.html

It looks like a bug.

create table tableA (
db_record_id serial NOT null PRIMARY KEY,
"name" varchar(60) NOT null UNIQUE
);
create or replace view viewSimpleAas select * from tableA where name
like 'a%'with check option;
create or replace view viewAas select * from tableAwhere db_record_id in (
select db_record_id from tableA
where name like 'a%'
) with check option;
insert into viewSimpleA(name) values('abc'); -- OKdelete 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 into tableA(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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2021-12-16 01:05:54 Re: Updatable view (where in) with check option doesn't validate data properly
Previous Message James Pang (chaolpan) 2021-12-15 02:54:51 RE: BUG #17326: Postgres crashed when pg_reload_conf() with ssl certificate parameters