Re: Global temporary tables

On Sat, Jul 4, 2026 at 6:33 AM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> So v6 now sets pg_class.relfrozenxid and pg_class.relminmxid to 0 for
> all global temporary relations, and only the pg_temp_class values are
> ever updated. That seems more logical, because a GTT has no storage
> until it is used by some session, so it makes sense for the frozen
> XIDs in pg_class to be 0.

Unfortunately, the cure seems to be worse than the disease:

-- Session 1
CREATE GLOBAL TEMPORARY TABLE g (a int);
-- Session 1 is no longer needed.

-- Session 2 (a fresh session that has never touched g)

BEGIN;
SELECT count(*) FROM g; -- first access here: queues g's pg_temp_class row
-- (still unflushed inside this transaction)
SAVEPOINT s1;
DROP TABLE g; -- removes that pending queue entry
ROLLBACK TO s1; -- pg_class row is restored by MVCC;
-- the queue entry is not restored
INSERT INTO g VALUES (1); -- the table still works normally
COMMIT;

-- The table is live and holds committed data:
SELECT count(*) AS live_rows FROM g;
-- live_rows
-- -----------
-- 1

-- The table cannot be vacuumed.
-- On assert builds it hits an assert. On production builds...
VACUUM g;
-- WARNING: bypassing nonessential maintenance of table
"john.public.g" as a failsafe after 0 index scans
-- DETAIL: The table's relfrozenxid or relminmxid is too far in the past.
-- HINT: Consider increasing configuration parameter
"maintenance_work_mem" or "autovacuum_work_mem".
-- You might also need to consider other ways for VACUUM to keep up
with the allocation of transaction IDs.
-- ERROR: cache lookup failed for global temp relation 16385

-- The error is because there is no matching row in pg_temp_class
SELECT count(*) AS temp_class_rows
FROM pg_temp_class
WHERE oid = 'g'::regclass;
-- temp_class_rows
-- -----------------
-- 0

-- ...so dropping doesn't work either:
DROP TABLE g;
-- ERROR: cache lookup failed for global temp relation 16385

I'm starting to think the queue mechanism needs serious attention, if
not a complete re-think.

On Mon, Jun 22, 2026 at 8:43 PM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> > On Jun 22, 2026, at 4:57 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> > Oh, wow. There's a lot of similarity between our patchsets, which is
> > reassuring, but there are also a number of differences, which I need
> > to think about in more detail.
>
> Yeah, agree on both fronts. The main areas I see are the catalog and handling wraparound. I don’t think there’s necessarily a clear winner on either, so I’ll be interested to hear what others say.

I was actually hoping to hear more about how the approaches differ so
we can weigh the architectural tradeoffs from both operational and
maintenance standpoints. (I suppose I just volunteered myself for
that, didn't I...)

--
John Naylor
Amazon Web Services