Need Multixact Freezing Docs
Hackers,
We need documentation on how users should intelligently set the
multixact freeze settings. I'm happy to write the actual text, but I
definitely don't have any idea how to set these myself. Under what
circumstances should they be different from freeze_max_age? How?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 04/15/2014 02:25 PM, Josh Berkus wrote:
Hackers,
We need documentation on how users should intelligently set the
multixact freeze settings. I'm happy to write the actual text, but I
definitely don't have any idea how to set these myself. Under what
circumstances should they be different from freeze_max_age? How?
Also: how do I check the multixact age of a table? There doesn't seem
to be any data for this ...
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMe71c36af513bace4ff046f7349231f370d2b74c4adee803b2eb3ed330aada82b5badd712b47f9556d3fa55d2ab73a3e0@asav-3.01.com
Josh Berkus wrote:
On 04/15/2014 02:25 PM, Josh Berkus wrote:
Hackers,
We need documentation on how users should intelligently set the
multixact freeze settings. I'm happy to write the actual text, but I
definitely don't have any idea how to set these myself. Under what
circumstances should they be different from freeze_max_age? How?
Measure consumption rate of multixacts, compare to consumption rate of
xids, and set the freeze ages so that they are reached more-or-less at
the same time, so that freezing for any of them would also freeze the
other one. You need to set both table_freeze_ages to values that would
be reached later than both min_freeze_ages would be reached, if you get
what I mean. The idea is that full scan of a table would fix both
things at once, saving a followup full scan shortly after the first one.
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.
Also: how do I check the multixact age of a table? There doesn't seem
to be any data for this ...
pg_class.relminmxid is the oldest multixact value that might be present
in a table.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
Also: how do I check the multixact age of a table? There doesn't seem
to be any data for this ...pg_class.relminmxid is the oldest multixact value that might be present
in a table.
On every database I've tested, age(relminmxid) returns int_max. So this
is apparently broken.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMa70b2681d2ac0ed55eb2d3eed44e6730e9c71b5fad8c5320503fde8b18a03fd98a6af6244821ca39d47c1d9e758c45ff@asav-2.01.com
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.
Yeah, good idea. Want to propose a patch?
Also: how do I check the multixact age of a table? There doesn't seem
to be any data for this ...pg_class.relminmxid is the oldest multixact value that might be present
in a table.On every database I've tested, age(relminmxid) returns int_max. So this
is apparently broken.
Hmm, are you sure it's INT_MAX and not 4244967297? Heikki reported
that: /messages/by-id/52401AEA.9000608@vmware.com
The absolute value is not important; I think that's mostly harmless. I
don't think applying age() to a multixact value is meaningful, though;
that's only good for Xids.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 04/16/2014 01:30 PM, Alvaro Herrera wrote:
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.Yeah, good idea. Want to propose a patch?
Yeah, lemme dig into this. I really think we need it for 9.4, feature
frozen or not.
Also: how do I check the multixact age of a table? There doesn't seem
to be any data for this ...pg_class.relminmxid is the oldest multixact value that might be present
in a table.On every database I've tested, age(relminmxid) returns int_max. So this
is apparently broken.Hmm, are you sure it's INT_MAX and not 4244967297? Heikki reported
that: /messages/by-id/52401AEA.9000608@vmware.com
The absolute value is not important; I think that's mostly harmless. I
don't think applying age() to a multixact value is meaningful, though;
that's only good for Xids.
Yeah, I'm sure:
josh=# select relname, age(relminmxid) from pg_class;
relname | age
-----------------------------------------+------------
pg_statistic | 2147483647
pg_type | 2147483647
random | 2147483647
dblink_pkey_results | 2147483647
pg_toast_17395 | 2147483647
...
So if age() doesn't mean anything, then how are users to know when the
need to freeze?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMfa9379a39fd015511ce3872138589081b6e688c26b67a6801364b609b26642096bfd329ce2cb7eb090da00879e59e525@asav-3.01.com
Josh Berkus wrote:
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.Yeah, good idea. Want to propose a patch?
Yeah, lemme dig into this. I really think we need it for 9.4, feature
frozen or not.
Great, thanks.
josh=# select relname, age(relminmxid) from pg_class;
relname | age
-----------------------------------------+------------
pg_statistic | 2147483647
pg_type | 2147483647
random | 2147483647
dblink_pkey_results | 2147483647
pg_toast_17395 | 2147483647...
So if age() doesn't mean anything, then how are users to know when the
need to freeze?
I don't understand. Autovacuum will freeze this automatically when the
threshold is reached. Users don't need to do anything.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
So if age() doesn't mean anything, then how are users to know when the
need to freeze?I don't understand. Autovacuum will freeze this automatically when the
threshold is reached. Users don't need to do anything.
What I'm asking is:
- how do users know if Autovacuum is keeping up with multixact feezing?
- how do users get data on multixact usage so that they can tune the
parameters?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM1f13fbd2f441a36e28cf57b12413993c9d5d0160fe7b5fb1dc8ce992c49999a1e658bca73a3d92963640c8cf9f1f69b0@asav-3.01.com
On Wed, Apr 16, 2014 at 4:39 PM, Josh Berkus <josh@agliodbs.com> wrote:
Hmm, are you sure it's INT_MAX and not 4244967297? Heikki reported
that: /messages/by-id/52401AEA.9000608@vmware.com
The absolute value is not important; I think that's mostly harmless. I
don't think applying age() to a multixact value is meaningful, though;
that's only good for Xids.Yeah, I'm sure:
josh=# select relname, age(relminmxid) from pg_class;
relname | age
-----------------------------------------+------------
pg_statistic | 2147483647
pg_type | 2147483647
random | 2147483647
dblink_pkey_results | 2147483647
pg_toast_17395 | 2147483647...
So if age() doesn't mean anything, then how are users to know when the
need to freeze?
Or, in other words, this is another example of xid-freezing
infrastructure that needed to be copied for mxid-freezing and wasn't.
We need an analogue of age() for mxids. Perhaps just mxid_age()?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Josh Berkus wrote:
On 04/16/2014 01:30 PM, Alvaro Herrera wrote:
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.Yeah, good idea. Want to propose a patch?
Yeah, lemme dig into this. I really think we need it for 9.4, feature
frozen or not.
Ping?
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/28/2014 09:09 AM, Alvaro Herrera wrote:
Josh Berkus wrote:
On 04/16/2014 01:30 PM, Alvaro Herrera wrote:
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.Yeah, good idea. Want to propose a patch?
Yeah, lemme dig into this. I really think we need it for 9.4, feature
frozen or not.
Got sidetracked by JSONB.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMab5645a7cd6e67e13c84377c9d568b52e17cdb08a0e5ba356fd95d14e5fcf75f114940bb9868c8c1e23e1d58680d5795@asav-3.01.com
On Thu, Aug 28, 2014 at 09:32:17AM -0700, Josh Berkus wrote:
On 08/28/2014 09:09 AM, Alvaro Herrera wrote:
Josh Berkus wrote:
On 04/16/2014 01:30 PM, Alvaro Herrera wrote:
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.Yeah, good idea. Want to propose a patch?
Yeah, lemme dig into this. I really think we need it for 9.4, feature
frozen or not.Got sidetracked by JSONB.
I had a look at this and came upon a problem --- there is no multi-xid
SQL data type, and in fact the system catalogs that store mxid values
use xid, e.g.:
relminmxid | xid | not null
With no mxid data type, there is no way to do function overloading to
cause age to call the mxid variant.
Should we use an explicit mxid_age() function name? Add an mxid data
type?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Sep 2, 2014 at 8:18 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Thu, Aug 28, 2014 at 09:32:17AM -0700, Josh Berkus wrote:
On 08/28/2014 09:09 AM, Alvaro Herrera wrote:
Josh Berkus wrote:
On 04/16/2014 01:30 PM, Alvaro Herrera wrote:
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.Yeah, good idea. Want to propose a patch?
Yeah, lemme dig into this. I really think we need it for 9.4, feature
frozen or not.Got sidetracked by JSONB.
I had a look at this and came upon a problem --- there is no multi-xid
SQL data type, and in fact the system catalogs that store mxid values
use xid, e.g.:relminmxid | xid | not null
With no mxid data type, there is no way to do function overloading to
cause age to call the mxid variant.Should we use an explicit mxid_age() function name? Add an mxid data
type?
Maybe both. But mxid_age() is probably the simpler way forward just to start.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Sep 3, 2014 at 05:17:17PM -0400, Robert Haas wrote:
I had a look at this and came upon a problem --- there is no multi-xid
SQL data type, and in fact the system catalogs that store mxid values
use xid, e.g.:relminmxid | xid | not null
With no mxid data type, there is no way to do function overloading to
cause age to call the mxid variant.Should we use an explicit mxid_age() function name? Add an mxid data
type?Maybe both. But mxid_age() is probably the simpler way forward just to start.
OK, patch applied using mxid_age() and no new data type.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
Attachments:
mxid-age.difftext/x-diff; charset=us-asciiDownload
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
new file mode 100644
index cf174f0..d692308
*** a/doc/src/sgml/maintenance.sgml
--- b/doc/src/sgml/maintenance.sgml
*************** HINT: Stop the postmaster and vacuum th
*** 640,646 ****
possible multixact ID still appearing in any tuple of that table.
If this value is older than
<xref linkend="guc-vacuum-multixact-freeze-table-age">, a whole-table
! scan is forced. Whole-table <command>VACUUM</> scans, regardless of
what causes them, enable advancing the value for that table.
Eventually, as all tables in all databases are scanned and their
oldest multixact values are advanced, on-disk storage for older
--- 640,651 ----
possible multixact ID still appearing in any tuple of that table.
If this value is older than
<xref linkend="guc-vacuum-multixact-freeze-table-age">, a whole-table
! scan is forced. <function>mxid_age()</> can be used on
! <structname>pg_class</>.<structfield>relminmxid</> to find its age.
! </para>
!
! <para>
! Whole-table <command>VACUUM</> scans, regardless of
what causes them, enable advancing the value for that table.
Eventually, as all tables in all databases are scanned and their
oldest multixact values are advanced, on-disk storage for older
diff --git a/src/backend/utils/adt/xid.c b/src/backend/utils/adt/xid.c
new file mode 100644
index 602a9e5..ecb3cf5
*** a/src/backend/utils/adt/xid.c
--- b/src/backend/utils/adt/xid.c
***************
*** 16,21 ****
--- 16,22 ----
#include <limits.h>
+ #include "access/multixact.h"
#include "access/transam.h"
#include "access/xact.h"
#include "libpq/pqformat.h"
*************** xid_age(PG_FUNCTION_ARGS)
*** 100,105 ****
--- 101,121 ----
PG_RETURN_INT32(INT_MAX);
PG_RETURN_INT32((int32) (now - xid));
+ }
+
+ /*
+ * mxid_age - compute age of a multi XID (relative to latest stable mxid)
+ */
+ Datum
+ mxid_age(PG_FUNCTION_ARGS)
+ {
+ TransactionId xid = PG_GETARG_TRANSACTIONID(0);
+ MultiXactId now = ReadNextMultiXactId();
+
+ if (!MultiXactIdIsValid(xid))
+ PG_RETURN_INT32(INT_MAX);
+
+ PG_RETURN_INT32((int32) (now - xid));
}
/*
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
new file mode 100644
index e1b62a5..e0875b7
*** a/src/include/catalog/catversion.h
--- b/src/include/catalog/catversion.h
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 201408281
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 201409021
#endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index 5176ed0..09e138b
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 1180 ( abstime PG
*** 1277,1282 ****
--- 1277,1284 ----
DESCR("convert timestamp with time zone to abstime");
DATA(insert OID = 1181 ( age PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 23 "28" _null_ _null_ _null_ _null_ xid_age _null_ _null_ _null_ ));
DESCR("age of a transaction ID, in transactions before current transaction");
+ DATA(insert OID = 3218 ( mxid_age PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 23 "28" _null_ _null_ _null_ _null_ mxid_age _null_ _null_ _null_ ));
+ DESCR("age of a multi-transaction ID, in multi-transactions before current multi-transaction");
DATA(insert OID = 1188 ( timestamptz_mi PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1186 "1184 1184" _null_ _null_ _null_ _null_ timestamp_mi _null_ _null_ _null_ ));
DATA(insert OID = 1189 ( timestamptz_pl_interval PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 1184 "1184 1186" _null_ _null_ _null_ _null_ timestamptz_pl_interval _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
new file mode 100644
index 78cc0a0..d88e7a3
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum xidrecv(PG_FUNCTION_ARGS);
*** 845,850 ****
--- 845,851 ----
extern Datum xidsend(PG_FUNCTION_ARGS);
extern Datum xideq(PG_FUNCTION_ARGS);
extern Datum xid_age(PG_FUNCTION_ARGS);
+ extern Datum mxid_age(PG_FUNCTION_ARGS);
extern int xidComparator(const void *arg1, const void *arg2);
extern Datum cidin(PG_FUNCTION_ARGS);
extern Datum cidout(PG_FUNCTION_ARGS);
On Fri, Sep 5, 2014 at 07:39:36PM -0400, Bruce Momjian wrote:
On Wed, Sep 3, 2014 at 05:17:17PM -0400, Robert Haas wrote:
I had a look at this and came upon a problem --- there is no multi-xid
SQL data type, and in fact the system catalogs that store mxid values
use xid, e.g.:relminmxid | xid | not null
With no mxid data type, there is no way to do function overloading to
cause age to call the mxid variant.Should we use an explicit mxid_age() function name? Add an mxid data
type?Maybe both. But mxid_age() is probably the simpler way forward just to start.
OK, patch applied using mxid_age() and no new data type.
Applied.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Attachments:
0001-pg_get_multixact_range.patchtext/x-diff; charset=us-asciiDownload
>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
On 6/14/15 9:50 AM, Alvaro Herrera wrote:
+ values[0] = MultiXactState->oldestMultiXactId;
What about oldestOffset and offsetStopLimit? Seems those would be useful
too. Looks good other than that.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby wrote:
On 6/14/15 9:50 AM, Alvaro Herrera wrote:
+ values[0] = MultiXactState->oldestMultiXactId;
What about oldestOffset and offsetStopLimit? Seems those would be useful
too. Looks good other than that.
Yeah, that's what I was trying to say. How about this?
I realized that pg_get_multixact_members() was not documented, so I
added a blurb about it too. I guess I could backpatch that part to 9.3
because it's been present all along.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
pg_get_multixact_range-2.patchtext/x-diff; charset=us-asciiDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9a2a1f6..1925c6c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14874,6 +14874,57 @@ SELECT collation for ('foo' COLLATE "de_DE");
For example <literal>10:20:10,14,15</literal> means
<literal>xmin=10, xmax=20, xip_list=10, 14, 15</literal>.
</para>
+
+ <indexterm>
+ <primary>pg_get_multixact_members</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>pg_get_multixact_range</primary>
+ </indexterm>
+
+ <para>
+ The functions shown in <xref linkend="functions-info-multixact-table">
+ obtain information relative to multixacts in the system.
+ </para>
+
+ <table id="functions-info-multixact-table">
+ <title>Multixact Functions</title>
+ <tgroup cols="3">
+ <thead>
+ <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal><function>pg_get_multixact_range()</function></literal></entry>
+ <entry>
+ <parameter>oldestmulti</parameter><type>xid</type>,
+ <parameter>nextmulti</parameter><type>xid</type>,
+ <parameter>stopmulti</parameter><type>xid</type>,
+ <parameter>oldestmember</parameter><type>xid</type>,
+ <parameter>nextmember</parameter><type>xid</type>,
+ <parameter>stopmember</parameter><type>xid</type>
+ </entry>
+ <entry>
+ get current oldest, next and stop limits for multixact IDs,
+ and oldest, next and stop limit for multixact members
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal><function>pg_get_multixact_members(<type>xid</type>)</function></literal></entry>
+ <entry>
+ <parameter>xid</parameter><type>xid</type>,
+ <parameter>mode</parameter><type>text</type>
+ </entry>
+ <entry>
+ get Xid and mode of the members of the given multixact
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
</sect1>
<sect1 id="functions-admin">
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 4383862..db4d457 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -662,6 +662,14 @@ HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".
Both of these kinds of whole-table scans will occur even if autovacuum is
nominally disabled.
</para>
+
+ <para>
+ To know the limits of the live range of both the multixact ID counter and
+ of the members storage area, and the limits which would cause further
+ multixact creation to be rejected, see
+ <xref linkend="functions-info-multixact-table">.
+ </para>
+
</sect3>
</sect2>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 5c25c2f..296c85b 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -66,6 +66,7 @@
*/
#include "postgres.h"
+#include "access/htup_details.h"
#include "access/multixact.h"
#include "access/slru.h"
#include "access/transam.h"
@@ -3308,3 +3309,38 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funccxt);
}
+
+Datum
+pg_get_multixact_range(PG_FUNCTION_ARGS)
+{
+ TupleDesc tupdesc;
+ HeapTuple htup;
+ Datum values[6];
+ bool nulls[6];
+
+ /* 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);
+
+ MemSet(nulls, 0, sizeof(nulls));
+
+ LWLockAcquire(MultiXactGenLock, LW_SHARED);
+ values[0] = MultiXactState->oldestMultiXactId;
+ values[1] = MultiXactState->nextMXact;
+ values[2] = MultiXactState->multiStopLimit;
+ if (MultiXactState->oldestOffsetKnown)
+ values[3] = MultiXactState->oldestOffset;
+ else
+ nulls[3] = true;
+ values[4] = MultiXactState->nextOffset;
+ if (MultiXactState->offsetStopLimitKnown)
+ values[5] = MultiXactState->offsetStopLimit;
+ else
+ nulls[5] = true;
+ LWLockRelease(MultiXactGenLock);
+
+ 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 92af36d..8241732 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2911,6 +2911,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_ pg_get_multixact_members _null_ _null_ _null_ ));
DESCR("view members of a multixactid");
+DATA(insert OID = 3846 ( pg_get_multixact_range PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 2249 "" "{28,28,28,28,28,28}" "{o,o,o,o,o,o}" "{oldestmulti,nextmulti,stopmulti,oldestmember,nextmember,stopmember}" _null_ pg_get_multixact_range _null_ _null_ _null_ ));
+DESCR("get range and limits of multixacts");
DATA(insert OID = 3537 ( pg_describe_object PGNSP PGUID 12 1 0 0 0 f f f f t f s 3 0 25 "26 26 23" _null_ _null_ _null_ _null_ pg_describe_object _null_ _null_ _null_ ));
DESCR("get identification of SQL object");
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 929a728..2fd612c 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -1143,6 +1143,7 @@ extern Datum pg_prepared_xact(PG_FUNCTION_ARGS);
/* access/transam/multixact.c */
extern Datum pg_get_multixact_members(PG_FUNCTION_ARGS);
+extern Datum pg_get_multixact_range(PG_FUNCTION_ARGS);
/* catalogs/dependency.c */
extern Datum pg_describe_object(PG_FUNCTION_ARGS);