RE: PITR and Temp Tables

From: Huan Ruan <leohuanruan(at)gmail(dot)com>
Sent: Wednesday, April 20, 2022 2:18 PM
To: pgsql-general(at)lists(dot)postgresql(dot)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