Fix concurrent indexing operations with temporary tables

Enterprise / PostgreSQL - Michael Paquier [] - 22 January 2020 00:49 UTC

Attempting to use CREATE INDEX, DROP INDEX or REINDEX with CONCURRENTLY on a temporary relation with ON COMMIT actions triggered unexpected errors because those operations use multiple transactions internally to complete their work. Here is for example one confusing error when using ON COMMIT DELETE ROWS: ERROR: index "foo" already contains data

Issues related to temporary relations and concurrent indexing are fixed in this commit by enforcing the non-concurrent path to be taken for temporary relations even if using CONCURRENTLY, transparently to the user. Using a non-concurrent path does not matter in practice as locks cannot be taken on a temporary relation by a session different than the one owning the relation, and the non-concurrent operation is more effective.

The problem exists with REINDEX since v12 with the introduction of CONCURRENTLY, and with CREATE/DROP INDEX since CONCURRENTLY exists for those commands. In all supported versions, this caused only confusing error messages to be generated. Note that with REINDEX, it was also possible to issue a REINDEX CONCURRENTLY for a temporary relation owned by a different session, leading to a server crash.

The idea to enforce transparently the non-concurrent code path for temporary relations comes originally from Andres Freund.

a904abe2e2 Fix concurrent indexing operations with temporary tables
doc/src/sgml/ref/create_index.sgml | 5 ++
doc/src/sgml/ref/drop_index.sgml | 5 ++
doc/src/sgml/ref/reindex.sgml | 5 ++
src/backend/catalog/index.c | 9 ++++
src/backend/commands/indexcmds.c | 65 +++++++++++++++++++++-----
src/backend/commands/tablecmds.c | 18 +++++++-
src/test/regress/expected/create_index.out | 74 ++++++++++++++++++++++++++++++
src/test/regress/sql/create_index.sql | 67 +++++++++++++++++++++++++++
8 files changed, 235 insertions(+), 13 deletions(-)


  • Share