Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kH8LL-0006S1-7x for pgsql-bugs@arkaria.postgresql.org; Sat, 12 Sep 2020 16:25:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kH8LJ-0004U9-UC for pgsql-bugs@arkaria.postgresql.org; Sat, 12 Sep 2020 16:25:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kH82j-0007Z9-8x for pgsql-bugs@lists.postgresql.org; Sat, 12 Sep 2020 16:06:25 +0000 Received: from mail-qk1-x742.google.com ([2607:f8b0:4864:20::742]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kH82g-0007KV-Tb for pgsql-bugs@lists.postgresql.org; Sat, 12 Sep 2020 16:06:24 +0000 Received: by mail-qk1-x742.google.com with SMTP id f142so12894656qke.13 for ; Sat, 12 Sep 2020 09:06:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=EdgJAZbaoexRBX3W0TAMQRxum2BLzz0n6XxiYaKbR78=; b=BE36GlOneF6E3j1rQgh0qsTju8dCeKNtTmO0p6aEa3/pAuZuscQiNUKtBHPIeFwx+e eotLf/xRldzBQhViXpKJjfKT8wZk9nJcm4zkSdv4pvC8X4zlTuIX9XOcd2VL0znn0SLY YaiwDbWDbr3FVdE9O7DQLibPlf1EKPZDn1v9vVrvoJUnQ6b//xesYnGlcpgxtp7Vl9Dw J7oc+UyZUajtmmvjx2VcKSZXck9V7JNcNHfc2y8bONKF9pcvXxyNzORTWfuAgbYpziwO H+EYe0Q1ea3EioKnrQgrm1uTD4JQVFtU+IEjAlW5Ky2fUdukFkW2ttNZYWZkmKd/Ti7G 7gPQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=EdgJAZbaoexRBX3W0TAMQRxum2BLzz0n6XxiYaKbR78=; b=qJH4oU2/Uc684Kbcq5Z/avsdy20nlPeYF7VHykUyMMTRlqb3ldt1m3NctOBXhvowOx bNoosIdm86pYmw+p65qCI+TtkvtZ2Bynr8JpTMbDntxwcw4T3JP7gecJFG4h2TOUpm/1 j0pFCNOBOVQE4tzhSf5L+07DD7EM+FbvAThUH0El233ChaZllo1MouyFqCDwjrrjI9k2 8xLp4+u7sMaPXz+ihnG22MYmwk9JXLQkoUJ+eg41WYrj4GsE5NbemUYWF3EQXdF6yFo4 8BtiYRDcX0gAApZOlTas2qnXfnGMB5b3uwZFT5RgvgyL/4XzgUqwLBexLh1QSuaa/1W3 GnaQ== X-Gm-Message-State: AOAM532Xj3Sr+kZX37bd6tlA8SnaVGAOCnR8NcEH1M7z6bWDaaVOGTWC cvqRHu0t2kJKl4pcjUQh1Es5QJm4tN8bXTi1lLk= X-Google-Smtp-Source: ABdhPJzPFzyNuzAbCUNxh08o9FmxMXrMl+6i1OkNLiOC9PhKqdELn+gAbcI0wEfe0U8WzDy6fUE+uPgnMNlBMDM25oY= X-Received: by 2002:a37:6248:: with SMTP id w69mr5949386qkb.448.1599926781617; Sat, 12 Sep 2020 09:06:21 -0700 (PDT) MIME-Version: 1.0 References: <16614-23e5c4c4567d1576@postgresql.org> <182231.1599749034@sss.pgh.pa.us> <20200911063036.GH2743@paquier.xyz> <371035.1599834081@sss.pgh.pa.us> <553829.1599925774@sss.pgh.pa.us> In-Reply-To: <553829.1599925774@sss.pgh.pa.us> From: Giorgio Saviane Date: Sat, 12 Sep 2020 18:06:10 +0200 Message-ID: Subject: Re: BUG #16614: Stale temporary objects makes vacuum ineffective when 1 million transactions remain To: Tom Lane Cc: Francisco Olarte , Michael Paquier , PostgreSQL mailing lists Content-Type: multipart/alternative; boundary="0000000000007c2cfd05af1ffbef" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000007c2cfd05af1ffbef Content-Type: text/plain; charset="UTF-8" Ok, let me add one more step in that sequence. Before shutting down Postgres and going down to single mode we stopped the application server and other services around it. I'm pretty sure that there was a reasonable window of time before we went to single mode. Moreover, if the database already reached the stop limit for preventing the wraparound, was it still possible for autovacuum to kick in in multiuser mode? Wasn't the graceful database shutdown enough to let Postgres drop the temporary tables? Kind regards Giorgio Saviane Il giorno sab 12 set 2020 alle ore 17:49 Tom Lane ha scritto: > Giorgio Saviane writes: > > Let me clarify. > > The sequence of events that led our databases to the wraparound issue is > > the following: > > > 1. The application creates temporary tables that should be by default > > cleaned at the end of the session. As a matter of fact the pooler > doesn't > > actually close the session, neither - apparently - issues a DISCARD > ALL, > > creating leaks among sessions. > > 2. Since sessions are kept alive with stale temporary tables the > > autovacuum process cannot do maintenance on transaction ids. > > 3. Since we have no evidence on what is going on, the database reaches > > the 1 million stop limit to avoid transaction wraparound. > > 4. The application gets then out of order. We stop the Postgres server > > gracefully and we enter single user mode to run the vacuum > > 5. Any attempt to vacuum the database ends up with the usual "WARNING: > > database "mydb" must be vacuumed within XXX transactions" message, > > revealing that the vacuum could not fix up the transaction ids. > > So, actually, that was the wrong recovery method. All you really > needed to do was terminate that long-running session(s) and wait for > a minute or two. There is code to delete old orphaned temp tables, > even as far back as 9.5. But *it's part of autovacuum*. Dropping > down to single-user mode prevents it from running. > > I thought briefly about whether VACUUM in single-user mode should > also run that code; but I'm not really convinced it'd be a good thing > to be auto-dropping tables in single-user mode. And anyway we shouldn't > be encouraging use of single-user mode, and it'd be much better to not > be in this situation to start with. > > What seems like the most helpful thing we could do is teach autovacuum to > issue log warnings when it sees old temp tables holding back datfrozenxid. > The trick here is to not spam the log too much. After a few moment's > thought, maybe warn if: > > * a table is temp but not orphaned, and > * its relfrozenxid or relminmxid is equal to datfrozenxid or datminmxid, > proving that some previous autovac run saw it as holding back those > horizons, and > * those xids are older than the relevant freeze limit. > > For extra credit maybe the message could mention the PID of the > session owning the table? > > regards, tom lane > --0000000000007c2cfd05af1ffbef Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Ok, let me add one more step in that sequence.
Before = shutting down Postgres and going down to single mode we stopped the applica= tion server and other services around it. I'm pretty sure that there wa= s a reasonable window of time before we went to single mode. Moreover, if t= he database already reached the stop limit for preventing the wraparound, w= as it still possible for autovacuum to kick in in multiuser mode?
Wasn't the graceful database shutdown enough to let Postgres drop the = temporary tables?

Kind regards

Giorgio Saviane

Il giorno sab 12 set 2020 alle ore 17:49 Tom Lan= e <tgl@sss.pgh.pa.us> ha scr= itto:
Giorgio Sa= viane <gsaviane@= gmail.com> writes:
> Let me clarify.
> The sequence of events that led our databases to the wraparound issue = is
> the following:

>=C2=A0 =C2=A0 1. The application creates temporary tables that should b= e by default
>=C2=A0 =C2=A0 cleaned at the end of the session. As a matter of fact th= e pooler doesn't
>=C2=A0 =C2=A0 actually close the session, neither - apparently - issues= a DISCARD ALL,
>=C2=A0 =C2=A0 creating leaks among sessions.
>=C2=A0 =C2=A0 2. Since sessions are kept alive with stale temporary tab= les the
>=C2=A0 =C2=A0 autovacuum process cannot do maintenance on transaction i= ds.
>=C2=A0 =C2=A0 3. Since we have no evidence on what is going on, the dat= abase reaches
>=C2=A0 =C2=A0 the 1 million stop limit to avoid transaction wraparound.=
>=C2=A0 =C2=A0 4. The application gets then out of order. We stop the Po= stgres server
>=C2=A0 =C2=A0 gracefully and we enter single user mode to run the vacuu= m
>=C2=A0 =C2=A0 5. Any attempt to vacuum the database ends up with the us= ual "WARNING:
>=C2=A0 =C2=A0 database "mydb" must be vacuumed within XXX tra= nsactions" message,
>=C2=A0 =C2=A0 revealing that the vacuum could not fix up the transactio= n ids.

So, actually, that was the wrong recovery method.=C2=A0 All you really
needed to do was terminate that long-running session(s) and wait for
a minute or two.=C2=A0 There is code to delete old orphaned temp tables, even as far back as 9.5.=C2=A0 But *it's part of autovacuum*.=C2=A0 Dro= pping
down to single-user mode prevents it from running.

I thought briefly about whether VACUUM in single-user mode should
also run that code; but I'm not really convinced it'd be a good thi= ng
to be auto-dropping tables in single-user mode.=C2=A0 And anyway we shouldn= 't
be encouraging use of single-user mode, and it'd be much better to not<= br> be in this situation to start with.

What seems like the most helpful thing we could do is teach autovacuum to issue log warnings when it sees old temp tables holding back datfrozenxid.<= br> The trick here is to not spam the log too much.=C2=A0 After a few moment= 9;s
thought, maybe warn if:

* a table is temp but not orphaned, and
* its relfrozenxid or relminmxid is equal to datfrozenxid or datminmxid, proving that some previous autovac run saw it as holding back those
horizons, and
* those xids are older than the relevant freeze limit.

For extra credit maybe the message could mention the PID of the
session owning the table?

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane
--0000000000007c2cfd05af1ffbef--