From: Michael Paquier <michael@paquier.xyz>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Manuel Rigger <rigger.manuel@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: REINDEX CONCURRENTLY unexpectedly fails
Date: Wed, 15 Jan 2020 10:39:03 +0900
Message-ID: <20200115013824.GA2243@paquier.xyz> (raw)
In-Reply-To: <d62659d5-8860-075f-1e86-f17834f26495@iki.fi>
References: <20191115080706.GI1849@paquier.xyz>
<20191120013649.2xfr2dnznyjgluch@alap3.anarazel.de>
<20191120035408.GA4243@paquier.xyz>
<20191212213709.neopqccvdo724eha@alap3.anarazel.de>
<20191213034536.GD1942@paquier.xyz>
<20191223015956.GB34339@paquier.xyz>
<8f0bde7b-2d96-fb5c-cd0e-553694c1d376@iki.fi>
<20200108081930.GF3413@paquier.xyz>
<20200109030619.GC2251@paquier.xyz>
<d62659d5-8860-075f-1e86-f17834f26495@iki.fi>
On Tue, Jan 14, 2020 at 11:41:11PM +0200, Heikki Linnakangas wrote:
> I'm not a fan of all those changes in RangeVarCallbackForDropRelation() to
> ensure that you get an AccessExclusiveLock to begin with. It gets pretty
> complicated, and it feels like you need to special-case temporary tables in
> dozen different places. I liked the v3 of this patch better. It's true that
> you're upgrading the ShareUpdateExclusiveLock to AccessExclusiveLock, but
> since it's a temporary table, there really should be no other backend
> holding a lock on it.
Thanks for taking the time to share your opinion. That was as well my
feeling with the peanut and the sledgehammer. I liked the peanuts,
but not the hammer part.
There are still some parts I liked about v4 (doc wording, tweaks about
the shape of RelationSupportsConcurrentIndexing and its use in
assertions, setting up the concurrent flag in RemoveRelation and use an
assert in index_drop is also cleaner), so I kept a good portion of
v4. Attached is an updated patch, v5, that removes the parts
enforcing the lock when looking at the relation OID based on its
RangeVar.
Any thoughts?
--
Michael
Attachments:reindex-conc-temp-v5.patch (text/x-diff, 15.8K) downloaddiff --git a/src/include/catalog/index.h b/src/include/catalog/index.hindex a2890c1314..51723a8d8d 100644--- a/src/include/catalog/index.h+++ b/src/include/catalog/index.h@@ -115,6 +115,8 @@ extern bool CompareIndexInfo(IndexInfo *info1, IndexInfo *info2,
extern void BuildSpeculativeIndexInfo(Relation index, IndexInfo *ii);
+extern bool RelationSupportsConcurrentIndexing(Oid relid);+
extern void FormIndexDatum(IndexInfo *indexInfo,
TupleTableSlot *slot,
EState *estate,
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 3e59e647e5..4139232b51 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -2016,6 +2016,13 @@ index_drop(Oid indexId, bool concurrent, bool concurrent_lock_mode)
LOCKTAG heaplocktag;
LOCKMODE lockmode;
+ /*+ * A relation not supporting concurrent indexing should never do+ * a concurrent index drop or try to use a concurrent lock mode.+ */+ Assert(RelationSupportsConcurrentIndexing(indexId) ||+ (!concurrent && !concurrent_lock_mode));+
/*
* To drop an index safely, we must grab exclusive lock on its parent
* table. Exclusive lock on the index alone is insufficient because
@@ -2108,6 +2115,9 @@ index_drop(Oid indexId, bool concurrent, bool concurrent_lock_mode)
*/
CacheInvalidateRelcache(userHeapRelation);
+ /* Relation had better support concurrent indexing */+ Assert(RelationSupportsConcurrentIndexing(indexId));+
/* save lockrelid and locktag for below, then close but keep locks */
heaprelid = userHeapRelation->rd_lockInfo.lockRelId;
SET_LOCKTAG_RELATION(heaplocktag, heaprelid.dbId, heaprelid.relId);
@@ -2490,6 +2500,30 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2,
return true;
}
+/*+ * RelationSupportsConcurrentIndexing+ *+ * Check if a relation supports concurrent builds or not. This is used+ * before processing CREATE INDEX, DROP INDEX or REINDEX when using+ * CONCURRENTLY to decide if the operation is supported.+ */+bool+RelationSupportsConcurrentIndexing(Oid relid)+{+ /*+ * Build indexes non-concurrently for temporary relations. Such+ * relations only work with the session assigned to them, so they are+ * not subject to concurrent concerns, and a concurrent build would+ * cause issues with ON COMMIT actions triggered by the transactions+ * of the concurrent build. A non-concurrent reindex is also more+ * efficient in this case.+ */+ if (get_rel_persistence(relid) == RELPERSISTENCE_TEMP)+ return false;++ return true;+}+
/* ----------------
* BuildSpeculativeIndexInfo
* Add extra state to IndexInfo record
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex 52ce02f898..d63a885638 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -485,6 +485,13 @@ DefineIndex(Oid relationId,
GUC_ACTION_SAVE, true, 0, false);
}
+ /*+ * Enforce non-concurrent build if the relation does not support this+ * option. Do this before any use of the concurrent option is done.+ */+ if (!RelationSupportsConcurrentIndexing(relationId))+ stmt->concurrent = false;+
/*
* Start progress report. If we're building a partition, this was already
* done.
@@ -2347,7 +2354,7 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
persistence = irel->rd_rel->relpersistence;
index_close(irel, NoLock);
- if (concurrent)+ if (concurrent && RelationSupportsConcurrentIndexing(indOid))
ReindexRelationConcurrently(indOid, options);
else
reindex_index(indOid, false, persistence,
@@ -2440,7 +2447,8 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
0,
RangeVarCallbackOwnsTable, NULL);
- if (concurrent)+ if (concurrent &&+ RelationSupportsConcurrentIndexing(heapOid))
{
result = ReindexRelationConcurrently(heapOid, options);
@@ -2646,7 +2654,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
/* functions in indexes may want a snapshot set */
PushActiveSnapshot(GetTransactionSnapshot());
- if (concurrent)+ if (concurrent &&+ RelationSupportsConcurrentIndexing(relid))
{
(void) ReindexRelationConcurrently(relid, options);
/* ReindexRelationConcurrently() does the verbose output */
@@ -2769,6 +2778,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
/* Open relation to get its indexes */
heapRelation = table_open(relationOid, ShareUpdateExclusiveLock);
+ /* Relation had better support concurrent indexing */+ Assert(RelationSupportsConcurrentIndexing(relationOid));+
/* Add all the valid indexes of relation to list */
foreach(lc, RelationGetIndexList(heapRelation))
{
@@ -2862,6 +2874,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
/* Save the list of relation OIDs in private context */
oldcontext = MemoryContextSwitchTo(private_context);
+ /* Relation had better support concurrent indexing */+ Assert(RelationSupportsConcurrentIndexing(heapId));+
/* Track the heap relation of this index for session locks */
heapRelationIds = list_make1_oid(heapId);
@@ -2937,6 +2952,13 @@ ReindexRelationConcurrently(Oid relationOid, int options)
heapRel = table_open(indexRel->rd_index->indrelid,
ShareUpdateExclusiveLock);
+ /*+ * Also check for active uses of the relation in the current+ * transaction, including open scans and pending AFTER trigger+ * events.+ */+ CheckTableNotInUse(indexRel, "REINDEX");+
pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
RelationGetRelid(heapRel));
pgstat_progress_update_param(PROGRESS_CREATEIDX_COMMAND,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 2ec3fc5014..7bcd08a479 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -1239,7 +1239,11 @@ RemoveRelations(DropStmt *drop)
/* DROP CONCURRENTLY uses a weaker lock, and has some restrictions */
if (drop->concurrent)
{
- flags |= PERFORM_DELETION_CONCURRENTLY;+ /*+ * Note that for temporary relations this lock may get upgraded+ * later on, but as a session is the only able to work on its+ * temporary relations, this is actually fine.+ */
lockmode = ShareUpdateExclusiveLock;
Assert(drop->removeType == OBJECT_INDEX);
if (list_length(drop->objects) != 1)
@@ -1330,6 +1334,19 @@ RemoveRelations(DropStmt *drop)
continue;
}
+ /*+ * Decide if concurrent mode needs to be used here or not. There+ * is no way to know if the relation supports concurrent indexing or+ * not without knowing its OID, so this is not done beforehand.+ */+ if (drop->concurrent &&+ RelationSupportsConcurrentIndexing(relOid))+ {+ Assert(list_length(drop->objects) == 1 &&+ drop->removeType == OBJECT_INDEX);+ flags |= PERFORM_DELETION_CONCURRENTLY;+ }+
/* OK, we're ready to delete this one */
obj.classId = RelationRelationId;
obj.objectId = relOid;
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.outindex 6446907a65..cf1a0ca2f2 100644--- a/src/test/regress/expected/create_index.out+++ b/src/test/regress/expected/create_index.out@@ -1435,6 +1435,31 @@ Indexes:
"concur_index5" btree (f2) WHERE f1 = 'x'::text
"std_index" btree (f2)
+-- Temporary tables with concurrent builds and on-commit actions+-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored.+-- PRESERVE ROWS, the default.+CREATE TEMP TABLE concur_temp (f1 int, f2 text)+ ON COMMIT PRESERVE ROWS;+INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');+CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);+DROP INDEX CONCURRENTLY concur_temp_ind;+DROP TABLE concur_temp;+-- ON COMMIT DROP+BEGIN;+CREATE TEMP TABLE concur_temp (f1 int, f2 text)+ ON COMMIT DROP;+INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');+-- Fails when running in a transaction.+CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);+ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block+COMMIT;+-- ON COMMIT DELETE ROWS+CREATE TEMP TABLE concur_temp (f1 int, f2 text)+ ON COMMIT DELETE ROWS;+INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');+CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);+DROP INDEX CONCURRENTLY concur_temp_ind;+DROP TABLE concur_temp;
--
-- Try some concurrent index drops
--
@@ -2418,6 +2443,55 @@ SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
(1 row)
DROP TABLE concur_exprs_tab;
+-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored.+-- ON COMMIT PRESERVE ROWS, the default.+CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text)+ ON COMMIT PRESERVE ROWS;+INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar');+CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2);+REINDEX TABLE CONCURRENTLY concur_temp_tab_1;+REINDEX INDEX CONCURRENTLY concur_temp_ind_1;+-- Still fails in transaction blocks+BEGIN;+REINDEX INDEX CONCURRENTLY concur_temp_ind_1;+ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block+COMMIT;+-- ON COMMIT DELETE ROWS+CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text)+ ON COMMIT DELETE ROWS;+CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2);+REINDEX TABLE CONCURRENTLY concur_temp_tab_2;+REINDEX INDEX CONCURRENTLY concur_temp_ind_2;+-- ON COMMIT DROP+BEGIN;+CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text)+ ON COMMIT PRESERVE ROWS;+INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar');+CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2);+-- Fails when running in a transaction+REINDEX INDEX CONCURRENTLY concur_temp_ind_3;+ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block+COMMIT;+-- REINDEX SCHEMA processes all temporary relations+CREATE TABLE reindex_temp_before AS+SELECT oid, relname, relfilenode, relkind, reltoastrelid+ FROM pg_class+ WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2');+SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset+REINDEX SCHEMA :temp_schema_name;+SELECT b.relname,+ b.relkind,+ CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'+ ELSE 'relfilenode has changed' END+ FROM reindex_temp_before b JOIN pg_class a ON b.oid = a.oid+ ORDER BY 1;+ relname | relkind | case +-------------------+---------+-------------------------+ concur_temp_ind_1 | i | relfilenode has changed+ concur_temp_ind_2 | i | relfilenode has changed+(2 rows)++DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before;
--
-- REINDEX SCHEMA
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sqlindex 3c0c1cdc5e..5bc9da440e 100644--- a/src/test/regress/sql/create_index.sql+++ b/src/test/regress/sql/create_index.sql@@ -501,6 +501,31 @@ VACUUM FULL concur_heap;
REINDEX TABLE concur_heap;
\d concur_heap
+-- Temporary tables with concurrent builds and on-commit actions+-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored.+-- PRESERVE ROWS, the default.+CREATE TEMP TABLE concur_temp (f1 int, f2 text)+ ON COMMIT PRESERVE ROWS;+INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');+CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);+DROP INDEX CONCURRENTLY concur_temp_ind;+DROP TABLE concur_temp;+-- ON COMMIT DROP+BEGIN;+CREATE TEMP TABLE concur_temp (f1 int, f2 text)+ ON COMMIT DROP;+INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');+-- Fails when running in a transaction.+CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);+COMMIT;+-- ON COMMIT DELETE ROWS+CREATE TEMP TABLE concur_temp (f1 int, f2 text)+ ON COMMIT DELETE ROWS;+INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');+CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);+DROP INDEX CONCURRENTLY concur_temp_ind;+DROP TABLE concur_temp;+
--
-- Try some concurrent index drops
--
@@ -972,6 +997,48 @@ SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
DROP TABLE concur_exprs_tab;
+-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored.+-- ON COMMIT PRESERVE ROWS, the default.+CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text)+ ON COMMIT PRESERVE ROWS;+INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar');+CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2);+REINDEX TABLE CONCURRENTLY concur_temp_tab_1;+REINDEX INDEX CONCURRENTLY concur_temp_ind_1;+-- Still fails in transaction blocks+BEGIN;+REINDEX INDEX CONCURRENTLY concur_temp_ind_1;+COMMIT;+-- ON COMMIT DELETE ROWS+CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text)+ ON COMMIT DELETE ROWS;+CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2);+REINDEX TABLE CONCURRENTLY concur_temp_tab_2;+REINDEX INDEX CONCURRENTLY concur_temp_ind_2;+-- ON COMMIT DROP+BEGIN;+CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text)+ ON COMMIT PRESERVE ROWS;+INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar');+CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2);+-- Fails when running in a transaction+REINDEX INDEX CONCURRENTLY concur_temp_ind_3;+COMMIT;+-- REINDEX SCHEMA processes all temporary relations+CREATE TABLE reindex_temp_before AS+SELECT oid, relname, relfilenode, relkind, reltoastrelid+ FROM pg_class+ WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2');+SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset+REINDEX SCHEMA :temp_schema_name;+SELECT b.relname,+ b.relkind,+ CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'+ ELSE 'relfilenode has changed' END+ FROM reindex_temp_before b JOIN pg_class a ON b.oid = a.oid+ ORDER BY 1;+DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before;+
--
-- REINDEX SCHEMA
--
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgmlindex 629a31ef79..ab362a0dc5 100644--- a/doc/src/sgml/ref/create_index.sgml+++ b/doc/src/sgml/ref/create_index.sgml@@ -129,6 +129,11 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
— see <xref linkend="sql-createindex-concurrently"
endterm="sql-createindex-concurrently-title"/>.
</para>
+ <para>+ For temporary tables, <command>CREATE INDEX</command> is always+ non-concurrent, as no other session can access them, and+ non-concurrent index creation is cheaper.+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgmlindex 2a8ca5bf68..0aedd71bd6 100644--- a/doc/src/sgml/ref/drop_index.sgml+++ b/doc/src/sgml/ref/drop_index.sgml@@ -58,6 +58,11 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r
performed within a transaction block, but
<command>DROP INDEX CONCURRENTLY</command> cannot.
</para>
+ <para>+ For temporary tables, <command>DROP INDEX</command> is always+ non-concurrent, as no other session can access them, and+ non-concurrent index drop is cheaper.+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgmlindex 5aa59d3b75..0cc19b86ee 100644--- a/doc/src/sgml/ref/reindex.sgml+++ b/doc/src/sgml/ref/reindex.sgml@@ -166,6 +166,11 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
— see <xref linkend="sql-reindex-concurrently"
endterm="sql-reindex-concurrently-title"/>.
</para>
+ <para>+ For temporary tables, <command>REINDEX</command> is always+ non-concurrent, as no other session can access them, and+ non-concurrent index creation is cheaper.+ </para>
</listitem>
</varlistentry>
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: michael@paquier.xyz, hlinnaka@iki.fi, andres@anarazel.de, tgl@sss.pgh.pa.us, rigger.manuel@gmail.com
Subject: Re: REINDEX CONCURRENTLY unexpectedly fails
In-Reply-To: <20200115013824.GA2243@paquier.xyz>
References: <20191115080706.GI1849@paquier.xyz> <20191120013649.2xfr2dnznyjgluch@alap3.anarazel.de> <20191120035408.GA4243@paquier.xyz> <20191212213709.neopqccvdo724eha@alap3.anarazel.de> <20191213034536.GD1942@paquier.xyz> <20191223015956.GB34339@paquier.xyz> <8f0bde7b-2d96-fb5c-cd0e-553694c1d376@iki.fi> <20200108081930.GF3413@paquier.xyz> <20200109030619.GC2251@paquier.xyz> <d62659d5-8860-075f-1e86-f17834f26495@iki.fi> <20200115013824.GA2243@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox