Re: Global temporary tables

Hi, Dean

Thanks for updating the v6 patch.

On Sat, 04 Jul 2026 at 00:37, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On Wed, 1 Jul 2026 at 07:24, Japin Li <japinli(at)hotmail(dot)com> 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.