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-check-execution-plan-with-buffers-verbose-28je
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.
