It would be useful to have CREATE INDEX CONCURRENTLY be ignored by
vacuuming's OldestXmin. Frequently in OLTP scenarios, CIC transactions
are severely disruptive because they are the only long-running
transactions in the system, and VACUUM has to keep rows only for their
sake, pointlessly. The motivation for this change seems well justified
to me (but feel free to argue if you think otherwise).
So the question is how to implement this. Here's a very small patch for
it:
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 374e2d0efe..9081dfe384 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -532,6 +532,12 @@ DefineIndex(Oid relationId,
errmsg("cannot use more than %d columns in an index",
INDEX_MAX_KEYS)));
+ if (stmt->concurrent && !IsTransactionBlock())
+ {
+ Assert(GetCurrentTransactionIdIfAny() == InvalidTransactionId);
+ MyPgXact->vacuumFlags |= PROC_IN_VACUUM;
+ }
+
/*
* Only SELECT ... FOR UPDATE/SHARE are allowed while doing a standard
* index build; but for concurrent builds we allow INSERT/UPDATE/DELETE
There's an obvious flaw, which is that this doesn't consider expressions
in partial indexes and column definitions. That's moderately easy to
fix. But there are less obvious flaws, such as: is it possible that
CIC's xmin is required for other reasons? (such as access to catalogs,
which get cleaned by concurrent vacuum) If it is, can we fix that
problem by keeping track of a separate Xmin for catalog vacuuming
purposes? (We already have catalog_xmin for replication slots, so this
is not completely ridiculous I think ...)
--
Álvaro Herrera
