Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Mon, Aug 20, 2018 at 10:54:28AM -0300, Alvaro Herrera wrote: > Seems reasonable. Thanks Álvaro, pushed. -- Michael signature.asc Description: PGP signature

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On 2018-Aug-20, Michael Paquier wrote: > On Tue, Aug 14, 2018 at 06:53:32PM +0200, Michael Paquier wrote: > > I was thinking about adding "Even if it is not atomic" or such at the > > beginning of the paragraph, but at the end your phrasing sounds better > > to me. So I have hacked up the

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Tue, Aug 14, 2018 at 06:53:32PM +0200, Michael Paquier wrote: > I was thinking about adding "Even if it is not atomic" or such at the > beginning of the paragraph, but at the end your phrasing sounds better > to me. So I have hacked up the attached, which also reworks the comment > in

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Mon, Aug 13, 2018 at 01:53:18PM -0300, Alvaro Herrera wrote: > I do share Andres' concerns on the wording the comment. I would say > something like > > /* > * Reset the temporary namespace flag in MyProc. We assume this to be > * an atomic assignment. > * > * Because this subtransaction

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Mon, Aug 13, 2018 at 02:15:07PM -0300, Alvaro Herrera wrote: > If writing an OID were not atomic, the assignment would be really > dangerous. I don't think your proposed update is good. OK, I am withdrawing this one then. -- Michael signature.asc Description: PGP signature

Re: Temporary tables prevent autovacuum, leading to XID wraparound

If writing an OID were not atomic, the assignment would be really dangerous. I don't think your proposed update is good. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Mon, Aug 13, 2018 at 02:56:16AM -0700, Andres Freund wrote: > On 2018-08-09 18:50:47 +0200, Michael Paquier wrote: > I don't think that comment, nor the comment that you ended up > committing: > + > + /* > +* Reset the temporary namespace flag in MyProc. We assume that >

Re: Temporary tables prevent autovacuum, leading to XID wraparound

>From 1e9ba9fa9b172bda1ea54b1f3be1b930973ff45f Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Wed, 8 Aug 2018 19:45:31 +0200 Subject: [PATCH] Make autovacuum more aggressive to remove orphaned temp tables I was here to complain about this piece: > @@ -3975,6 +4033,15 @@

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On 2018-08-09 18:50:47 +0200, Michael Paquier wrote: > On Thu, Aug 09, 2018 at 02:29:54AM -0700, Andres Freund wrote: > + /* > +* Mark MyProc as owning this namespace which other processes can use to > +* decide if a temporary namespace is in use or not. We assume that > +*

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Aug 09, 2018 at 06:50:47PM +0200, Michael Paquier wrote: > Better ideas are of course welcome. I have gone back-and-forth on this patch for the last couple of days, reworded the comment blocks to outline the point Andres has been making, and I have finally been able to push it and

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Aug 09, 2018 at 02:29:54AM -0700, Andres Freund wrote: > On 2018-08-09 09:00:29 +0200, Michael Paquier wrote: >> Would you be fine if I add an extra note like what's in >> BackendIdGetProc? Say "The result may be out of date quickly, so the >> caller must be careful how to handle this

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On 2018-08-09 09:00:29 +0200, Michael Paquier wrote: > On Thu, Aug 09, 2018 at 08:32:32AM +0530, Andres Freund wrote: > > My point is that the documentation isn't sufficient. Not that there's an > > active problem. > > OK. Attached is the latest version if the patch I have that I was >

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Aug 09, 2018 at 08:32:32AM +0530, Andres Freund wrote: > My point is that the documentation isn't sufficient. Not that there's an > active problem. OK. Attached is the latest version if the patch I have that I was preparing for commit. On top of isTempNamespaceInUse I have this note: +

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On August 9, 2018 12:41:17 AM GMT+05:30, Michael Paquier wrote: >Hi Andres, > >(Not my intention to miss your message, I have just noticed it.) > >On Wed, Aug 08, 2018 at 01:41:27AM -0700, Andres Freund wrote: >> I can't parse this. "Even if this is an atomic operation, this can be >> safely

Re: Temporary tables prevent autovacuum, leading to XID wraparound

Hi Andres, (Not my intention to miss your message, I have just noticed it.) On Wed, Aug 08, 2018 at 01:41:27AM -0700, Andres Freund wrote: > I can't parse this. "Even if this is an atomic operation, this can be > safely done lock-less" - that seems like a contradictory sentence. Is > there a

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Wed, Aug 08, 2018 at 10:50:34AM -0400, Tom Lane wrote: > Alvaro Herrera writes: >> I'm unsure about pg11 -- is it a backbranch already or not? Since we've >> released beta3 already, ISTM we should consider it so. > > No. See >

Re: Temporary tables prevent autovacuum, leading to XID wraparound

Alvaro Herrera writes: > Unnoticed ABI breaks make my hair stand on end. Yeah. > I'm unsure about pg11 -- is it a backbranch already or not? Since we've > released beta3 already, ISTM we should consider it so. No. See https://www.postgresql.org/message-id/12725.1533737052%40sss.pgh.pa.us

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On 2018-Aug-08, Michael Paquier wrote: > As this introduces a new > field to PGPROC, so back-patching the thing as-is would cause an ABI > breakage. Are folks here fine with the new field added to the bottom of > the structure for the backpatched versions, including v11? I have found > about

Re: Temporary tables prevent autovacuum, leading to XID wraparound

Hi, On 2018-07-30 16:59:16 +0900, Michael Paquier wrote: > + /* > + * Mark MyProc as owning this namespace which other processes can use to > + * decide if a temporary namespace is in use or not. Even if this is an > + * atomic operation, this can be safely done lock-less as no

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Tue, Jul 31, 2018 at 01:39:07PM +0900, Kyotaro HORIGUCHI wrote: > "int backendID" is left in autovacuum.c as an unused variable. Fixed. > "Even if this is *not* an atomic operation" ? Yeah, I am reworking this comment a bit more to map with the other PGPROC fields. > + * Mark the proc

Re: Temporary tables prevent autovacuum, leading to XID wraparound

At Mon, 30 Jul 2018 16:59:16 +0900, Michael Paquier wrote in <20180730075916.gb2...@paquier.xyz> > On Fri, Jul 27, 2018 at 08:27:26AM +, Tsunakawa, Takayuki wrote: > > I don't have a strong opinion, but I wonder which of namespace.c or > > autovacuum.c is suitable, because

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Fri, Jul 27, 2018 at 08:27:26AM +, Tsunakawa, Takayuki wrote: > I don't have a strong opinion, but I wonder which of namespace.c or > autovacuum.c is suitable, because isTempNamespaceInUse is specific to > autovacuum. I think that there is also a point in allowing other backends to use it

RE: Temporary tables prevent autovacuum, leading to XID wraparound

Thank you, Michael and Horiguchi-san, From: Michael Paquier [mailto:mich...@paquier.xyz] > autovacuum.c is a pretty bad place for stuff as namespace.c holds all the > logic related to temporary tablespaces, so I renamed the routine to > isTempNamespaceInUse and moved it there. I don't have a

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Jul 26, 2018 at 07:05:11PM +0900, Kyotaro HORIGUCHI wrote: > At Wed, 18 Jul 2018 07:34:10 +, "Tsunakawa, Takayuki" > wrote in > <0A3221C70F24FB45833433255569204D1FA538FD@G01JPEXMBYT05> >> From: Michael Paquier [mailto:mich...@paquier.xyz] >>> + /* Does the backend own the temp

Re: Temporary tables prevent autovacuum, leading to XID wraparound

Hello. At Wed, 18 Jul 2018 07:34:10 +, "Tsunakawa, Takayuki" wrote in <0A3221C70F24FB45833433255569204D1FA538FD@G01JPEXMBYT05> > From: Michael Paquier [mailto:mich...@paquier.xyz] > > + /* Does the backend own the temp schema? */ > > + if (proc->tempNamespaceId != namespaceID) > > +

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Michael Paquier [mailto:mich...@paquier.xyz] > + /* 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. The lwlock in

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Tue, Mar 13, 2018 at 08:08:48AM +, Tsunakawa, Takayuki wrote: > From: Tom Lane [mailto:t...@sss.pgh.pa.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

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Tom Lane [mailto:t...@sss.pgh.pa.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

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Mar 8, 2018 at 1:09 PM, Tom Lane wrote: > We already have autovacuum taking care of that, and as I stated, asking > backends to do it is provably insufficient. The right path is to make > autovacuum cover the cases it's missing today. Well, your counter-proposal of

Re: Temporary tables prevent autovacuum, leading to XID wraparound

Robert Haas writes: > On Tue, Mar 6, 2018 at 6:39 PM, Tom Lane wrote: >> Now as for the problem originally stated, step 1 alone doesn't fix it, >> and there's reason not to like that change much. Forcing backends to >> clear their temp schemas

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Tue, Mar 6, 2018 at 6:39 PM, Tom Lane wrote: > Now as for the problem originally stated, step 1 alone doesn't fix it, > and there's reason not to like that change much. Forcing backends to > clear their temp schemas immediately on connection will slow down > connection

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Tom Lane [mailto:t...@sss.pgh.pa.us] > I just read through this thread for the first time; sorry for not paying > attention sooner. Don't mind, please. It's very happy that you gave attention now. > I'm uncomfortable with all the discussion of changing the autovacuum > launcher's

Re: Temporary tables prevent autovacuum, leading to XID wraparound

Robert Haas writes: > This is really two separate changes: > 1. Teach backends to remove any leftover pg_temp_%d schema on startup. > 2. Teach autovacuum to remove any leftover tables in a pg_temp_%d > schema if the backend is active but in some other database (rather >

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Sat, Feb 3, 2018 at 1:48 AM, Robert Haas wrote: > On Fri, Feb 2, 2018 at 1:27 AM, Masahiko Sawada wrote: >> Thank you for suggestion. It sounds more smarter. So it would be more >> better if we vacuums database for anti-wraparound in ascending

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Tue, Feb 6, 2018 at 6:03 PM, Michael Paquier wrote: > I am not sure that we would like to give up that easily the property > that we have now to clean up past temporary files only at postmaster > startup and only when not in recovery. If you implement that, there is

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Michael Paquier [mailto:michael.paqu...@gmail.com] > > postmaster deletes temporary relation files at startup by calling > > RemovePgTempFiles() regardless of whether it's in recovery. It > > doesn't call that function during auto restart after a crash when > > restart_after_crash is on. >

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Wed, Feb 07, 2018 at 12:37:50AM +, Tsunakawa, Takayuki wrote: > From: Michael Paquier [mailto:michael.paqu...@gmail.com] >> I am not sure that we would like to give up that easily the property that >> we have now to clean up past temporary files only at postmaster startup >> and only when

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Michael Paquier [mailto:michael.paqu...@gmail.com] > I am not sure that we would like to give up that easily the property that > we have now to clean up past temporary files only at postmaster startup > and only when not in recovery. If you implement that, there is a risk that > the backend

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Tue, Feb 06, 2018 at 11:33:59AM -0500, Robert Haas wrote: > This is really two separate changes: > > 1. Teach backends to remove any leftover pg_temp_%d schema on > startup. I am not sure that we would like to give up that easily the property that we have now to clean up past temporary files

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Sun, Feb 4, 2018 at 10:10 PM, Tsunakawa, Takayuki wrote: > The attached patch does the former. The small change in autovacuum.c is > mainly for autovac launcher and background workers which don't connect to a > database. I'll add this to the next CF. I'd

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Robert Haas [mailto:robertmh...@gmail.com] > Temporary tables contain XIDs, so they need to be vacuumed for XID > wraparound. Otherwise, queries against those tables by the session > that created them could yield wrong answers. However, autovacuum > can't perform that vacuuming; it would

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Fri, Feb 2, 2018 at 1:27 AM, Masahiko Sawada wrote: > Thank you for suggestion. It sounds more smarter. So it would be more > better if we vacuums database for anti-wraparound in ascending order > of relfrozenxid? Currently, we're doing it based on datfrozenxid. I was

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Masahiko Sawada [mailto:sawada.m...@gmail.com] > Thank you for suggestion. It sounds more smarter. So it would be more better > if we vacuums database for anti-wraparound in ascending order of > relfrozenxid? I thought so, too. The current behavior is inconsistent: the launcher tries to

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Fri, Feb 2, 2018 at 12:31 AM, Robert Haas wrote: > On Wed, Jan 31, 2018 at 7:37 PM, Masahiko Sawada > wrote: >> I think the idea would not be an improvement, but just change the >> policy. The current launcher's policy is "let's launch a new

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Wed, Jan 31, 2018 at 7:37 PM, Masahiko Sawada wrote: > I think the idea would not be an improvement, but just change the > policy. The current launcher's policy is "let's launch a new worker as > much as possible on the database that is at risk of wraparound most". > The

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Feb 1, 2018 at 2:01 AM, Robert Haas wrote: > On Tue, Jan 30, 2018 at 7:04 PM, Tsunakawa, Takayuki > wrote: >> So a simple improvement would be to assign workers fairly to databases >> facing a wraparound risk, as Sawada-san

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Tue, Jan 30, 2018 at 7:04 PM, Tsunakawa, Takayuki wrote: > So a simple improvement would be to assign workers fairly to databases facing > a wraparound risk, as Sawada-san suggested. Is that always an improvement, or does it make some cases better and others

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Masahiko Sawada [mailto:sawada.m...@gmail.com] > On Mon, Jan 29, 2018 at 3:33 PM, Tsunakawa, Takayuki > wrote: > > I can understand your concern. On the other hand, it's unfair that one > database could monopolize all workers, because other databases might

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Mon, Jan 29, 2018 at 3:33 PM, Tsunakawa, Takayuki wrote: > From: Masahiko Sawada [mailto:sawada.m...@gmail.com] >> What I thought is that a worker reports these two values after scanned >> pg_class and after freezed a table. The launcher decides to launch a new

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Masahiko Sawada [mailto:sawada.m...@gmail.com] > What I thought is that a worker reports these two values after scanned > pg_class and after freezed a table. The launcher decides to launch a new > worker if the number of tables requiring anti-wraparound vacuum is greater > than the number of

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Fri, Jan 26, 2018 at 2:22 PM, Tsunakawa, Takayuki wrote: > From: Masahiko Sawada [mailto:sawada.m...@gmail.com] >> On Thu, Jan 25, 2018 at 3:14 PM, Tsunakawa, Takayuki >> wrote: >> > * Why does autovacuum launcher always choose

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Robert Haas [mailto:robertmh...@gmail.com] > I think we should consider having backends try to remove their temporary > schema on startup; then, if a temp table in a backend is old enough that > it's due for vacuum for wraparound, have autovacuum kill the connection. > The former is

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Masahiko Sawada [mailto:sawada.m...@gmail.com] > On Thu, Jan 25, 2018 at 3:14 PM, Tsunakawa, Takayuki > wrote: > > * Why does autovacuum launcher always choose only one database when that > database need vacuuming for XID wraparound? Shouldn't it also choose

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Jan 25, 2018 at 1:14 AM, Tsunakawa, Takayuki wrote: > * I think temporary tables should not require vacuuming for XID wraparound. > Furtherover, should updates/deletes to temporary tables be in-place instead > of creating garbage, so that any form of

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Jan 25, 2018 at 3:14 PM, Tsunakawa, Takayuki wrote: > > FIX > > > I have the following questions. Along which line should I proceed to fix the > problem? > > * Why does autovacuum launcher always choose only one

RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Michael Paquier [mailto:michael.paqu...@gmail.com] > On Thu, Jan 25, 2018 at 08:10:00AM +, Tsunakawa, Takayuki wrote: > > I understood you suggested a new session which recycle the temp schema > > should erase the zombie metadata of old temp tables or recreate the > > temp schema. That

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Jan 25, 2018 at 08:10:00AM +, Tsunakawa, Takayuki wrote: >>> * In this incident, autovacuum worker misjudged that >>> pg_temp_3.fetchchunks can't be deleted, although the creator >>> (pg_rewind) is no longer active. How can we delete orphan temporary >>> tables safely? >> >> As long

Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Jan 25, 2018 at 06:14:41AM +, Tsunakawa, Takayuki wrote: > I don't know why pg_temp3.fetchchunks still exists. Maybe the user > ran pg_ctl stop -mi while pg_rewind was running. Likely that was the case :( As a superuser, DROP TABLE should work on the temporary schema of another