public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v2 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED
7+ messages / 4 participants
[nested] [flat]
* [PATCH v2 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED
@ 2020-11-11 14:21 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Kyotaro Horiguchi @ 2020-11-11 14:21 UTC (permalink / raw)
To ease invoking ALTER TABLE SET LOGGED/UNLOGGED, this command changes
relation persistence of all tables in the specified tablespace.
---
src/backend/commands/tablecmds.c | 140 +++++++++++++++++++++++++++++++
src/backend/nodes/copyfuncs.c | 16 ++++
src/backend/nodes/equalfuncs.c | 15 ++++
src/backend/parser/gram.y | 20 +++++
src/backend/tcop/utility.c | 11 +++
src/include/commands/tablecmds.h | 2 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 9 ++
8 files changed, 214 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 45be633d9f..002749094b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13663,6 +13663,146 @@ AlterTableMoveAll(AlterTableMoveAllStmt *stmt)
return new_tablespaceoid;
}
+/*
+ * Alter Table ALL ... SET LOGGED/UNLOGGED
+ *
+ * Allows a user to change persistence of all objects in a given tablespace in
+ * the current database. Objects can be chosen based on the owner of the
+ * object also, to allow users to change persistene only their objects. The
+ * main permissions handling is done by the lower-level change persistence
+ * function.
+ *
+ * All to-be-modified objects are locked first. If NOWAIT is specified and the
+ * lock can't be acquired then we ereport(ERROR).
+ */
+void
+AlterTableSetLoggedAll(AlterTableSetLoggedAllStmt *stmt)
+{
+ List *relations = NIL;
+ ListCell *l;
+ ScanKeyData key[1];
+ Relation rel;
+ TableScanDesc scan;
+ HeapTuple tuple;
+ Oid tablespaceoid;
+ List *role_oids = roleSpecsToIds(NIL);
+
+ /* Ensure we were not asked to change something we can't */
+ if (stmt->objtype != OBJECT_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("only tables can be specified")));
+
+ /* Get the tablespace OID */
+ tablespaceoid = get_tablespace_oid(stmt->tablespacename, false);
+
+ /*
+ * Now that the checks are done, check if we should set either to
+ * InvalidOid because it is our database's default tablespace.
+ */
+ if (tablespaceoid == MyDatabaseTableSpace)
+ tablespaceoid = InvalidOid;
+
+ /*
+ * Walk the list of objects in the tablespace to pick up them. This will
+ * only find objects in our database, of course.
+ */
+ ScanKeyInit(&key[0],
+ Anum_pg_class_reltablespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(tablespaceoid));
+
+ rel = table_open(RelationRelationId, AccessShareLock);
+ scan = table_beginscan_catalog(rel, 1, key);
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class relForm = (Form_pg_class) GETSTRUCT(tuple);
+ Oid relOid = relForm->oid;
+
+ /*
+ * Do not pick-up objects in pg_catalog as part of this, if an admin
+ * really wishes to do so, they can issue the individual ALTER
+ * commands directly.
+ *
+ * Also, explicitly avoid any shared tables, temp tables, or TOAST
+ * (TOAST will be changed with the main table).
+ */
+ if (IsCatalogNamespace(relForm->relnamespace) ||
+ relForm->relisshared ||
+ isAnyTempNamespace(relForm->relnamespace) ||
+ IsToastNamespace(relForm->relnamespace))
+ continue;
+
+ /* Only pick up the object type requested */
+ if (relForm->relkind != RELKIND_RELATION)
+ continue;
+
+ /* Check if we are only picking-up objects owned by certain roles */
+ if (role_oids != NIL && !list_member_oid(role_oids, relForm->relowner))
+ continue;
+
+ /*
+ * Handle permissions-checking here since we are locking the tables
+ * and also to avoid doing a bunch of work only to fail part-way. Note
+ * that permissions will also be checked by AlterTableInternal().
+ *
+ * Caller must be considered an owner on the table of which we're going
+ * to change persistence.
+ */
+ if (!pg_class_ownercheck(relOid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(get_rel_relkind(relOid)),
+ NameStr(relForm->relname));
+
+ if (stmt->nowait &&
+ !ConditionalLockRelationOid(relOid, AccessExclusiveLock))
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg("aborting because lock on relation \"%s.%s\" is not available",
+ get_namespace_name(relForm->relnamespace),
+ NameStr(relForm->relname))));
+ else
+ LockRelationOid(relOid, AccessExclusiveLock);
+
+ /*
+ * Add to our list of objects of which we're going to change
+ * persistence.
+ */
+ relations = lappend_oid(relations, relOid);
+ }
+
+ table_endscan(scan);
+ table_close(rel, AccessShareLock);
+
+ if (relations == NIL)
+ ereport(NOTICE,
+ (errcode(ERRCODE_NO_DATA_FOUND),
+ errmsg("no matching relations in tablespace \"%s\" found",
+ tablespaceoid == InvalidOid ? "(database default)" :
+ get_tablespace_name(tablespaceoid))));
+
+ /*
+ * Everything is locked, loop through and change persistence of all of the
+ * relations.
+ */
+ foreach(l, relations)
+ {
+ List *cmds = NIL;
+ AlterTableCmd *cmd = makeNode(AlterTableCmd);
+
+ if (stmt->logged)
+ cmd->subtype = AT_SetLogged;
+ else
+ cmd->subtype = AT_SetUnLogged;
+
+ cmds = lappend(cmds, cmd);
+
+ EventTriggerAlterTableStart((Node *) stmt);
+ /* OID is set by AlterTableInternal */
+ AlterTableInternal(lfirst_oid(l), cmds, false);
+ EventTriggerAlterTableEnd();
+ }
+}
+
static void
index_copy_data(Relation rel, RelFileNode newrnode)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 70f8b718e0..222b81724a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4124,6 +4124,19 @@ _copyAlterTableMoveAllStmt(const AlterTableMoveAllStmt *from)
return newnode;
}
+static AlterTableSetLoggedAllStmt *
+_copyAlterTableSetLoggedAllStmt(const AlterTableSetLoggedAllStmt *from)
+{
+ AlterTableSetLoggedAllStmt *newnode = makeNode(AlterTableSetLoggedAllStmt);
+
+ COPY_STRING_FIELD(tablespacename);
+ COPY_SCALAR_FIELD(objtype);
+ COPY_SCALAR_FIELD(logged);
+ COPY_SCALAR_FIELD(nowait);
+
+ return newnode;
+}
+
static CreateExtensionStmt *
_copyCreateExtensionStmt(const CreateExtensionStmt *from)
{
@@ -5424,6 +5437,9 @@ copyObjectImpl(const void *from)
case T_AlterTableMoveAllStmt:
retval = _copyAlterTableMoveAllStmt(from);
break;
+ case T_AlterTableSetLoggedAllStmt:
+ retval = _copyAlterTableSetLoggedAllStmt(from);
+ break;
case T_CreateExtensionStmt:
retval = _copyCreateExtensionStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 541e0e6b48..898f78d899 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1860,6 +1860,18 @@ _equalAlterTableMoveAllStmt(const AlterTableMoveAllStmt *a,
return true;
}
+static bool
+_equalAlterTableSetLoggedAllStmt(const AlterTableSetLoggedAllStmt *a,
+ const AlterTableSetLoggedAllStmt *b)
+{
+ COMPARE_STRING_FIELD(tablespacename);
+ COMPARE_SCALAR_FIELD(objtype);
+ COMPARE_SCALAR_FIELD(logged);
+ COMPARE_SCALAR_FIELD(nowait);
+
+ return true;
+}
+
static bool
_equalCreateExtensionStmt(const CreateExtensionStmt *a, const CreateExtensionStmt *b)
{
@@ -3479,6 +3491,9 @@ equal(const void *a, const void *b)
case T_AlterTableMoveAllStmt:
retval = _equalAlterTableMoveAllStmt(a, b);
break;
+ case T_AlterTableSetLoggedAllStmt:
+ retval = _equalAlterTableSetLoggedAllStmt(a, b);
+ break;
case T_CreateExtensionStmt:
retval = _equalCreateExtensionStmt(a, b);
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8f341ac006..afc4ff0447 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1885,6 +1885,26 @@ AlterTableStmt:
n->nowait = $13;
$$ = (Node *)n;
}
+ | ALTER TABLE ALL IN_P TABLESPACE name SET LOGGED opt_nowait
+ {
+ AlterTableSetLoggedAllStmt *n =
+ makeNode(AlterTableSetLoggedAllStmt);
+ n->tablespacename = $6;
+ n->objtype = OBJECT_TABLE;
+ n->logged = true;
+ n->nowait = $9;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE ALL IN_P TABLESPACE name SET UNLOGGED opt_nowait
+ {
+ AlterTableSetLoggedAllStmt *n =
+ makeNode(AlterTableSetLoggedAllStmt);
+ n->tablespacename = $6;
+ n->objtype = OBJECT_TABLE;
+ n->logged = false;
+ n->nowait = $9;
+ $$ = (Node *)n;
+ }
| ALTER INDEX qualified_name alter_table_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index a42ead7d69..f866b8cab2 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -161,6 +161,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_AlterTSConfigurationStmt:
case T_AlterTSDictionaryStmt:
case T_AlterTableMoveAllStmt:
+ case T_AlterTableSetLoggedAllStmt:
case T_AlterTableSpaceOptionsStmt:
case T_AlterTableStmt:
case T_AlterTypeStmt:
@@ -1732,6 +1733,12 @@ ProcessUtilitySlow(ParseState *pstate,
commandCollected = true;
break;
+ case T_AlterTableSetLoggedAllStmt:
+ AlterTableSetLoggedAll((AlterTableSetLoggedAllStmt *) parsetree);
+ /* commands are stashed in AlterTableSetLoggedAll */
+ commandCollected = true;
+ break;
+
case T_DropStmt:
ExecDropStmt((DropStmt *) parsetree, isTopLevel);
/* no commands stashed for DROP */
@@ -2615,6 +2622,10 @@ CreateCommandTag(Node *parsetree)
tag = AlterObjectTypeCommandTag(((AlterTableMoveAllStmt *) parsetree)->objtype);
break;
+ case T_AlterTableSetLoggedAllStmt:
+ tag = AlterObjectTypeCommandTag(((AlterTableSetLoggedAllStmt *) parsetree)->objtype);
+ break;
+
case T_AlterTableStmt:
tag = AlterObjectTypeCommandTag(((AlterTableStmt *) parsetree)->objtype);
break;
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index c1581ad178..206de61154 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -42,6 +42,8 @@ extern void AlterTableInternal(Oid relid, List *cmds, bool recurse);
extern Oid AlterTableMoveAll(AlterTableMoveAllStmt *stmt);
+extern void AlterTableSetLoggedAll(AlterTableSetLoggedAllStmt *stmt);
+
extern ObjectAddress AlterTableNamespace(AlterObjectSchemaStmt *stmt,
Oid *oldschema);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 3684f87a88..7fb6437973 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -423,6 +423,7 @@ typedef enum NodeTag
T_AlterCollationStmt,
T_CallStmt,
T_AlterStatsStmt,
+ T_AlterTableSetLoggedAllStmt,
/*
* TAGS FOR PARSE TREE NODES (parsenodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 48a79a7657..5d549b2476 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2234,6 +2234,15 @@ typedef struct AlterTableMoveAllStmt
bool nowait;
} AlterTableMoveAllStmt;
+typedef struct AlterTableSetLoggedAllStmt
+{
+ NodeTag type;
+ char *tablespacename;
+ ObjectType objtype; /* Object type to move */
+ bool logged;
+ bool nowait;
+} AlterTableSetLoggedAllStmt;
+
/* ----------------------
* Create/Alter Extension Statements
* ----------------------
--
2.27.0
----Next_Part(Fri_Dec_25_09_12_52_2020_873)----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Improving the "Routine Vacuuming" docs
@ 2022-04-12 21:53 Peter Geoghegan <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Peter Geoghegan @ 2022-04-12 21:53 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
Recent work on VACUUM and relfrozenxid advancement required that I
update the maintenance.sgml VACUUM documentation ("Routine
Vacuuming"). It was tricky to keep things current, due in part to
certain structural problems. Many of these problems are artifacts of
how the document evolved over time.
"Routine Vacuuming" ought to work as a high level description of how
VACUUM keeps the system going over time. The intended audience is
primarily DBAs, so low level implementation details should either be
given much less prominence, or not even mentioned. We should keep it
practical -- without going too far in the direction of assuming that
we know the limits of what information might be useful.
My high level concerns are:
* Instead of discussing FrozenTransactionId (and then explaining how
that particular magic value is not really used anymore anyway), why
not describe freezing in terms of the high level rules?
Something along the lines of the following seems more useful: "A tuple
whose xmin is frozen (and xmax is unset) is considered visible to
every possible MVCC snapshot. In other words, the transaction that
inserted the tuple is treated as if it ran and committed at some point
that is now *infinitely* far in the past."
It might also be useful to describe freezing all of a live tuple's
XIDs as roughly the opposite process as completely physically removing
a dead tuple. It follows that we don't necessarily need to freeze
anything to advance relfrozenxid (especially not on Postgres 15).
* The general description of how the XID space works similarly places
way too much emphasis on low level details that are of very little
relevance.
These details would even seem totally out of place if I was the
intended audience. The problem isn't really that the information is
too technical. The problem is that we emphasize mechanistic stuff
while never quite explaining the point of it all.
Currently, "25.1.5. Preventing Transaction ID Wraparound Failures"
says this, right up-front:
"But since transaction IDs have limited size (32 bits) a cluster that
runs for a long time (more than 4 billion transactions) would suffer
transaction ID wraparound"
This is way too mechanistic. We totally muddle things by even
mentioning 4 billion XIDs in the first place. It seems like a
confusing artefact of a time before freezing was invented, back when
you really could have XIDs that were more than 2 billion XIDs apart.
This statement has another problem: it's flat-out untrue. The
xidStopLimit stuff will reliably kick in at about 2 billion XIDs.
* The description of wraparound sounds terrifying, implying that data
corruption can result.
The alarming language isn't proportionate to the true danger
(something I complained about in a dedicated thread last year [1]).
* XID space isn't really a precious resource -- it isn't even a
resource at all IMV.
ISTM that we should be discussing wraparound as an issue about the
maximum *difference* between any two unfrozen XIDs in a
cluster/installation.
Talking about an abstract-sounding XID space seems to me to be quite
counterproductive. The logical XID space is practically infinite,
after all. We should move away from the idea that physical XID space
is a precious resource. Sure, users are often concerned that the
xidStopLimit mechanism might kick-in, effectively resulting in an
outage. That makes perfect sense. But it doesn't follow that XIDs are
precious, and implying that they are intrinsically valuable just
confuses matters.
First of all, physical XID space is usually abundantly available. A
"distance" of ~2 billion XIDs is a vast distance in just about any
application (barring those with pathological problems, such as a
leaked replication slot). Second of all, Since the amount of physical
freezing required to be able to advance relfrozenxid by any given
amount (amount of XIDs) varies enormously, and is not even predictable
for a given table (because individual tables don't get their own
physical XID space), the age of datfrozenxid predicts very little
about how close we are to having the dreaded xidStopLimit mechanism
kick in. We do need some XID-wise slack, but that's just a way of
absorbing shocks -- it's ballast, usually only really needed for one
or two very large tables.
Third of all, and most importantly, the whole idea that we can just
put off freezing indefinitely and actually reduce the pain (rather
than having a substantial increase in problems) seems to have just
about no basis in reality, at least once you get into the tens of
millions range (though usually well before that).
Why should you be better off if all of your freezing occurs in one big
balloon payment? Sometimes getting into debt for a while is useful,
but why should it make sense to keep delaying freezing? And if it
doesn't make sense, then why does it still make sense to treat XID
space as a precious resource?
* We don't cleanly separate discussion of anti-wraparound autovacuums,
and aggressive vacuums, and the general danger of wraparound (by which
I actually mean the danger of having the xidStopLimit stop limit kick
in).
I think that we should move towards a world in which we explicitly
treat the autovacuum anti-wraparound criteria as not all that
different to any of the standard criteria (so we probably still have
the behavior with autovacuums not being cancellable, but it would be a
dynamic thing that didn't depend on the original reason why
autovacuum.c launched an autovacuum worker). But even now we aren't
clear enough about the fact that anti-wraparound autovacuums really
aren't all that special. Which makes them seem scarier than they
should be.
[1] https://postgr.es/m/CAH2-Wzk_FxfJvs4TnUtj=DCsokbiK0CxfjZ9jjrfSx8sTWkeUg@mail.gmail.com
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Improving the "Routine Vacuuming" docs
@ 2022-04-12 23:24 David G. Johnston <[email protected]>
parent: Peter Geoghegan <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2022-04-12 23:24 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Tue, Apr 12, 2022 at 2:53 PM Peter Geoghegan <[email protected]> wrote:
> Recent work on VACUUM and relfrozenxid advancement required that I
> update the maintenance.sgml VACUUM documentation ("Routine
> Vacuuming"). It was tricky to keep things current, due in part to
> certain structural problems. Many of these problems are artifacts of
> how the document evolved over time.
>
> "Routine Vacuuming" ought to work as a high level description of how
> VACUUM keeps the system going over time. The intended audience is
> primarily DBAs, so low level implementation details should either be
> given much less prominence, or not even mentioned. We should keep it
> practical -- without going too far in the direction of assuming that
> we know the limits of what information might be useful.
>
+1
I've attached some off-the-cuff thoughts on reworking the first three
paragraphs and the note.
It's hopefully useful for providing perspective if nothing else.
> My high level concerns are:
>
> * Instead of discussing FrozenTransactionId (and then explaining how
> that particular magic value is not really used anymore anyway), why
> not describe freezing in terms of the high level rules?
>
Agreed and considered
>
> Something along the lines of the following seems more useful: "A tuple
> whose xmin is frozen (and xmax is unset) is considered visible to
> every possible MVCC snapshot. In other words, the transaction that
> inserted the tuple is treated as if it ran and committed at some point
> that is now *infinitely* far in the past."
>
I'm assuming and caring only about visible rows when I'm reading this
section. Maybe we need to make that explicit - only xmin matters (and the
invisible frozen flag)?
> It might also be useful to describe freezing all of a live tuple's
> XIDs as roughly the opposite process as completely physically removing
> a dead tuple. It follows that we don't necessarily need to freeze
> anything to advance relfrozenxid (especially not on Postgres 15).
>
I failed to pickup on how this and "mod-2^32" math interplay, and I'm not
sure I care when reading this. It made more sense to consider "shortest
path" along the "circle".
> Currently, "25.1.5. Preventing Transaction ID Wraparound Failures"
> says this, right up-front:
>
> "But since transaction IDs have limited size (32 bits) a cluster that
> runs for a long time (more than 4 billion transactions) would suffer
> transaction ID wraparound"
>
I both agree and disagree - where I settled (as of now) is reflected in the
patch.
> * The description of wraparound sounds terrifying, implying that data
> corruption can result.
>
Agreed, though I just skimmed a bit after the material the patch covers.
>
> * XID space isn't really a precious resource -- it isn't even a
> resource at all IMV.
>
Agreed
>
> * We don't cleanly separate discussion of anti-wraparound autovacuums,
> and aggressive vacuums, and the general danger of wraparound (by which
> I actually mean the danger of having the xidStopLimit stop limit kick
> in).
>
Didn't really get this far.
I am wondering, for the more technical details, is there an existing place
to send xrefs, do you plan to create one, or is it likely unnecessary?
David J.
Attachments:
[application/octet-stream] v0001-doc-reworking-of-vacuum-transaction-wraparound-section.patch (6.3K, ../../CAKFQuwbGWVmYQqp=Oujieb5pVcy+pRh-8N95kX1XWMhGWxBzVw@mail.gmail.com/3-v0001-doc-reworking-of-vacuum-transaction-wraparound-section.patch)
download | inline diff:
commit 5b747b6427c9f5bf080ed61073f9be4eef66d537
Author: David G. Johnston <[email protected]>
Date: Tue Apr 12 23:08:49 2022 +0000
doc-wip: thoughts regarding reworking of vacuum-transaction-wraparound section
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index a209a63304..88e1a47cde 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -414,77 +414,32 @@
</indexterm>
<para>
- <productname>PostgreSQL</productname>'s
- <link linkend="mvcc-intro">MVCC</link> transaction semantics
- depend on being able to compare transaction ID (<acronym>XID</acronym>)
- numbers: a row version with an insertion XID greater than the current
- transaction's XID is <quote>in the future</quote> and should not be visible
- to the current transaction. But since transaction IDs have limited size
- (32 bits) a cluster that runs for a long time (more
- than 4 billion transactions) would suffer <firstterm>transaction ID
- wraparound</firstterm>: the XID counter wraps around to zero, and all of a sudden
- transactions that were in the past appear to be in the future — which
- means their output become invisible. In short, catastrophic data loss.
- (Actually the data is still there, but that's cold comfort if you cannot
- get at it.) To avoid this, it is necessary to vacuum every table
- in every database at least once every two billion transactions.
+ This vacuum responsibility is necessary due to the fact that transaction ID (xid) numbers
+ are assigned serially and stored in a unsiged 32bit integer: incrementing INT_MAX causes
+ a wraparound such that the value becomes INT_MIN. Because of this, the id space is
+ circular, and the shortest path between two transactions either goes backward or forward,
+ representing visible and not visible respectively. This results in a situation where,
+ without maintenance, a transaction (or, more precisely, the rows created by that transaction,
+ the xid being stored in xmin) that was reached going backward from the current transaction
+ will have a shorter path going forward for some future transaction. Thus vacuum will
+ periodically flag older transactions as being "frozen", such that they are considered visible
+ to every "current" transaction without using the shortest path logic.
</para>
-
+
<para>
- The reason that periodic vacuuming solves the problem is that
- <command>VACUUM</command> will mark rows as <emphasis>frozen</emphasis>, indicating that
- they were inserted by a transaction that committed sufficiently far in
- the past that the effects of the inserting transaction are certain to be
- visible to all current and future transactions.
- Normal XIDs are
- compared using modulo-2<superscript>32</superscript> arithmetic. This means
- that for every normal XID, there are two billion XIDs that are
- <quote>older</quote> and two billion that are <quote>newer</quote>; another
- way to say it is that the normal XID space is circular with no
- endpoint. Therefore, once a row version has been created with a particular
- normal XID, the row version will appear to be <quote>in the past</quote> for
- the next two billion transactions, no matter which normal XID we are
- talking about. If the row version still exists after more than two billion
- transactions, it will suddenly appear to be in the future. To
- prevent this, <productname>PostgreSQL</productname> reserves a special XID,
- <literal>FrozenTransactionId</literal>, which does not follow the normal XID
- comparison rules and is always considered older
- than every normal XID.
- Frozen row versions are treated as if the inserting XID were
- <literal>FrozenTransactionId</literal>, so that they will appear to be
- <quote>in the past</quote> to all normal transactions regardless of wraparound
- issues, and so such row versions will be valid until deleted, no matter
- how long that is.
+ While vacuum will not touch a row's xmin while updating its frozen status, two reserved xid
+ values may seen. <literal>BootstreapTransactionId</literal> (1) may be seen on system catalog
+ tables to indicate records inserted during initdb. <literal>FronzenTransactionID</literal> (2)
+ may be seen on any table and also indicates that the row is frozen. This was the mechanism
+ used in versions prior to 9.4, when it was decided to keep the xmin unchanged for forensic use.
</para>
- <note>
- <para>
- In <productname>PostgreSQL</productname> versions before 9.4, freezing was
- implemented by actually replacing a row's insertion XID
- with <literal>FrozenTransactionId</literal>, which was visible in the
- row's <structname>xmin</structname> system column. Newer versions just set a flag
- bit, preserving the row's original <structname>xmin</structname> for possible
- forensic use. However, rows with <structname>xmin</structname> equal
- to <literal>FrozenTransactionId</literal> (2) may still be found
- in databases <application>pg_upgrade</application>'d from pre-9.4 versions.
- </para>
- <para>
- Also, system catalogs may contain rows with <structname>xmin</structname> equal
- to <literal>BootstrapTransactionId</literal> (1), indicating that they were
- inserted during the first phase of <application>initdb</application>.
- Like <literal>FrozenTransactionId</literal>, this special XID is treated as
- older than every normal XID.
- </para>
- </note>
-
<para>
- <xref linkend="guc-vacuum-freeze-min-age"/>
- controls how old an XID value has to be before rows bearing that XID will be
- frozen. Increasing this setting may avoid unnecessary work if the
- rows that would otherwise be frozen will soon be modified again,
- but decreasing this setting increases
- the number of transactions that can elapse before the table must be
- vacuumed again.
+ The shortest path mechanism on the circular 32bit space results in a 2 billion transaction
+ window. While a row can be frozen anytime while in that window doing so too soon will be
+ wasted effort if the row becomes dead long before it would have left the window.
+ The <xref linkend="guc-vacuum-freeze-min-age"/> setting controls how old a row has to be
+ before it is eligible for freezing.
</para>
<para>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Improving the "Routine Vacuuming" docs
@ 2022-04-13 15:40 Robert Haas <[email protected]>
parent: Peter Geoghegan <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Robert Haas @ 2022-04-13 15:40 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Tue, Apr 12, 2022 at 5:53 PM Peter Geoghegan <[email protected]> wrote:
> My high level concerns are:
>
> * Instead of discussing FrozenTransactionId (and then explaining how
> that particular magic value is not really used anymore anyway), why
> not describe freezing in terms of the high level rules?
>
> Something along the lines of the following seems more useful: "A tuple
> whose xmin is frozen (and xmax is unset) is considered visible to
> every possible MVCC snapshot. In other words, the transaction that
> inserted the tuple is treated as if it ran and committed at some point
> that is now *infinitely* far in the past."
I agree with this idea.
> * The description of wraparound sounds terrifying, implying that data
> corruption can result.
>
> The alarming language isn't proportionate to the true danger
> (something I complained about in a dedicated thread last year [1]).
I mostly agree with this, but not entirely. The section needs some
rephrasing, but xidStopLimit doesn't apply in single-user mode, and
relfrozenxid and datfrozenxid values can and do get corrupted. So it's
not a purely academic concern.
> * XID space isn't really a precious resource -- it isn't even a
> resource at all IMV.
I disagree with this. Usable XID space is definitely a resource, and
if you're in the situation where you care deeply about this section of
the documentation, it's probably one in short supply. Being careful
not to expend too many XIDs while fixing the problems that have cause
you to be short of safe XIDs is *definitely* a real thing.
> * We don't cleanly separate discussion of anti-wraparound autovacuums,
> and aggressive vacuums, and the general danger of wraparound (by which
> I actually mean the danger of having the xidStopLimit stop limit kick
> in).
I think it is wrong to conflate wraparound with xidStopLimit.
xidStopLimit is the final defense against an actual wraparound, and
like I say, an actual wraparound is quite possible if you put the
system in single user mode and then do something like this:
backend> VACUUM FULL;
Big ouch.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Improving the "Routine Vacuuming" docs
@ 2022-04-13 16:34 Peter Geoghegan <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Peter Geoghegan @ 2022-04-13 16:34 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Wed, Apr 13, 2022 at 8:40 AM Robert Haas <[email protected]> wrote:
> > Something along the lines of the following seems more useful: "A tuple
> > whose xmin is frozen (and xmax is unset) is considered visible to
> > every possible MVCC snapshot. In other words, the transaction that
> > inserted the tuple is treated as if it ran and committed at some point
> > that is now *infinitely* far in the past."
>
> I agree with this idea.
Cool. Maybe I should write a doc patch just for this part, then.
What do you think of the idea of relating freezing to removing tuples
by VACUUM at this point? This would be a basis for explaining how
freezing and tuple removal are constrained by the same cutoff. A very
old snapshot can hold up cleanup, but it can also hold up freezing to
the same degree (it's just not as obvious because we are less eager
about freezing by default).
> > The alarming language isn't proportionate to the true danger
> > (something I complained about in a dedicated thread last year [1]).
>
> I mostly agree with this, but not entirely. The section needs some
> rephrasing, but xidStopLimit doesn't apply in single-user mode, and
> relfrozenxid and datfrozenxid values can and do get corrupted. So it's
> not a purely academic concern.
I accept the distinction that you want to make is valid. More on that below.
> > * XID space isn't really a precious resource -- it isn't even a
> > resource at all IMV.
>
> I disagree with this. Usable XID space is definitely a resource, and
> if you're in the situation where you care deeply about this section of
> the documentation, it's probably one in short supply. Being careful
> not to expend too many XIDs while fixing the problems that have cause
> you to be short of safe XIDs is *definitely* a real thing.
I may have gone too far with this metaphor. My point was mostly that
XID space has a highly unpredictable cost (paid in freezing).
Perhaps we can agree on some (or even all) of the following specific points:
* We shouldn't mention "4 billion XIDs" at all.
* We should say that the issue is an issue of distances between
unfrozen XIDs. The maximum distance that can ever be allowed to emerge
between any two unfrozen XIDs in a cluster is about 2 billion XIDs.
* We don't need to say anything about how XIDs are compared, normal vs
permanent XIDs, etc.
* The system takes drastic intervention to prevent this implementation
restriction from becoming a problem, starting with anti-wraparound
autovacuums. Then there's the failsafe. Finally, there's the
xidStopLimit mechanism, our last line of defense.
> I think it is wrong to conflate wraparound with xidStopLimit.
> xidStopLimit is the final defense against an actual wraparound, and
> like I say, an actual wraparound is quite possible if you put the
> system in single user mode and then do something like this:
I forget to emphasize one aspect of the problem that seems quite
important: the document itself seems to conflate the xidStopLimit
mechanism with true wraparound. At least I thought so. Last year's
thread on this subject ('What is "wraparound failure", really?') was
mostly about that confusion. I personally found that very confusing,
and I doubt that I'm the only one.
There is no good reason to use single user mode anymore (a related
problem with the docs is that we still haven't made that point). And
the pg_upgrade bug that led to invalid relfrozenxid values was
flagrantly just a bug (adding a WARNING for this recently, in commit
e83ebfe6). So while I accept that the distinction you're making here
is valid, maybe we can fix the single user mode doc bug too, removing
the need to discuss "true wraparound" as a general phenomenon. You
shouldn't ever see it in practice anymore. If you do then either
you've done something that "invalidated the warranty", or you've run
into a legitimate bug.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Improving the "Routine Vacuuming" docs
@ 2022-04-13 20:24 Robert Haas <[email protected]>
parent: Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Robert Haas @ 2022-04-13 20:24 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Wed, Apr 13, 2022 at 12:34 PM Peter Geoghegan <[email protected]> wrote:
> What do you think of the idea of relating freezing to removing tuples
> by VACUUM at this point? This would be a basis for explaining how
> freezing and tuple removal are constrained by the same cutoff. A very
> old snapshot can hold up cleanup, but it can also hold up freezing to
> the same degree (it's just not as obvious because we are less eager
> about freezing by default).
I think something like that could be useful, if we can find a way to
word it sufficiently clearly.
> Perhaps we can agree on some (or even all) of the following specific points:
>
> * We shouldn't mention "4 billion XIDs" at all.
>
> * We should say that the issue is an issue of distances between
> unfrozen XIDs. The maximum distance that can ever be allowed to emerge
> between any two unfrozen XIDs in a cluster is about 2 billion XIDs.
>
> * We don't need to say anything about how XIDs are compared, normal vs
> permanent XIDs, etc.
>
> * The system takes drastic intervention to prevent this implementation
> restriction from becoming a problem, starting with anti-wraparound
> autovacuums. Then there's the failsafe. Finally, there's the
> xidStopLimit mechanism, our last line of defense.
Those all sound pretty reasonable. There's a little bit of doubt in my
mind about the third one; I think it could possibly be useful to
explain that the XID space is circular and 0-2 are special, but maybe
not.
> > I think it is wrong to conflate wraparound with xidStopLimit.
> > xidStopLimit is the final defense against an actual wraparound, and
> > like I say, an actual wraparound is quite possible if you put the
> > system in single user mode and then do something like this:
>
> I forget to emphasize one aspect of the problem that seems quite
> important: the document itself seems to conflate the xidStopLimit
> mechanism with true wraparound. At least I thought so. Last year's
> thread on this subject ('What is "wraparound failure", really?') was
> mostly about that confusion. I personally found that very confusing,
> and I doubt that I'm the only one.
OK.
> There is no good reason to use single user mode anymore (a related
> problem with the docs is that we still haven't made that point). And
Agreed.
> the pg_upgrade bug that led to invalid relfrozenxid values was
> flagrantly just a bug (adding a WARNING for this recently, in commit
> e83ebfe6). So while I accept that the distinction you're making here
> is valid, maybe we can fix the single user mode doc bug too, removing
> the need to discuss "true wraparound" as a general phenomenon. You
> shouldn't ever see it in practice anymore. If you do then either
> you've done something that "invalidated the warranty", or you've run
> into a legitimate bug.
I think it is probably important to discuss this, but along the lines
of: it is possible to bypass all of these safeguards and cause a true
wraparound by running in single-user mode. Don't do that. There's no
wraparound situation that can't be addressed just fine in multi-user
mode, and here's how to do that. In previous releases, we used to
sometimes recommend single user mode, but that's no longer necessary
and not a good idea, so steer clear.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Improving the "Routine Vacuuming" docs
@ 2022-04-13 21:18 Peter Geoghegan <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Peter Geoghegan @ 2022-04-13 21:18 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Wed, Apr 13, 2022 at 1:25 PM Robert Haas <[email protected]> wrote:
> On Wed, Apr 13, 2022 at 12:34 PM Peter Geoghegan <[email protected]> wrote:
> > What do you think of the idea of relating freezing to removing tuples
> > by VACUUM at this point? This would be a basis for explaining how
> > freezing and tuple removal are constrained by the same cutoff.
> I think something like that could be useful, if we can find a way to
> word it sufficiently clearly.
What if the current "25.1.5. Preventing Transaction ID
Wraparound Failures" section was split into two parts? The first part
would cover freezing, the second part would cover
relfrozenxid/relminmxid advancement.
Freezing can sensibly be discussed before introducing relfrozenxid.
Freezing is a maintenance task that makes tuples self-contained
things, suitable for long term storage. Freezing makes tuples not rely
on transient transaction metadata (mainly clog), and so is an overhead
of storing data in Postgres long term.
That's how I think of it, at least. That definition seems natural to me.
> Those all sound pretty reasonable.
Great.
I have two more things that I see as problems. Would be good to get
your thoughts here, too. They are:
1. We shouldn't really be discussing VACUUM FULL here at all, except
to say that it's out of scope, and probably a bad idea.
You once wrote about the problem of how VACUUM FULL is perceived by
users (VACUUM FULL doesn't mean "VACUUM, but better"), expressing an
opinion of VACUUM FULL that I agree with fully. The docs definitely
contributed to that problem.
2. We don't go far enough in emphasizing the central role of autovacuum.
Technically the entire section assumes that its primary audience are
those users that have opted to not use autovacuum. This seems entirely
backwards to me.
We should make it clear that technically autovacuum isn't all that
different from running your own VACUUM commands, because that's an
important part of understanding autovacuum. But that's all. ISTM that
anybody that *entirely* opts out of using autovacuum is just doing it
wrong (besides, it's kind of impossible to do it anyway, what with
anti-wraparound autovacuum being impossible to disable).
There is definitely a role for using tools like cron to schedule
off-hours VACUUM operations, and that's still worth pointing out
prominently. But that should be a totally supplementary thing, used
when the DBA understands that running VACUUM off-hours is less
disruptive.
> There's a little bit of doubt in my
> mind about the third one; I think it could possibly be useful to
> explain that the XID space is circular and 0-2 are special, but maybe
> not.
I understand the concern. I'm not saying that this kind of information
doesn't have any business being in the docs. Just that it has no
business being in this particular chapter of the docs. In fact, it
doesn't even belong in "III. Server Administration". If it belongs
anywhere, it should be in some chapter from "VII. Internals".
Discussing it here just seems inappropriate (and would be even if it
wasn't how we introduce discussion of wraparound). It's really only
tangentially related to VACUUM anyway. It seems like it should be
covered when discussing the heapam on-disk representation.
> I think it is probably important to discuss this, but along the lines
> of: it is possible to bypass all of these safeguards and cause a true
> wraparound by running in single-user mode. Don't do that. There's no
> wraparound situation that can't be addressed just fine in multi-user
> mode, and here's how to do that. In previous releases, we used to
> sometimes recommend single user mode, but that's no longer necessary
> and not a good idea, so steer clear.
Yeah, that should probably happen somewhere.
On the other hand...why do we even need to tolerate wraparound in
single-user mode? I do see some value in reserving extra XIDs that can
be used in single-user mode (apparently single-user mode can be used
in scenarios where you have buggy event triggers, things like that).
But that in itself does not justify allowing single-user mode to
exceed xidWrapLimit.
Why shouldn't single-user mode also refuse to allocate new XIDs when
we reach xidWrapLimit (as opposed to when we reach xidStopLimit)?
Maybe there is a good reason to believe that allowing single-user mode
to corrupt the database is the lesser evil, but if there is then I'd
like to know the reason.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-04-13 21:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-11 14:21 [PATCH v2 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED Kyotaro Horiguchi <[email protected]>
2022-04-12 21:53 Improving the "Routine Vacuuming" docs Peter Geoghegan <[email protected]>
2022-04-12 23:24 ` Re: Improving the "Routine Vacuuming" docs David G. Johnston <[email protected]>
2022-04-13 15:40 ` Re: Improving the "Routine Vacuuming" docs Robert Haas <[email protected]>
2022-04-13 16:34 ` Re: Improving the "Routine Vacuuming" docs Peter Geoghegan <[email protected]>
2022-04-13 20:24 ` Re: Improving the "Routine Vacuuming" docs Robert Haas <[email protected]>
2022-04-13 21:18 ` Re: Improving the "Routine Vacuuming" docs Peter Geoghegan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox