Re: Global temporary tables

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).

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