public inbox for [email protected]
help / color / mirror / Atom feedFrom: Japin Li <[email protected]>
To: Dean Rasheed <[email protected]>
Cc: Kirk Wolak <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Global temporary tables
Date: Mon, 06 Jul 2026 16:35:06 +0800
Message-ID: <SY7PR01MB10921AF179BFC56D15C70894FB6F12@SY7PR01MB10921.ausprd01.prod.outlook.com> (raw)
In-Reply-To: <CAEZATCX+WMWxxT-He2r24Y96ZBbx1Z2b6zFDGm9gHjjYWviZ=Q@mail.gmail.com>
References: <CAEZATCU_goy9GiN75Rhqwg8=AEHrujYdf+cF9ORMGg98Tc44YA@mail.gmail.com>
<[email protected]>
<CAFj8pRDbGis5SPwaoGjyeVd9K0ynabEXfe-C3RzOF+d46WLOyA@mail.gmail.com>
<CAEZATCU0fg2JTYCJoY1m3FGmUMoDUHzDsQ9JExujWRrHz68sUg@mail.gmail.com>
<CAEZATCUdg2tGj1OWbSWMCubrQdXpGirQkdXNgQBgGf-UQG2hGA@mail.gmail.com>
<CACLU5mTSDkxr7R9MpXqO8Fi-TL-V+JO_f4vQFMU92OhhDYTtww@mail.gmail.com>
<CAEZATCUqP6iu0QcuZ+718iz630hsfQLdqZdhW=B3+vz6iOuq9Q@mail.gmail.com>
<SY7PR01MB1092104CDB43530A408012EAFB6F62@SY7PR01MB10921.ausprd01.prod.outlook.com>
<CAEZATCX+WMWxxT-He2r24Y96ZBbx1Z2b6zFDGm9gHjjYWviZ=Q@mail.gmail.com>
Hi, Dean
Thanks for updating the v6 patch.
On Sat, 04 Jul 2026 at 00:37, Dean Rasheed <[email protected]> wrote:
> On Wed, 1 Jul 2026 at 07:24, Japin Li <[email protected]> wrote:
>>
>> While testing the v5 patch, I encountered a lock wait.
>>
>> 2026-07-01 14:18:43.603 CST [1486593] LOG: process 1486593 still waiting for AccessExclusiveLock on relation 16384 of database 5 after 1000.106 ms
>> 2026-07-01 14:18:43.603 CST [1486593] DETAIL: Process holding the lock: 1486484. Wait queue: 1486593.
>> 2026-07-01 14:18:43.603 CST [1486593] CONTEXT: waiting for AccessExclusiveLock on relation 16384 of database 5
>> 2026-07-01 14:18:43.603 CST [1486593] STATEMENT: SELECT * FROM gtt_delete;
>>
>> To reproduce:
>>
>> Session 1:
>>
>> CREATE GLOBAL TEMPORARY TABLE gtt_delete (id int) ON COMMIT DELETE ROWS;
>> BEGIN;
>> INSERT INTO gtt_delete VALUES (1);
>>
>> Session 2:
>>
>> SELECT * FROM gtt_delete; ---> blocked
>>
>> Is this expected?
>
> Thanks for testing!
>
> This is fixed in v6.
>
I tested the v6 patch, and the blocked is fixed.
During testing of the v6 patch, I observed that sequences belonging to global
temporary tables defined with ON COMMIT DELETE ROWS are not reset after the
transaction commits.
This is similar to the index I found in [1]. Here is the example:
INSERT INTO gtt_delete (info) VALUES ('row 1');
postgres=# CREATE GLOBAL TEMPORARY TABLE gtt_delete (id serial primary key, info text) ON COMMIT DELETE ROWS;
CREATE TABLE
postgres=# SELECT relname, reloncommit FROM pg_class WHERE relname ~ '^gtt_delete.*';
relname | reloncommit
-------------------+-------------
gtt_delete | d
gtt_delete_id_seq | p <-- The sequence is marked as PRESERVE.
gtt_delete_pkey | p
(3 rows)
postgres=# SELECT currval('gtt_delete_id_seq');
ERROR: currval of sequence "gtt_delete_id_seq" is not yet defined in this session
postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO gtt_delete (info) VALUES ('row 1');
INSERT 0 1
postgres=*# SELECT * FROM gtt_delete;
id | info
----+-------
1 | row 1
(1 row)
postgres=*# SELECT currval('gtt_delete_id_seq');
currval
---------
1
(1 row)
postgres=*# END;
COMMIT
postgres=# SELECT currval('gtt_delete_id_seq'); -- The sequence still exists.
currval
---------
1
(1 row)
postgres=*# END;
COMMIT
postgres=# SELECT currval('gtt_delete_id_seq'); -- The sequence doesn't reset.
currval
---------
2
(1 row)
The sequence doesn't reset after commit – is this intended?
[1] https://postgr.es/m/SY7PR01MB1092159B3ED3CA6F4401E465EB6F42@SY7PR01MB10921.ausprd01.prod.outlook.com
> Regards,
> Dean
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
view thread (492+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Global temporary tables
In-Reply-To: <SY7PR01MB10921AF179BFC56D15C70894FB6F12@SY7PR01MB10921.ausprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox