Re: Global temporary tables

On Mon, 6 Jul 2026 at 09:35, Japin Li <japinli@hotmail.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).

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.

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?

Regards,
Dean