Re: PITR and Temp Tables

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Huan Ruan <leohuanruan(at)gmail(dot)com> 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
>
>
>