pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdatable view (where in) with check option doesn't validate data properly
3+ messages / 2 participants
[nested] [flat]
* Updatable view (where in) with check option doesn't validate data properly
@ 2021-12-15 14:09 Nicolas M <kiruahxh@gmail.com>
2021-12-16 01:05 ` Re: Updatable view (where in) with check option doesn't validate data properly Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Nicolas M @ 2021-12-15 14:09 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Updatable view (where in) with check option doesn't validate data properly
2021-12-15 14:09 Updatable view (where in) with check option doesn't validate data properly Nicolas M <kiruahxh@gmail.com>
@ 2021-12-16 01:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-16 09:46 ` Re: Updatable view (where in) with check option doesn't validate data properly Nicolas M <kiruahxh@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-12-16 01:05 UTC (permalink / raw)
To: Nicolas M <kiruahxh@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Nicolas M <kiruahxh@gmail.com> writes:
> create or replace view viewSimpleA as select * from tableA where name
> like 'a%'with check option;
[ behaves as expected ]
> create or replace view viewA as select * from tableAwhere db_record_id in (
> select db_record_id from tableA
> where name like 'a%'
> ) with check option;
[ not so much ]
I don't think this is a bug. The problem is that we handle
WITH CHECK OPTION by seeing whether the proposed new tuple
value satisfies the view's WHERE clause. But the new tuple
isn't yet stored, or at least isn't yet visible, so that
that sub-select still finds the old row contents (or fails
to find any row at all, in your first test).
We could maybe hack our way to fixing that in the specific
case you show here, but I'm not very excited about that,
because this usage of WITH CHECK OPTION seems inherently
unsafe. For example, if we permit an update based on the
fact that there's currently a row with "name like 'a%'",
there's no way to be certain that some other transaction
hasn't concurrently changed or deleted that row. That'd
lead to inconsistency once both transactions commit.
In general I think that expecting WITH CHECK OPTION to
enforce non-immutable conditions is pretty hazardous,
for largely the same reasons that a non-immutable CHECK
constraint is hazardous. We don't stop you from declaring
such a constraint, but it's likely to bite you in the rear.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Updatable view (where in) with check option doesn't validate data properly
2021-12-15 14:09 Updatable view (where in) with check option doesn't validate data properly Nicolas M <kiruahxh@gmail.com>
2021-12-16 01:05 ` Re: Updatable view (where in) with check option doesn't validate data properly Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-12-16 09:46 ` Nicolas M <kiruahxh@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Nicolas M @ 2021-12-16 09:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
To add more context about this issue, I am currently porting a software
which works with both Oracle and MSSQL.
Those two RDBMS support views with "check option" that contain either
subqueries or inner joins. In practice we also use "with recursive" in some
subqueries.
Using "check option" without self table subqueries or inner joins puts a
big restriction on what can be achieved through this mechanism.
The fact that view creation is supported in postgres but yields undefined
behavior bothers me. I would have to rewrite a lot of views to make them
compatible with postgres; and then do extensive tests for each of them to
validate postgres behavior upon inserts and updates. The safety net is very
thin here, and I guess postgres implementation could change at every moment
making the application crash with new versions of the RDBMS.
Regards,
Nicolas M
Le jeu. 16 déc. 2021 à 02:05, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> Nicolas M <kiruahxh@gmail.com> writes:
> > create or replace view viewSimpleA as select * from tableA where name
> > like 'a%'with check option;
>
> [ behaves as expected ]
>
> > create or replace view viewA as select * from tableAwhere db_record_id
> in (
> > select db_record_id from tableA
> > where name like 'a%'
> > ) with check option;
>
> [ not so much ]
>
> I don't think this is a bug. The problem is that we handle
> WITH CHECK OPTION by seeing whether the proposed new tuple
> value satisfies the view's WHERE clause. But the new tuple
> isn't yet stored, or at least isn't yet visible, so that
> that sub-select still finds the old row contents (or fails
> to find any row at all, in your first test).
>
> We could maybe hack our way to fixing that in the specific
> case you show here, but I'm not very excited about that,
> because this usage of WITH CHECK OPTION seems inherently
> unsafe. For example, if we permit an update based on the
> fact that there's currently a row with "name like 'a%'",
> there's no way to be certain that some other transaction
> hasn't concurrently changed or deleted that row. That'd
> lead to inconsistency once both transactions commit.
>
> In general I think that expecting WITH CHECK OPTION to
> enforce non-immutable conditions is pretty hazardous,
> for largely the same reasons that a non-immutable CHECK
> constraint is hazardous. We don't stop you from declaring
> such a constraint, but it's likely to bite you in the rear.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-12-16 09:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-15 14:09 Updatable view (where in) with check option doesn't validate data properly Nicolas M <kiruahxh@gmail.com>
2021-12-16 01:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-16 09:46 ` Nicolas M <kiruahxh@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox