From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: pgsql-bugs@lists.postgresql.org
Subject: Assertion on create index concurrently
Date: Sat, 26 Jun 2021 21:48:45 +0300
Message-ID: <9b905019-5297-7372-0ad2-e1a4bb66a719@postgrespro.ru> (raw)
I found one annoying assertion in the CREATE INDEX CONCURRENTLY
operation. It will happen if you add into WHERE part of CREATE INDEX
CONCURRENTLY some function that will make transactional update (see an
example in the attached patch).
It looks like a hack, but it lead to assertion and restart of the
instance, that isn't good.
I suggest to perform error, not assertion in this case (see the patch).
This problem affects PG 9.6 - 13. The master branch was fixed 9 months
ago by commit 83158f7, that made this operation transactional, but still.
--
regards,
Andrey Lepikhov
Postgres Professional
Attachments:0001-Use-more-safe-procedure-to-check-a-non-transactional.patch (text/plain, 3.5K) download
From 2d658eb9830c78e875b08daa86c608d5f327b1f3 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Sat, 26 Jun 2021 19:54:25 +0300
Subject: [PATCH] Use more safe procedure to check a non-transactional
CONCURRENT operation on transactional updates.
---
src/backend/catalog/index.c | 8 ++++++--
src/test/regress/expected/create_index.out | 17 +++++++++++++++++
src/test/regress/sql/create_index.sql | 13 +++++++++++++
3 files changed, 36 insertions(+), 2 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex b9eed8ec38..ca91a7c74f 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -3395,8 +3395,12 @@ index_set_state_flags(Oid indexId, IndexStateFlagsAction action)
HeapTuple indexTuple;
Form_pg_index indexForm;
- /* Assert that current xact hasn't done any transactional updates */- Assert(GetTopTransactionIdIfAny() == InvalidTransactionId);+ /* Check that current xact hasn't done any transactional updates */+ if (GetTopTransactionIdIfAny() != InvalidTransactionId)+ ereport(ERROR,+ (errcode(ERRCODE_ACTIVE_SQL_TRANSACTION),+ errmsg("Some transactional updates were made during a concurrent index operation")));+
/* Open pg_index and fetch a writable copy of the index's tuple */
pg_index = table_open(IndexRelationId, RowExclusiveLock);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.outindex 8e11c089b1..93103334d8 100644--- a/src/test/regress/expected/create_index.out+++ b/src/test/regress/expected/create_index.out@@ -1393,6 +1393,23 @@ BEGIN;
CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
COMMIT;
+CREATE FUNCTION predicate() RETURNS bool IMMUTABLE LANGUAGE plpgsql AS $$+BEGIN+ EXECUTE 'SELECT txid_current()';+ RETURN true;+END; $$;+-- You can't do any transactional updates during concurrent index build.+CREATE INDEX CONCURRENTLY concur_index8 ON concur_heap (f1) WHERE predicate();+ERROR: Some transactional updates were made during a concurrent index operation+-- Index exists but invalid. It must be actualized after REINDEX TABLE command.+SELECT relname,indisvalid FROM pg_class, pg_index+ WHERE relname = 'concur_index8' AND oid = indexrelid;+ relname | indisvalid +---------------+------------+ concur_index8 | f+(1 row)++DROP INDEX concur_index8;
-- But you can do a regular index build in a transaction
BEGIN;
CREATE INDEX std_index on concur_heap(f2);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sqlindex c0392f052d..71779e3dce 100644--- a/src/test/regress/sql/create_index.sql+++ b/src/test/regress/sql/create_index.sql@@ -487,6 +487,19 @@ BEGIN;
CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
COMMIT;
+CREATE FUNCTION predicate() RETURNS bool IMMUTABLE LANGUAGE plpgsql AS $$+BEGIN+ EXECUTE 'SELECT txid_current()';+ RETURN true;+END; $$;++-- You can't do any transactional updates during concurrent index build.+CREATE INDEX CONCURRENTLY concur_index8 ON concur_heap (f1) WHERE predicate();+-- Index exists but invalid. It must be actualized after REINDEX TABLE command.+SELECT relname,indisvalid FROM pg_class, pg_index+ WHERE relname = 'concur_index8' AND oid = indexrelid;+DROP INDEX concur_index8;+
-- But you can do a regular index build in a transaction
BEGIN;
CREATE INDEX std_index on concur_heap(f2);
--
2.31.1
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.lepikhov@postgrespro.ru, pgsql-bugs@lists.postgresql.org
Subject: Re: Assertion on create index concurrently
In-Reply-To: <9b905019-5297-7372-0ad2-e1a4bb66a719@postgrespro.ru>
* 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