Here's another small patch, this time from Simon Riggs. Maybe he already
posted it for this commitfest, but I didn't find it in a quick look so
here it is.
This patch reduces the amount of bloat you get from running CREATE INDEX
CONCURRENTLY by destroying the snapshot taken in the first phase, before
entering the second phase. This allows the global xmin to advance,
letting concurrent vacuum keep bloat in other tables in check.
Currently this implements the change for btree indexes only, but doing
it for other indexes should be a one-liner.
--
Álvaro Herrera
diff --git a/src/backend/access/nbtree/nbtree.c
b/src/backend/access/nbtree/nbtree.c
index 469e7ab..853d8e0 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -168,7 +168,17 @@ btbuild(Relation heap, Relation index, IndexInfo
*indexInfo)
reltuples = IndexBuildHeapScan(heap, index, indexInfo, true,
btbuildCallback, (void *) &buildstate);
- /* okay, all heap tuples are indexed */
+ /*
+ * By this point, all heap tuples are indexed so we don't need the
+ * snapshot to complete the index build.
+ *
+ * Ideally, we'd like to drop our snapshot as soon as possible, to
+ * avoid holding xmin back and causing bloat. That is only possible
+ * if we are running a concurrent index build because that command
+ * is sufficiently restricted to allow this to happen safely.
+ */
+ if (indexInfo->ii_Concurrent)
+ PopActiveSnapshot();
if (buildstate.spool2 && !buildstate.haveDead)
{
/* spool2 turns out to be unnecessary */
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index ed6136c..469d455 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -770,11 +770,27 @@ DefineIndex(Oid relationId,
/* Now build the index */
index_build(rel, indexRelation, indexInfo, stmt->primary, false);
+ /*
+ * At this point it is possible that the indexAM has popped its
+ * snapshot and will be unable to run other commands, but we
+ * accept that restriction because it means the snapshot is held
+ * open for much less time.
+ */
+ PopActiveSnapshotIfAny();
+
/* Close both the relations, but keep the locks */
heap_close(rel, NoLock);
index_close(indexRelation, NoLock);
/*
+ * Now we need a snapshot so we can update our indexes. The snapshot
+ * here is different from that used to build the index, but the only
+ * thing we will do with it is update the pg_index row for the index
+ * we have locked, so we're not in danger of mismatch.
+ */
+ PushActiveSnapshot(GetTransactionSnapshot());
+
+ /*
* Update the pg_index row to mark the index as ready for inserts. Once
we
* commit this transaction, any new transactions that open the table
must
* insert new entries into the index for insertions and non-HOT updates.
diff --git a/src/backend/utils/time/snapmgr.c b/src/backend/utils/time/snapmgr.c
index 92afc32..4d949f6 100644
--- a/src/backend/utils/time/snapmgr.c
+++ b/src/backend/utils/time/snapmgr.c
@@ -806,11 +806,19 @@ UpdateActiveSnapshotCommandId(void)
void
PopActiveSnapshot(void)
{
+ Assert(ActiveSnapshot->as_snap->active_count > 0);
+ PopActiveSnapshotIfAny();
+}
+
+void
+PopActiveSnapshotIfAny(void)
+{
ActiveSnapshotElt *newstack;
- newstack = ActiveSnapshot->as_next;
+ if (ActiveSnapshot == NULL)
+ return;
- Assert(ActiveSnapshot->as_snap->active_count > 0);
+ newstack = ActiveSnapshot->as_next;
ActiveSnapshot->as_snap->active_count--;
diff --git a/src/include/utils/snapmgr.h b/src/include/utils/snapmgr.h
index 2618cc4..66368d2 100644
--- a/src/include/utils/snapmgr.h
+++ b/src/include/utils/snapmgr.h
@@ -75,6 +75,7 @@ extern void PushActiveSnapshot(Snapshot snapshot);
extern void PushCopiedSnapshot(Snapshot snapshot);
extern void UpdateActiveSnapshotCommandId(void);
extern void PopActiveSnapshot(void);
+extern void PopActiveSnapshotIfAny(void);
extern Snapshot GetActiveSnapshot(void);
extern bool ActiveSnapshotSet(void);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
