From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nicolas M <kiruahxh@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Updatable view (where in) with check option doesn't validate data properly
Date: Wed, 15 Dec 2021 20:05:54 -0500
Message-ID: <843836.1639616754@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJcNL1JZaFRZUM0r3SATJiuzvDArZtshHawMHXtqxh48BahmQQ@mail.gmail.com>
References: <CAJcNL1JZaFRZUM0r3SATJiuzvDArZtshHawMHXtqxh48BahmQQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, kiruahxh@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Updatable view (where in) with check option doesn't validate data properly
In-Reply-To: <843836.1639616754@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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