RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
To: 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: Temporary tables prevent autovacuum, leading to XID wraparound
Date: 2018-03-13 08:08:48
Message-ID: 0A3221C70F24FB45833433255569204D1F901BC1@G01JPEXMBYT05
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> So the correct fix is to improve autovacuum's check to discover whether
> an old temp table is orphaned, so that it isn't fooled by putative owner
> processes that are connected to some other DB. Step 2 of the proposed patch
> tries to do that, but it's only half right: we need a change near line 2264
> not only line 2090. (Evidently, we need either a comment that the logic
> is repeated, or else refactor so that there's only one copy.)
>
> Now, you can argue that autovacuum's check can be fooled by an "owner"
> backend that is connected to the current DB but hasn't actually taken
> possession of its assigned temp schema (and hence the table in question
> really is orphaned after all). This edge case could be taken care of by
> having backends clear their temp schema immediately, as in step 1 of the
> patch. But I still think that that is an expensive way to catch what would
> be a really infrequent case. Perhaps a better idea is to have backends
> advertise in PGPROC whether they have taken possession of their assigned
> temp schema, and then autovacuum could ignore putative owners that aren't
> showing that flag. Or we could just do nothing about that, on the grounds
> that nobody has shown the case to be worth worrying about.
> Temp schemas that are sufficiently low-numbered to be likely to have an
> apparent owner are also likely to get cleaned out by actual temp table
> creation reasonably often, so I'm not at all convinced that we need a
> mechanism that covers this case. We do need something for the cross-DB
> case though, because even a very low-numbered temp schema can be problematic
> if it's in a seldom-used DB, which seems to be the case that was complained
> of originally.
>
> On the whole, my vote is to fix and apply step 2, and leave it at that.

Done. It seems to work well.

Regards
Takayuki Tsunakawa

Attachment Content-Type Size
reset_temp_schema_v2.patch application/octet-stream 4.5 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message John Naylor 2018-03-13 08:39:42 Re: MCV lists for highly skewed distributions
Previous Message Michael Paquier 2018-03-13 08:04:04 Re: Problem while setting the fpw with SIGHUP