When running CREATE INDEX CONCURRENTLY, the DefineIndex() code in
src/backend/commands/indexcmds.c does a few things that one would expect
from the documentation. And then at the end it executes code described
like this:
"The index is now valid in the sense that it contains all currently
interesting tuples. But since it might not contain tuples deleted just
before the reference snap was taken, we have to wait out any
transactions that might have older snapshots. Obtain a list of VXIDs of
such transactions, and wait for them individually."
It's possible to end up with a long series in pg_locks waiting for
virtualxid entries at this point, for as long as some set of giant
queries takes to execute, and you'll only see them one at a time. The
documentation warns:
"PostgreSQL must perform two scans of the table, and in addition it must
wait for all existing transactions that could potentially use the index
to terminate."
That's correct, but easy to read the wrong way. I always assumed that
this meant it was going to wait behind anything that had a shared lock
or such on the table, things that had already accessed it. This is the
case with some earlier parts of this same code path. But when it comes
to the end here, the scope is actually broader than that. And since
there's a session-level lock on the table the whole time this wait loop
is executing, that makes considerable secondary havoc possible here.
You can end up waiting an unbounded amount of time for some long-running
transaction, one not even expected to enter into the rebuild work, to
finish, which leaves you no idea what's happening unless you know just
what to look for. (Watching such havoc actually happen is what prompted
this investigation)
What makes it worse is that the wait shows up as a virtualxid one, which
doesn't pop up on many common samples of things to look for in
pg_locks. It would be reasonable but also incorrect for admins to
assume a table one would be visible if running into the case alluded to
in the docs. The serial way locks are obtained is unexpected too.
Attached patch expands the warnings around this command to reflect both
issues:
-The waiting time is not necessarily limited to just things that involve
the table
-The locks it obtains while running this phase of the rebuild are unusual
--
Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 43b6499..cb3334b 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -381,7 +381,17 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</
<para>
In a concurrent index build, the index is actually entered into the
system catalogs in one transaction, then the two table scans occur in a
- second and third transaction.
+ second and third transaction. All active transactions at the time the
+ second table scan starts, not just ones that already involve the table,
+ have the potential to block the concurrent index creation for however
+ long it takes for them to finish. When checking for transactions that
+ could still use the original index, concurrent index creation advances
+ through potentially interfering older transactions one at a time,
+ obtaining locks on their virtual transaction identifiers to wait for
+ them to complete.
+ </para>
+
+ <para>
If a problem arises while scanning the table, such as a
uniqueness violation in a unique index, the <command>CREATE INDEX</>
command will fail but leave behind an <quote>invalid</> index. This index
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
