From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: pg.franck@pachot.net
Subject: BUG #17770: SELECT FOR UPDATE on a UNION ALL view doesn't raise an error
Date: Thu, 02 Feb 2023 18:18:54 +0000
Message-ID: <17770-f9e90c19d082a231@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17770
Logged by: Franck Pachot
Email address: pg.franck@pachot.net
PostgreSQL version: 15.1
Operating system: x86_64-pc-linux-gnu
Description:
I guess that a SELECT FOR UPDATE on a view with UNION should raise an error
as it cannot lock the rows. The following test case works but without
locking the rows:
```
postgres@85a4c185b9ba:~$ psql
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
postgres=# create table demo_table
as select generate_series(1,3) as id , 0 as value;
vacuum analyze demo_table;
CREATE OR REPLACE VIEW demo_view AS
select id,value from demo_table where mod(id,2)=0
union all
select id,value from demo_table where mod(id,2)=1
;
SELECT 3
VACUUM
CREATE VIEW
postgres=# drop table demo_table cascade;
NOTICE: drop cascades to view demo_view
DROP TABLE
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# create table demo_table
as select generate_series(1,3) as id , 0 as value;
vacuum analyze demo_table;
SELECT 3
VACUUM
postgres=# CREATE OR REPLACE VIEW demo_view AS
select id,value from demo_table where mod(id,2)=0
union all
select id,value from demo_table where mod(id,2)=1
;
CREATE VIEW
postgres=# begin transaction;
select * from demo_view for update;
BEGIN
id | value
----+-------
2 | 0
1 | 0
3 | 0
(3 rows)
postgres=*#
postgres=*# \! psql -ec "UPDATE demo_table SET value = 1"
UPDATE demo_table SET value = 1
UPDATE 3
postgres=*#
postgres=*# select * from demo_view for update;
rollback;
id | value
----+-------
2 | 1
1 | 1
3 | 1
(3 rows)
ROLLBACK
```
The easy to copy/paste commands, as well as the execution plan, are here:
https://dev.to/aws-heroes/postgresql-when-locking-though-views-tldr-test-for-race-conditions-and-che...
Note that Tom Lane mentions in in
https://www.postgresql.org/message-id/flat/15676-8248e6b0beac09c6%40postgresql.org
that:
> it'd still be fairly weird to allow something like FOR UPDATE to propagate
down into the individual UNION arms from outside
which suggests that it should raise an error.
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, pg.franck@pachot.net
Subject: Re: BUG #17770: SELECT FOR UPDATE on a UNION ALL view doesn't raise an error
In-Reply-To: <17770-f9e90c19d082a231@postgresql.org>
* 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