Re: Global temporary tables

On Mon, 06 Jul 2026 at 11:12, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On Mon, 6 Jul 2026 at 09:35, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>
>> 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.
>>
>> postgres=# BEGIN;
>> postgres=*# INSERT INTO gtt_delete (info) VALUES ('row 1');
>> postgres=*# SELECT * FROM gtt_delete;
>> postgres=*# SELECT currval('gtt_delete_id_seq');
>> postgres=*# END;
>> 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?
>>
>
> I think that's what I would expect. This matches what happens with
> normal (local) temp tables -- sequences are not reset after commit (or
> rollback, for that matter).
>

Thanks for the explanation! I hadn't noticed this before.

> Regarding pg_class.reloncommit, 'p' (preserve) seems correct for a
> sequence attached to a GTT with ON COMMIT DELETE ROWS, because the
> sequence data is preserved. For indexes, not so much, because the
> index data is deleted on commit. I guess it could be NULL for anything
> that's not a table.
>

+1 for setting it to NULL for non‑table objects, since CREATE INDEX and
CREATE SEQUENCE do not support the ON COMMIT clause.

> Andrew's patch uses a reloption to store the table's on-commit state,
> but to me that doesn't seem quite right, and it requires special code
> to mark it as a "private" reloption that is non-user-editable. It does
> simplify the question of what to do for non-tables though. What do
> others think?
>

I'm on board with your approach to the on-commit state.

> Regards,
> Dean

--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.