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

Поиск
Список
Период
Сортировка
От Nicolas M
Тема Updatable view (where in) with check option doesn't validate data properly
Дата
Msg-id CAJcNL1JZaFRZUM0r3SATJiuzvDArZtshHawMHXtqxh48BahmQQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Updatable view (where in) with check option doesn't validate data properly  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "James Pang (chaolpan)"
Дата:
Сообщение: RE: BUG #17326: Postgres crashed when pg_reload_conf() with ssl certificate parameters
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Updatable view (where in) with check option doesn't validate data properly