On Mon, 06 Jul 2026 at 11:12, Dean Rasheed <[email protected]> wrote: > On Mon, 6 Jul 2026 at 09:35, Japin Li <[email protected]> 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.
