Обсуждение: BUG #17770: SELECT FOR UPDATE on a UNION ALL view doesn't raise an error
BUG #17770: SELECT FOR UPDATE on a UNION ALL view doesn't raise an error
От
PG Bug reporting form
Дата:
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.