Date: Sun, 14 Jun 2015 11:50:18 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Need Multixact Freezing Docs
Message-ID: <20150614145018.GE133018@postgresql.org>
References: <534DA3D1.2010000@agliodbs.com>
 <WM!e71c36af513bace4ff046f7349231f370d2b74c4adee803b2eb3ed330aada82b5badd712b47f9556d3fa55d2ab73a3e0!@asav-3.01.com>
 <534DB22A.2040909@agliodbs.com>
 <20140416133233.GL5822@eldon.alvh.no-ip.org>
 <WM!a70b2681d2ac0ed55eb2d3eed44e6730e9c71b5fad8c5320503fde8b18a03fd98a6af6244821ca39d47c1d9e758c45ff!@asav-2.01.com>
 <534EC69B.9010107@agliodbs.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="/e2eDi0V/xtL+Mc8"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <534EC69B.9010107@agliodbs.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--/e2eDi0V/xtL+Mc8
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Josh Berkus wrote:
> 
> > You can see the current multixact value in pg_controldata output.  Keep
> > timestamped values of that somewhere (a table?) so that you can measure
> > consumption rate.  I don't think we provide SQL-level access to those
> > values.
> 
> Bleh.  Do we provide SQL-level access in 9.4?  If not, I think that's a
> requirement before release.  Telling users to monitor a setting using a
> restricted-permission command-line utility which produces a
> version-specific text file they have to parse is not going to win us a
> lot of fans.

I found that I had written a very quick accessor function to multixact
shared state data awhile ago.  This might be useful for monitoring
purposes.  What do people think of including this for 9.5?  It needs a
small change to add the newly added oldestOffset (plus a little cleanup
and docs).

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

--/e2eDi0V/xtL+Mc8
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0001-pg_get_multixact_range.patch"

From 2134f7928cad8c8d1a1e2d752c9443fb44a92a7e Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Sun, 14 Jun 2015 11:48:58 -0300
Subject: [PATCH] pg_get_multixact_range

---
 src/backend/access/transam/multixact.c | 31 +++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.h          |  2 ++
 2 files changed, 33 insertions(+)

diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 516a89f..dedded3 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -3274,3 +3274,34 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+#include "access/htup_details.h"
+
+Datum
+pg_get_multixact_range(PG_FUNCTION_ARGS);
+
+Datum
+pg_get_multixact_range(PG_FUNCTION_ARGS)
+{
+	TupleDesc		tupdesc;
+	HeapTuple		htup;
+	Datum			values[3];
+	bool			nulls[3];
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+	tupdesc = BlessTupleDesc(tupdesc);
+
+	LWLockAcquire(MultiXactGenLock, LW_SHARED);
+	values[0] = MultiXactState->oldestMultiXactId;
+	values[1] = MultiXactState->nextMXact;
+	values[2] = MultiXactState->nextOffset;
+	LWLockRelease(MultiXactGenLock);
+
+	nulls[0] = nulls[1] = nulls[2] = false;
+
+	htup = heap_form_tuple(tupdesc, values, nulls);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6b3d194..f87d3d0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3079,6 +3079,8 @@ DATA(insert OID = 1065 (  pg_prepared_xact PGNSP PGUID 12 1 1000 0 0 f f f f t t
 DESCR("view two-phase transactions");
 DATA(insert OID = 3819 (  pg_get_multixact_members PGNSP PGUID 12 1 1000 0 0 f f f f t t v 1 0 2249 "28" "{28,28,25}" "{i,o,o}" "{multixid,xid,mode}" _null_ _null_ pg_get_multixact_members _null_ _null_ _null_ ));
 DESCR("view members of a multixactid");
+DATA(insert OID = 3401 (  pg_get_multixact_range   PGNSP PGUID 12 1    0 0 0 f f f f t f s 0 0 2249 "" "{28,28,28}" "{o,o,o}" "{oldestmulti,nextmulti,nextoffset}" _null_ _null_ pg_get_multixact_range _null_ _null_ _null_ ));
+DESCR("get range of live multixacts");
 
 DATA(insert OID = 3581 ( pg_xact_commit_timestamp PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1184 "28" _null_ _null_ _null_ _null_ _null_ pg_xact_commit_timestamp _null_ _null_ _null_ ));
 DESCR("get commit timestamp of a transaction");
-- 
2.1.4


--/e2eDi0V/xtL+Mc8
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


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

--/e2eDi0V/xtL+Mc8--