pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed Multixact Freezing Docs
18+ messages / 5 participants
[nested] [flat]
Commits from this thread:
2025-07-01 e6115394d4 0ee7f51a8b c8b9f75111 f5551538bc d271b04dc6 a167e654d0 e35a0174a9 Document pg_get_multixact_members().
* Need Multixact Freezing Docs
@ 2014-04-15 21:25 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 18+ messages in thread
From: Josh Berkus @ 2014-04-15 21:25 UTC (permalink / raw)
To: pgsql-hackers
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-04-15 22:26 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 18+ messages in thread
From: Josh Berkus @ 2014-04-15 22:26 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-04-16 13:32 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 18+ messages in thread
From: Alvaro Herrera @ 2014-04-16 13:32 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-hackers
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-04-16 18:06 Josh Berkus <josh@agliodbs.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 2 replies; 18+ messages in thread
From: Josh Berkus @ 2014-04-16 18:06 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-04-16 20:30 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 18+ messages in thread
From: Alvaro Herrera @ 2014-04-16 20:30 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: 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.
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: http://www.postgresql.org/message-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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-04-16 20:39 Josh Berkus <josh@agliodbs.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 3 replies; 18+ messages in thread
From: Josh Berkus @ 2014-04-16 20:39 UTC (permalink / raw)
To: 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: http://www.postgresql.org/message-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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-04-16 20:45 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 18+ messages in thread
From: Alvaro Herrera @ 2014-04-16 20:45 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-hackers
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-04-16 21:32 Josh Berkus <josh@agliodbs.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 18+ messages in thread
From: Josh Berkus @ 2014-04-16 21:32 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-04-17 13:21 Robert Haas <robertmhaas@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 18+ messages in thread
From: Robert Haas @ 2014-04-17 13:21 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-hackers
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: http://www.postgresql.org/message-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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-08-28 16:09 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 18+ messages in thread
From: Alvaro Herrera @ 2014-08-28 16:09 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-08-28 16:32 Josh Berkus <josh@agliodbs.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 18+ messages in thread
From: Josh Berkus @ 2014-08-28 16:32 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-09-03 00:18 Bruce Momjian <bruce@momjian.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 18+ messages in thread
From: Bruce Momjian @ 2014-09-03 00:18 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-09-03 21:17 Robert Haas <robertmhaas@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 18+ messages in thread
From: Robert Haas @ 2014-09-03 21:17 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Josh Berkus <josh@agliodbs.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2014-09-05 23:39 Bruce Momjian <bruce@momjian.us>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Bruce Momjian @ 2014-09-05 23:39 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; 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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments:
mxid-age.diff (text/x-diff, 4.3K) download
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);
* Re: Need Multixact Freezing Docs
@ 2014-09-10 21:13 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 18+ messages in thread
From: Bruce Momjian @ 2014-09-10 21:13 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2015-06-14 14:50 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 18+ messages in thread
From: Alvaro Herrera @ 2015-06-14 14:50 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments:
0001-pg_get_multixact_range.patch (text/x-diff, 2.5K) download
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
* Re: Need Multixact Freezing Docs
@ 2015-06-16 00:26 Jim Nasby <Jim.Nasby@BlueTreble.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 18+ messages in thread
From: Jim Nasby @ 2015-06-16 00:26 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-hackers
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Need Multixact Freezing Docs
@ 2015-06-19 21:52 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 0 replies; 18+ messages in thread
From: Alvaro Herrera @ 2015-06-19 21:52 UTC (permalink / raw)
To: Jim Nasby <Jim.Nasby@BlueTreble.com>; +Cc: Josh Berkus <josh@agliodbs.com>; 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments:
pg_get_multixact_range-2.patch (text/x-diff, 5.7K) download
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);
end of thread, other threads:[~2015-06-19 21:52 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-15 21:25 Need Multixact Freezing Docs Josh Berkus <josh@agliodbs.com>
2014-04-15 22:26 ` Josh Berkus <josh@agliodbs.com>
2014-04-16 13:32 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-04-16 18:06 ` Josh Berkus <josh@agliodbs.com>
2014-04-16 20:30 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-04-16 20:39 ` Josh Berkus <josh@agliodbs.com>
2014-04-16 20:45 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-04-16 21:32 ` Josh Berkus <josh@agliodbs.com>
2014-04-17 13:21 ` Robert Haas <robertmhaas@gmail.com>
2014-08-28 16:09 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-08-28 16:32 ` Josh Berkus <josh@agliodbs.com>
2014-09-03 00:18 ` Bruce Momjian <bruce@momjian.us>
2014-09-03 21:17 ` Robert Haas <robertmhaas@gmail.com>
2014-09-05 23:39 ` Bruce Momjian <bruce@momjian.us>
2014-09-10 21:13 ` Bruce Momjian <bruce@momjian.us>
2015-06-14 14:50 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-06-16 00:26 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2015-06-19 21:52 ` Alvaro Herrera <alvherre@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox