From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: smartkeyerror@gmail.com
Subject: BUG #17812: LOCK TABLE IN ACCESS EXCLUSIVE MODE with a view returns an empty tuple set
Date: Tue, 28 Feb 2023 03:12:38 +0000
Message-ID: <17812-206eaec1d133a94a@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17812
Logged by: Zhenglong Li
Email address: smartkeyerror@gmail.com
PostgreSQL version: 15.2
Operating system: Ubuntu 20.04
Description:
Step to reproduce the behavior
We need 2 sessions to reproduce this bug.
Firstly, we create a simple view that just fetches all the data from
table1.
And then we start a transaction and lock table1 with AccessExclusive Mode in
Read Committed Transaction Isolation Level.
After that, we try to use CTAS to create a temp table table2 using the data
from table1 in session2, and this will be blocked by AccessExclusive Lock.
Finally, we insert some data into table1 in session1 and commit it, session2
will continue, but there is no data in table2.
```sql
--------[ Sessions 1 ]--------
show transaction_isolation;
transaction_isolation
-----------------------
read committed
DROP TABLE IF EXISTS table1 CASCADE;
CREATE TABLE table1 (
zahl integer,
upd_dat timestamp without time zone
);
CREATE OR REPLACE VIEW view1 as select zahl,upd_dat from table1;
BEGIN;
LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE;
TRUNCATE TABLE table1;
--------[ Sessions 2 ]--------
show transaction_isolation;
transaction_isolation
-----------------------
read committed
DROP TABLE IF EXISTS table2 CASCADE;
CREATE TEMP TABLE table2 AS select zahl,upd_dat from view1;
-- this will hang now waiting for a lock form session1
--------[ Sessions 1 ]--------
SELECT clock_timestamp();
INSERT INTO table1 SELECT i zahl,clock_timestamp() upd_dat FROM
generate_series(1,10) a(i);
COMMIT;
--------[ Sessions 2 ]--------
SELECT * FROM table2 limit 10;
zahl | upd_dat
------+---------
(0 rows)
```
But when we used a table, there will have data:
```sql
--------[ Sessions 1 ]--------
DROP TABLE IF EXISTS table1 CASCADE;
CREATE TABLE table1 (
zahl integer,
upd_dat timestamp without time zone
);
CREATE OR REPLACE VIEW view1 as select zahl,upd_dat from table1;
BEGIN;
LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE;
TRUNCATE TABLE table1;
--------[ Sessions 2 ]--------
DROP TABLE IF EXISTS table2 CASCADE;
CREATE TEMP TABLE table2 AS select zahl,upd_dat from table1;
-- this will hang now waiting for a lock form session1
--------[ Sessions 1 ]--------
SELECT clock_timestamp();
INSERT INTO table1 SELECT i zahl,clock_timestamp() upd_dat FROM
generate_series(1,10) a(i);
COMMIT;
--------[ Sessions 2 ]--------
SELECT * FROM table2 limit 10;
zahl | upd_dat
------+----------------------------
1 | 2023-02-28 03:10:15.743068
2 | 2023-02-28 03:10:15.743196
3 | 2023-02-28 03:10:15.743203
4 | 2023-02-28 03:10:15.743206
5 | 2023-02-28 03:10:15.743208
6 | 2023-02-28 03:10:15.74321
7 | 2023-02-28 03:10:15.743212
8 | 2023-02-28 03:10:15.743214
9 | 2023-02-28 03:10:15.743216
10 | 2023-02-28 03:10:15.743218
(10 rows)
```
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, smartkeyerror@gmail.com
Subject: Re: BUG #17812: LOCK TABLE IN ACCESS EXCLUSIVE MODE with a view returns an empty tuple set
In-Reply-To: <17812-206eaec1d133a94a@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