Re: Temporary tables prevent autovacuum, leading to XID wraparound

From: Michael Paquier <michael(at)paquier(dot)xyz>
To: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
Cc: 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Temporary tables prevent autovacuum, leading to XID wraparound
Date: 2018-07-13 04:29:57
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, Mar 13, 2018 at 08:08:48AM +0000, Tsunakawa, Takayuki wrote:
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>> On the whole, my vote is to fix and apply step 2, and leave it at that.

Yeah, I have been thinking about the idea 1 mentioned above, or in short
clean up the temporary namespace at connection start instead of
first-use of it, and while that would make the cleanup more aggressive,
it could be possible as well that only having autovacuum do the work
could be enough, so I am +-0 on this idea.

> Done. It seems to work well.

I have looked at the patch proposed.

+ /* Does the backend own the temp schema? */
+ if (proc->tempNamespaceId != namespaceID)
+ return false;
I have a very hard time believing that this is safe lock-less, and a
spin lock would be enough it seems.

+ /* Is the backend connected to this database? */
+ if (proc->databaseId != MyDatabaseId)
+ return false;
Wouldn't it be more interesting to do this cleanup as well if the
backend is *not* connected to the database autovacuum'ed? This would
make the cleanup more aggresive, which is better.

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2018-07-13 04:34:47 Re: How to make partitioning scale better for larger numbers of partitions
Previous Message Thomas Munro 2018-07-13 04:23:02 Re: Cache invalidation after authentication (on-the-fly role creation)