Обсуждение: 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
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.
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
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