Updatable view (where in) with check option doesn't validate data properly - Mailing list pgsql-bugs

From Nicolas M
Subject Updatable view (where in) with check option doesn't validate data properly
Date
Msg-id CAJcNL1JZaFRZUM0r3SATJiuzvDArZtshHawMHXtqxh48BahmQQ@mail.gmail.com
Whole thread Raw
Responses Re: Updatable view (where in) with check option doesn't validate data properly  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs

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 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 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

pgsql-bugs by date:

Previous
From: "James Pang (chaolpan)"
Date:
Subject: RE: BUG #17326: Postgres crashed when pg_reload_conf() with ssl certificate parameters
Next
From: Tom Lane
Date:
Subject: Re: Updatable view (where in) with check option doesn't validate data properly