There actually is a third backstop if no other session ever connects to that temp schema and cleans them out.
Eventually autovacuum notices that they would need a vacuum "to prevent wraparound". It can't actually did the vacuum on temp tables but if there's no session attached to the temp schema it drops them.
This normally takes quite a long time to reach so if you routinely have sessions using temp schemas it's unlikely to happen. But if you only use temp schemas manually then eventually it would.
On Wed., Apr. 20, 2022, 09:37 Tom Lane, <tgl@xxxxxxxxxxxxx> wrote:
Huan Ruan <leohuanruan@xxxxxxxxx> writes:
> Let's say at T0 a database has N session based temp tables. They would have
> corresponding records in the catalog tables like pg_class and pg_attribute
> that are visible to other sessions.
> At T1, I do a PITR to T0. That recovered database should not have those
> temp tables because the sessions they were created in are not present. My
> question is what events trigger the deletion of those temp tables' catalog
> records (e.g. pg_class and pg_attribute etc.) in the recovered database?
Those records will still be there in the catalogs, yes.
Cleaning out the contents of a temporary schema is not the responsibility
of the WAL/recovery system. It's done by live backends at two times:
1. A session that has used a temp schema will normally clean out the
contained objects when it exits.
2. As a backstop in case #1 fails, a session that is about to begin using
a temp schema will clean out any surviving contents.
So if you rewound to a point where some temp objects exist, it'd be the
responsibility of the first session that wants to use a given temp schema
to clean out those objects.
regards, tom lane
