PITR and Temp Tables

From: Huan Ruan <leohuanruan@gmail.com>
Sent: Wednesday, April 20, 2022 2:18 PM
To: pgsql-general@lists.postgresql.org
Subject: PITR and Temp Tables

Hi All

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?

Thanks
Huan

Hi,

My guess is that temp table entries will still be in your catalog until you do a VACUUM FULL of the pg_class / pg_attribute tables.
But you should not care about these entries if these tables are vacuumed at regular intervals.

Regards,
Patrick

Hi Patrick

Thanks for your reply.

My guess is that temp table entries will still be in your catalog until
you do a VACUUM FULL of the pg_class / pg_attribute tables.

But you should not care about these entries if these tables are vacuumed
at regular intervals.

What I observed in one instance seems* to be like this - by the time I
connected to the recovered database (as soon as I could), an autovacuuum
has run on those catalog tables and they don't have those temp table
entries. Normally, autovacuum removes dead tuples, but those temp table
records are live tuples at T0. So if it was autovacuum that removed them in
the recovered database, were they live or dead tuples? If they are dead,
what did the deletion that made them dead?

*I would like to confirm to be 100% sure but was wondering if I can get an
explanation here.

Regards
Huan

Huan Ruan <leohuanruan@gmail.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

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@sss.pgh.pa.us> wrote:

Many thanks for the explanation, Tom and Greg. That all makes sense.

Cheers
Huan