On Mon, Aug 20, 2018 at 10:54:28AM -0300, Alvaro Herrera wrote:
> Seems reasonable.
Thanks Álvaro, pushed.
--
Michael
signature.asc
Description: PGP signature
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 attache
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 InitTempT
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 i
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
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
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
> +
>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 @@ AtEOSubXact_Name
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
> +* assignmen
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 back-pat
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 info
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
> preparing
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:
+
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 d
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 "not
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
> https://www.postgresql.org/message-id/12725.1533737052%40sss.pgh.
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
At
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 com
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
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 entry
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 isTempNamespaceInUs
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
a
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 stro
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 sche
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)
> > +
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 Ba
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
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 it's
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 teaching autovacuum to
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 immediately on connection will slow down
>> connecti
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 times, and for applicati
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 algorith
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
> than only when the backen
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 order
>> of relfrozenxid?
>
> Currently, we're doi
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
> a risk that the backend y
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.
>
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 no
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
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 o
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 like this to be back-patched.
This
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 ha
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 looking for a
small, rel
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
as
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 worker as
>> much as possible on the database that
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 idea I suggested makes
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 suggested.
>
> Is that always an improvement, or does it make so
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 worse?
> One ultimate solution sh
From: Robert Haas [mailto:robertmh...@gmail.com]
> Unfortunately, I think a full solution to the problem of allocating AV
> workers to avoid wraparound is quite complex.
Yes, that easily puts my small brain into an infinite loop...
> Given all of the foregoing this seems like a very hard problem.
On Mon, Jan 29, 2018 at 1:33 AM, Tsunakawa, Takayuki
wrote:
>> Since the above idea would be complex a bit, as an
>> alternated idea it might be better to specify the number of worker to launch
>> per database by a new GUC parameter or something. If the number of worker
>> running on the database
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 also
> be facing wraparound risk.
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
>> worker if the number of tables
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
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 only one database when that
>> database need vacuuming for XID wra
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 necessary
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 other
> databases?
>
> Yeah, I'
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 vacuum is unnecessary? Other
> se
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 database when that
> database need vac
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 s
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 a
> From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> As a superuser, DROP TABLE should work on the temporary schema of another
> session. Have you tried that to solve the situation?
Yes, we asked the customer to do that today. I think the customer will do in
the near future.
> > * In th
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 sessi
Hello,
I've found a problem that an orphaned temporary table could cause XID
wraparound. Our customer encountered this problem with PG 9.5.2, but I think
this will happen with the latest PG.
I'm willing to fix this, but I'd like to ask you what approach we should take.
PROBLEM
==
62 matches
Mail list logo
