agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19723: CREATE INDEX racing with ALTER INDEX ATTACH PARTITION triggers unexpected internal error
4+ messages / 3 participants
[nested] [flat]
* BUG #19723: CREATE INDEX racing with ALTER INDEX ATTACH PARTITION triggers unexpected internal error
@ 2026-09-26 09:00 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-09-26 09:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 19723
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 19beta4
Operating system: Ubuntu 24.04
Description:
The following script:
echo "
CREATE TABLE t (a int, b int) PARTITION BY list (b);
CREATE TABLE tp1 PARTITION OF t FOR VALUES IN (1);
" | psql
for ((i=1;i<=100;i++)); do
echo "iteration $i"
echo "
DROP INDEX t_a_idx;
DROP INDEX t_a_idx2;
CREATE INDEX t_a_idx ON ONLY t (a);
CREATE INDEX tp1_a_idx ON tp1 (a);
" | psql
echo "ALTER INDEX t_a_idx ATTACH PARTITION tp1_a_idx;" | psql &
echo "CREATE INDEX t_a_idx2 ON t(a);" | psql
wait
grep 'ERROR: bogus pg_inherit row' server.log && break;
done
tirggers:
iteration 3
DROP INDEX
DROP INDEX
CREATE INDEX
CREATE INDEX
ERROR: bogus pg_inherit row: inhrelid 16400 inhparent 16399
ALTER INDEX
2026-09-26 04:36:22.653 EDT|user|regression|6ab78406.1ddce8|XX000 ERROR:
bogus pg_inherit row: inhrelid 16400 inhparent 16399
which is described as unexpected:
/*
* A pg_inherits row exists. If it's the same we
want, then we're
* good; if it differs, that amounts to a corrupt
catalog and
* should not happen.
*/
if (inhForm->inhparent != parentOid)
{
/* unexpected: we should not get called in
this case */
elog(ERROR, "bogus pg_inherit row: inhrelid
%u inhparent %u",
inhForm->inhrelid,
inhForm->inhparent);
}
Reproduced starting from 8b08f7d48.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19723: CREATE INDEX racing with ALTER INDEX ATTACH PARTITION triggers unexpected internal error
@ 2026-09-26 14:25 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Ayush Tiwari @ 2026-09-26 14:25 UTC (permalink / raw)
To: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Hi,
On Sat, 26 Sept 2026 at 18:51, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19723
> Logged by: Alexander Lakhin
> Email address: exclusion@gmail.com
> PostgreSQL version: 19beta4
> Operating system: Ubuntu 24.04
> Description:
>
> The following script:
> echo "
> CREATE TABLE t (a int, b int) PARTITION BY list (b);
> CREATE TABLE tp1 PARTITION OF t FOR VALUES IN (1);
> " | psql
>
> for ((i=1;i<=100;i++)); do
> echo "iteration $i"
> echo "
> DROP INDEX t_a_idx;
> DROP INDEX t_a_idx2;
> CREATE INDEX t_a_idx ON ONLY t (a);
> CREATE INDEX tp1_a_idx ON tp1 (a);
> " | psql
>
> echo "ALTER INDEX t_a_idx ATTACH PARTITION tp1_a_idx;" | psql &
> echo "CREATE INDEX t_a_idx2 ON t(a);" | psql
> wait
> grep 'ERROR: bogus pg_inherit row' server.log && break;
> done
>
> tirggers:
> iteration 3
> DROP INDEX
> DROP INDEX
> CREATE INDEX
> CREATE INDEX
> ERROR: bogus pg_inherit row: inhrelid 16400 inhparent 16399
> ALTER INDEX
> 2026-09-26 04:36:22.653 EDT|user|regression|6ab78406.1ddce8|XX000 ERROR:
> bogus pg_inherit row: inhrelid 16400 inhparent 16399
>
> which is described as unexpected:
> /*
> * A pg_inherits row exists. If it's the same we
> want, then we're
> * good; if it differs, that amounts to a corrupt
> catalog and
> * should not happen.
> */
> if (inhForm->inhparent != parentOid)
> {
> /* unexpected: we should not get called in
> this case */
> elog(ERROR, "bogus pg_inherit row: inhrelid
> %u inhparent %u",
> inhForm->inhrelid,
> inhForm->inhparent);
> }
>
> Reproduced starting from 8b08f7d48.
Thanks for the report with repro and bisect.
I think I see how this happens. DefineIndex() calls has_superclass()
before locking the child index, although its comment says the caller
*must hold that lock*. If ALTER INDEX ... ATTACH hasn't committed yet,
CREATE INDEX sees the child as unattached, waits in index_open(), and
then tries to attach it to its new parent after ATTACH commits.
Would it make sense to open the index before calling has_superclass()?
Then the check would see the attachment after the wait and skip that
index.
The below simple diff fixed the issue for me:
---
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 5a0312fe772..561dd124e2c 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1453,11 +1453,14 @@ DefineIndex(ParseState *pstate,
Relation cldidx;
IndexInfo *cldIdxInfo;
+ cldidx = index_open(cldidxid, lockmode);
/* this index is already partition of another one */
if (has_superclass(cldidxid))
+ {
+ index_close(cldidx, lockmode);
continue;
+ }
- cldidx = index_open(cldidxid, lockmode);
cldIdxInfo = BuildIndexInfo(cldidx);
Regards,
Ayush
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19723: CREATE INDEX racing with ALTER INDEX ATTACH PARTITION triggers unexpected internal error
@ 2026-09-28 04:44 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ayush Tiwari @ 2026-09-28 04:44 UTC (permalink / raw)
To: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org, Álvaro Herrera <alvherre@kurilemu.de>
Hi,
On Sat, 26 Sept 2026 at 19:55, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
> On Sat, 26 Sept 2026 at 18:51, PG Bug reporting form
> <noreply@postgresql.org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 19723
> > Logged by: Alexander Lakhin
> > Email address: exclusion@gmail.com
> > PostgreSQL version: 19beta4
> > Operating system: Ubuntu 24.04
> > Description:
> >
> > The following script:
> > echo "
> > CREATE TABLE t (a int, b int) PARTITION BY list (b);
> > CREATE TABLE tp1 PARTITION OF t FOR VALUES IN (1);
> > " | psql
> >
> > for ((i=1;i<=100;i++)); do
> > echo "iteration $i"
> > echo "
> > DROP INDEX t_a_idx;
> > DROP INDEX t_a_idx2;
> > CREATE INDEX t_a_idx ON ONLY t (a);
> > CREATE INDEX tp1_a_idx ON tp1 (a);
> > " | psql
> >
> > echo "ALTER INDEX t_a_idx ATTACH PARTITION tp1_a_idx;" | psql &
> > echo "CREATE INDEX t_a_idx2 ON t(a);" | psql
> > wait
> > grep 'ERROR: bogus pg_inherit row' server.log && break;
> > done
> >
> > tirggers:
> > iteration 3
> > DROP INDEX
> > DROP INDEX
> > CREATE INDEX
> > CREATE INDEX
> > ERROR: bogus pg_inherit row: inhrelid 16400 inhparent 16399
> > ALTER INDEX
> > 2026-09-26 04:36:22.653 EDT|user|regression|6ab78406.1ddce8|XX000 ERROR:
> > bogus pg_inherit row: inhrelid 16400 inhparent 16399
> >
> > which is described as unexpected:
> > /*
> > * A pg_inherits row exists. If it's the same we
> > want, then we're
> > * good; if it differs, that amounts to a corrupt
> > catalog and
> > * should not happen.
> > */
> > if (inhForm->inhparent != parentOid)
> > {
> > /* unexpected: we should not get called in
> > this case */
> > elog(ERROR, "bogus pg_inherit row: inhrelid
> > %u inhparent %u",
> > inhForm->inhrelid,
> > inhForm->inhparent);
> > }
> >
> > Reproduced starting from 8b08f7d48.
>
> Thanks for the report with repro and bisect.
>
> I think I see how this happens. DefineIndex() calls has_superclass()
> before locking the child index, although its comment says the caller
> *must hold that lock*. If ALTER INDEX ... ATTACH hasn't committed yet,
> CREATE INDEX sees the child as unattached, waits in index_open(), and
> then tries to attach it to its new parent after ATTACH commits.
>
> Would it make sense to open the index before calling has_superclass()?
> Then the check would see the attachment after the wait and skip that
> index.
>
> The below simple diff fixed the issue for me:
>
> ---
> diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
> index 5a0312fe772..561dd124e2c 100644
> --- a/src/backend/commands/indexcmds.c
> +++ b/src/backend/commands/indexcmds.c
> @@ -1453,11 +1453,14 @@ DefineIndex(ParseState *pstate,
> Relation cldidx;
> IndexInfo *cldIdxInfo;
>
> + cldidx = index_open(cldidxid, lockmode);
> /* this index is already partition of another one */
> if (has_superclass(cldidxid))
> + {
> + index_close(cldidx, lockmode);
> continue;
> + }
>
> - cldidx = index_open(cldidxid, lockmode);
> cldIdxInfo = BuildIndexInfo(cldidx);
Post some more testing, attaching patch file with above diff.
Regards,
Ayush
Attachments:
[application/octet-stream] v1-0001-Lock-child-index-before-checking-its-parent.patch (1.7K, ../../CAJTYsWV3K0vN1c=4ZEybDtcMKRLk2qatFvG2aQRQ1ZXVWXMn3Q@mail.gmail.com/2-v1-0001-Lock-child-index-before-checking-its-parent.patch)
download | inline diff:
From 743ad3a5a9c2f4c01a2c1a2e194f01610d608aa3 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Date: Mon, 28 Sep 2026 09:53:34 +0530
Subject: [PATCH v1] Lock child index before checking its partition parent
When creating an index on a partitioned table, DefineIndex() may
reuse an existing index on a partition. It calls has_superclass()
before locking the child index, though has_superclass() requires it.
If ALTER INDEX ... ATTACH PARTITION commits while CREATE INDEX
waits for the child index lock, the earlier check becomes stale.
CREATE INDEX can then try to attach the child index to a different
parent and report "bogus pg_inherit row".
Lock the child index before calling has_superclass(), so the check
sees the attachment after the lock wait. Close the index when it is
already attached.
Reported-by: Alexander Lakhin <exclusion@gmail.com>
Bug: #19723
---
src/backend/commands/indexcmds.c | 5 ++++-
1 file changed, 4 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 5a0312fe772..561dd124e2c 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1453,11 +1453,14 @@ DefineIndex(ParseState *pstate,
Relation cldidx;
IndexInfo *cldIdxInfo;
+ cldidx = index_open(cldidxid, lockmode);
/* this index is already partition of another one */
if (has_superclass(cldidxid))
+ {
+ index_close(cldidx, lockmode);
continue;
+ }
- cldidx = index_open(cldidxid, lockmode);
cldIdxInfo = BuildIndexInfo(cldidx);
if (CompareIndexInfo(cldIdxInfo, indexInfo,
cldidx->rd_indcollation,
--
2.34.1
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19723: CREATE INDEX racing with ALTER INDEX ATTACH PARTITION triggers unexpected internal error
@ 2026-09-28 14:00 Rafia Sabih <rafia.pghackers@gmail.com>
parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rafia Sabih @ 2026-09-28 14:00 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org, Álvaro Herrera <alvherre@kurilemu.de>
On Mon, 28 Sept 2026 at 06:44, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
> Hi,
>
> On Sat, 26 Sept 2026 at 19:55, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
> wrote:
> > On Sat, 26 Sept 2026 at 18:51, PG Bug reporting form
> > <noreply@postgresql.org> wrote:
> > >
> > > The following bug has been logged on the website:
> > >
> > > Bug reference: 19723
> > > Logged by: Alexander Lakhin
> > > Email address: exclusion@gmail.com
> > > PostgreSQL version: 19beta4
> > > Operating system: Ubuntu 24.04
> > > Description:
> > >
> > > The following script:
> > > echo "
> > > CREATE TABLE t (a int, b int) PARTITION BY list (b);
> > > CREATE TABLE tp1 PARTITION OF t FOR VALUES IN (1);
> > > " | psql
> > >
> > > for ((i=1;i<=100;i++)); do
> > > echo "iteration $i"
> > > echo "
> > > DROP INDEX t_a_idx;
> > > DROP INDEX t_a_idx2;
> > > CREATE INDEX t_a_idx ON ONLY t (a);
> > > CREATE INDEX tp1_a_idx ON tp1 (a);
> > > " | psql
> > >
> > > echo "ALTER INDEX t_a_idx ATTACH PARTITION tp1_a_idx;" | psql &
> > > echo "CREATE INDEX t_a_idx2 ON t(a);" | psql
> > > wait
> > > grep 'ERROR: bogus pg_inherit row' server.log && break;
> > > done
> > >
> > > tirggers:
> > > iteration 3
> > > DROP INDEX
> > > DROP INDEX
> > > CREATE INDEX
> > > CREATE INDEX
> > > ERROR: bogus pg_inherit row: inhrelid 16400 inhparent 16399
> > > ALTER INDEX
> > > 2026-09-26 04:36:22.653 EDT|user|regression|6ab78406.1ddce8|XX000
> ERROR:
> > > bogus pg_inherit row: inhrelid 16400 inhparent 16399
> > >
> > > which is described as unexpected:
> > > /*
> > > * A pg_inherits row exists. If it's the same
> we
> > > want, then we're
> > > * good; if it differs, that amounts to a
> corrupt
> > > catalog and
> > > * should not happen.
> > > */
> > > if (inhForm->inhparent != parentOid)
> > > {
> > > /* unexpected: we should not get
> called in
> > > this case */
> > > elog(ERROR, "bogus pg_inherit row:
> inhrelid
> > > %u inhparent %u",
> > > inhForm->inhrelid,
> > > inhForm->inhparent);
> > > }
> > >
> > > Reproduced starting from 8b08f7d48.
> >
> > Thanks for the report with repro and bisect.
> >
> > I think I see how this happens. DefineIndex() calls has_superclass()
> > before locking the child index, although its comment says the caller
> > *must hold that lock*. If ALTER INDEX ... ATTACH hasn't committed yet,
> > CREATE INDEX sees the child as unattached, waits in index_open(), and
> > then tries to attach it to its new parent after ATTACH commits.
> >
> > Would it make sense to open the index before calling has_superclass()?
> > Then the check would see the attachment after the wait and skip that
> > index.
> >
> The below simple diff fixed the issue for me:
> >
> > ---
> > diff --git a/src/backend/commands/indexcmds.c
> b/src/backend/commands/indexcmds.c
> > index 5a0312fe772..561dd124e2c 100644
> > --- a/src/backend/commands/indexcmds.c
> > +++ b/src/backend/commands/indexcmds.c
> > @@ -1453,11 +1453,14 @@ DefineIndex(ParseState *pstate,
> > Relation cldidx;
> > IndexInfo *cldIdxInfo;
> >
> > + cldidx = index_open(cldidxid, lockmode);
> > /* this index is already partition of another one */
> > if (has_superclass(cldidxid))
> > + {
> > + index_close(cldidx, lockmode);
> > continue;
> > + }
> >
> > - cldidx = index_open(cldidxid, lockmode);
> > cldIdxInfo = BuildIndexInfo(cldidx);
>
Post some more testing, attaching patch file with above diff.
>
> I went through this patch and the fix looks good to me. However I see one
problem with this in the following scenario,
s1: BEGIN; ALTER INDEX c_a_idx RENAME TO c_renamed; -- c_a_idx attached
s2: CREATE INDEX pi_new ON p (a); -- now waits (used to complete)
s1: INSERT INTO c VALUES (1);
=> ERROR: deadlock detected
Unpatched, both sessions complete.
So basically it is occurring because of opening the index first including
ones that are
already attached. Before the patch, those were skipped without taking any
lock.
That lock conflicts with locks other sessions can hold on an attached
index without conflicting on the partition itself, so CREATE INDEX on
the parent can now block, or deadlock, where it didn't before. What I
suggest is
we could keep the unlocked check as a fast path and check again only after
taking the lock:
/* this index is already partition of another one */
if (has_superclass(cldidxid))
continue;
cldidx = index_open(cldidxid, lockmode);
/* recheck, in case it was attached while we waited for the lock */
if (has_superclass(cldidxid))
{
index_close(cldidx, lockmode);
continue;
}
Another remark is to add an isolation test for this.
I have attached a patch with these additions.
--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH
Attachments:
[application/octet-stream] 0001-Recheck-partition-index-attachment-after-locking-it.patch (6.1K, ../../CA+FpmFf9Pf5gJofOiED3wU3006v_8XwM8WNs_f-Q3eQYKvZNNA@mail.gmail.com/3-0001-Recheck-partition-index-attachment-after-locking-it.patch)
download | inline diff:
From c54b0d27c3784a1e2e56c91a871ee76e39f0b4c2 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Date: Mon, 28 Sep 2026 15:29:05 +0200
Subject: [PATCH] Recheck partition index attachment after locking it
When creating an index on a partitioned table, DefineIndex() may reuse
an existing index on a partition that is not yet attached to a parent.
It checked that with has_superclass() before locking the child index.
If ALTER INDEX ... ATTACH PARTITION committed while CREATE INDEX waited
for that lock, CREATE INDEX then tried to attach the index to a second
parent and failed with "bogus pg_inherit row".
Repeat the check after acquiring the lock. Keep the unlocked check as
well, so that already-attached indexes are skipped without locking
them; otherwise CREATE INDEX could block, or deadlock, on locks held on
indexes it has no use for.
Add an isolation test covering both cases.
Reported-by: Alexander Lakhin <exclusion@gmail.com>
Author: Ayush Tiwari
Reviewed-by: Rafia Sabih
Bug: #19723
Backpatch-through: 14
---
src/backend/commands/indexcmds.c | 16 +++++++
.../expected/partition-index-attach.out | 45 ++++++++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../specs/partition-index-attach.spec | 46 +++++++++++++++++++
4 files changed, 108 insertions(+)
create mode 100644 src/test/isolation/expected/partition-index-attach.out
create mode 100644 src/test/isolation/specs/partition-index-attach.spec
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 5a0312fe772..c5f81d3e1f0 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1458,6 +1458,22 @@ DefineIndex(ParseState *pstate,
continue;
cldidx = index_open(cldidxid, lockmode);
+
+ /*
+ * Recheck now that we hold the lock, in case the index
+ * was concurrently attached to some other parent while we
+ * waited for it. The unlocked check above cannot give a
+ * stale true answer, because the lock we hold on the
+ * partition prevents detaching it or dropping the parent
+ * index; so it only serves to avoid locking indexes we're
+ * going to skip anyway.
+ */
+ if (has_superclass(cldidxid))
+ {
+ index_close(cldidx, lockmode);
+ continue;
+ }
+
cldIdxInfo = BuildIndexInfo(cldidx);
if (CompareIndexInfo(cldIdxInfo, indexInfo,
cldidx->rd_indcollation,
diff --git a/src/test/isolation/expected/partition-index-attach.out b/src/test/isolation/expected/partition-index-attach.out
new file mode 100644
index 00000000000..3bb6ad3054b
--- /dev/null
+++ b/src/test/isolation/expected/partition-index-attach.out
@@ -0,0 +1,45 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1b s1attach s2create s1c s2check
+step s1b: BEGIN;
+step s1attach: ALTER INDEX pia_old ATTACH PARTITION pia_1_a;
+step s2create: CREATE INDEX pia_new ON pia (a); <waiting ...>
+step s1c: COMMIT;
+step s2create: <... completed>
+step s2check:
+ SELECT c.relname AS index, p.relname AS parent
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhrelid
+ JOIN pg_class p ON p.oid = i.inhparent
+ WHERE p.relname IN ('pia_old', 'pia_new')
+ ORDER BY 1;
+
+index |parent
+-----------+-------
+pia_1_a |pia_old
+pia_1_a_idx|pia_new
+pia_2_a |pia_old
+pia_2_a_idx|pia_new
+(4 rows)
+
+
+starting permutation: s1b s1rename s2create s1c s2check
+step s1b: BEGIN;
+step s1rename: ALTER INDEX pia_2_a RENAME TO pia_2_renamed;
+step s2create: CREATE INDEX pia_new ON pia (a);
+step s1c: COMMIT;
+step s2check:
+ SELECT c.relname AS index, p.relname AS parent
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhrelid
+ JOIN pg_class p ON p.oid = i.inhparent
+ WHERE p.relname IN ('pia_old', 'pia_new')
+ ORDER BY 1;
+
+index |parent
+-------------+-------
+pia_1_a |pia_new
+pia_2_a_idx |pia_new
+pia_2_renamed|pia_old
+(3 rows)
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 8470d50d2bc..285184413a6 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -113,6 +113,7 @@ test: predicate-gist
test: predicate-gin
test: partition-concurrent-attach
test: partition-drop-index-locking
+test: partition-index-attach
test: partition-key-update-1
test: partition-key-update-2
test: partition-key-update-3
diff --git a/src/test/isolation/specs/partition-index-attach.spec b/src/test/isolation/specs/partition-index-attach.spec
new file mode 100644
index 00000000000..093226b736b
--- /dev/null
+++ b/src/test/isolation/specs/partition-index-attach.spec
@@ -0,0 +1,46 @@
+# Test CREATE INDEX on a partitioned table concurrently with
+# ALTER INDEX ... ATTACH PARTITION on one of its partitions' indexes.
+#
+# CREATE INDEX must notice that the partition's index was attached to
+# another parent while it waited for the lock on it, and must not wait for
+# locks on partition indexes that were already attached.
+
+setup
+{
+ CREATE TABLE pia (a int) PARTITION BY RANGE (a);
+ CREATE TABLE pia_1 PARTITION OF pia FOR VALUES FROM (0) TO (10);
+ CREATE TABLE pia_2 PARTITION OF pia FOR VALUES FROM (10) TO (20);
+ CREATE INDEX pia_old ON ONLY pia (a);
+ CREATE INDEX pia_1_a ON pia_1 (a);
+ CREATE INDEX pia_2_a ON pia_2 (a);
+ ALTER INDEX pia_old ATTACH PARTITION pia_2_a;
+}
+
+teardown
+{
+ DROP TABLE pia;
+}
+
+session s1
+step s1b { BEGIN; }
+step s1attach { ALTER INDEX pia_old ATTACH PARTITION pia_1_a; }
+step s1rename { ALTER INDEX pia_2_a RENAME TO pia_2_renamed; }
+step s1c { COMMIT; }
+
+session s2
+step s2create { CREATE INDEX pia_new ON pia (a); }
+step s2check {
+ SELECT c.relname AS index, p.relname AS parent
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhrelid
+ JOIN pg_class p ON p.oid = i.inhparent
+ WHERE p.relname IN ('pia_old', 'pia_new')
+ ORDER BY 1;
+}
+
+# CREATE INDEX waits for the ATTACH, then must build a new index on pia_1
+# rather than trying to reuse pia_1_a.
+permutation s1b s1attach s2create s1c s2check
+
+# CREATE INDEX must not wait for the lock on an already-attached index.
+permutation s1b s1rename s2create s1c s2check
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-09-28 14:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-26 09:00 BUG #19723: CREATE INDEX racing with ALTER INDEX ATTACH PARTITION triggers unexpected internal error PG Bug reporting form <noreply@postgresql.org>
2026-09-26 14:25 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-28 04:44 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-28 14:00 ` Rafia Sabih <rafia.pghackers@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox