Date: Fri, 6 May 2016 13:05:47 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: =?utf-8?B?T25kxZllaiBTdsSbdGzDrWs=?= <osvetlik@flexibee.eu>,
	pgsql-admin@postgresql.org
Subject: Re: Autovacuum of pg_database
Message-ID: <20160506160547.GA212152@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="7JfCtLOvnd9MIVvH"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <13485.1462544224@sss.pgh.pa.us>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--7JfCtLOvnd9MIVvH
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > These are all shared catalogs.  There are others, so you may still see
> > more.  We got another report for pg_database
> > https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com
> > so I suppose there really is a bug.  I don't know what's going on there.
> 
> I think it's pretty obvious: autovacuum.c's rule for detecting whether
> some other worker is already processing table X is wrong when X is a
> shared table.  I propose the attached patch.

Hmm, I have pretty much the same patch, except I added the flag to
struct autovac_table and have it populated by table_recheck_autovac.
Haven't tested this yet, which is why I hadn't posted it.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--7JfCtLOvnd9MIVvH
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="autovac-shared.patch"

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index e2859df..6bdaac5 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -190,6 +190,7 @@ typedef struct autovac_table
 	int			at_vacuum_cost_delay;
 	int			at_vacuum_cost_limit;
 	bool		at_dobalance;
+	bool		at_sharedrel;
 	char	   *at_relname;
 	char	   *at_nspname;
 	char	   *at_datname;
@@ -203,6 +204,7 @@ typedef struct autovac_table
  * wi_links		entry into free list or running list
  * wi_dboid		OID of the database this worker is supposed to work on
  * wi_tableoid	OID of the table currently being vacuumed, if any
+ * wi_sharedrel	flag indicating whether table is marked relisshared
  * wi_proc		pointer to PGPROC of the running worker, NULL if not started
  * wi_launchtime Time at which this worker was launched
  * wi_cost_*	Vacuum cost-based delay parameters current in this worker
@@ -220,6 +222,7 @@ typedef struct WorkerInfoData
 	PGPROC	   *wi_proc;
 	TimestampTz wi_launchtime;
 	bool		wi_dobalance;
+	bool		wi_sharedrel;
 	int			wi_cost_delay;
 	int			wi_cost_limit;
 	int			wi_cost_limit_base;
@@ -717,6 +720,7 @@ AutoVacLauncherMain(int argc, char *argv[])
 					worker = AutoVacuumShmem->av_startingWorker;
 					worker->wi_dboid = InvalidOid;
 					worker->wi_tableoid = InvalidOid;
+					worker->wi_sharedrel = false;
 					worker->wi_proc = NULL;
 					worker->wi_launchtime = 0;
 					dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
@@ -1683,6 +1687,7 @@ FreeWorkerInfo(int code, Datum arg)
 		dlist_delete(&MyWorkerInfo->wi_links);
 		MyWorkerInfo->wi_dboid = InvalidOid;
 		MyWorkerInfo->wi_tableoid = InvalidOid;
+		MyWorkerInfo->wi_sharedrel = false;
 		MyWorkerInfo->wi_proc = NULL;
 		MyWorkerInfo->wi_launchtime = 0;
 		MyWorkerInfo->wi_dobalance = false;
@@ -2229,8 +2234,8 @@ do_autovacuum(void)
 			if (worker == MyWorkerInfo)
 				continue;
 
-			/* ignore workers in other databases */
-			if (worker->wi_dboid != MyDatabaseId)
+			/* ignore workers in other databases (unless table is shared) */
+			if (!worker->wi_sharedrel && worker->wi_dboid != MyDatabaseId)
 				continue;
 
 			if (worker->wi_tableoid == relid)
@@ -2271,6 +2276,7 @@ do_autovacuum(void)
 		 * the lock so that other workers don't vacuum it concurrently.
 		 */
 		MyWorkerInfo->wi_tableoid = relid;
+		MyWorkerInfo->wi_sharedrel = tab->at_sharedrel;
 		LWLockRelease(AutovacuumScheduleLock);
 
 		/*
@@ -2382,6 +2388,7 @@ deleted:
 		 */
 		LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE);
 		MyWorkerInfo->wi_tableoid = InvalidOid;
+		MyWorkerInfo->wi_sharedrel = false;
 		LWLockRelease(AutovacuumLock);
 
 		/* restore vacuum cost GUCs for the next iteration */
@@ -2577,6 +2584,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
+		tab->at_sharedrel = classForm->relisshared;
 		tab->at_vacoptions = VACOPT_SKIPTOAST |
 			(dovacuum ? VACOPT_VACUUM : 0) |
 			(doanalyze ? VACOPT_ANALYZE : 0) |

--7JfCtLOvnd9MIVvH
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--7JfCtLOvnd9MIVvH--