Re: Global temporary tables

Hi, Dean

Thanks for updating the v6 patch.

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