pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
13+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2020-09-01 afc7e0ad55 7067ba1b4b f00f5e0c11 15dad57765 Raise error on concurrent drop of partitioned index
* BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
@ 2020-08-26 20:11 PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 13+ messages in thread
From: PG Bug reporting form @ 2020-08-26 20:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jan.mussler@zalando.de
The following bug has been logged on the website:
Bug reference: 16594
Logged by: Jan Mussler
Email address: jan.mussler@zalando.de
PostgreSQL version: 12.3
Operating system: Ubuntu
Description:
Hello everyone,
earlier today we observed a user trying to drop an index concurrently
yielding the following error message:
DROP INDEX CONCURRENTLY IF EXISTS some_idx;
ERROR: DROP INDEX CONCURRENTLY must be first action in transaction
Continuing down this road the user was asked double check what was going on
and try with a rollback first, yielding the following example output:
=> rollback;
WARNING: there is no transaction in progress
ROLLBACK
=> DROP INDEX CONCURRENTLY IF EXISTS some_idx;
ERROR: DROP INDEX CONCURRENTLY must be first action in transaction
=>
After more digging the user elaborated that this is in fact a partitioned
table.
While a person with deeper knowledge may understand that there is more going
on when dropping an index on a partitioned table from a normal user
perspective this error message is confusing.
We have some internal discussion if this is a bug or lack of documentation.
I am siding though with bug believing the otherwise excellent Postgres error
messages can be more helpful here.
Open for feedback and we can also see to contribute to the docs if needed.
-- Jan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
@ 2020-08-27 01:00 ` Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2020-08-27 01:00 UTC (permalink / raw)
To: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On Wed, Aug 26, 2020 at 08:11:16PM +0000, PG Bug reporting form wrote:
> After more digging the user elaborated that this is in fact a partitioned
> table.
This error happens in index_drop() as of index.c, where we need to
assume that the index drop is the first action happening in a
transaction. Now comes the tricky problem: when you drop the index of
a partitioned table, the dependency machinery needs to find the set of
dependent objects, and this assigns a transaction ID for the list of
partitions.
Still, it seems to me that we are far from being able to support fully
this operation on partitioned indexes, and we need to do more than
what we have now if we want this feature. So I think that the
concurrent drop of partitioned indexes should respect the following
flow:
1) Drop all the dependencies for the partition tree in a single,
first, transaction.
2) Drop each index after that, in its own two sets of transactions for
each entry, one to clear indisvalid and a second one to do the actual
drop.
The whole operation should make sure that we still have a fully
consistent state in the catalogs for any transaction, so as we have no
risk of finishing with a semi-broken partition tree if the thing fails
while processing for a reason or another. As long as 1) is not
implemented, I don't think that this can really be safe, still this
requires careful thinking in the way we gather the list of indexes to
drop beforehand.
The error message is really confusing though, so for now I would
recommend to just drop an error if trying the operation on a
partitioned table, and we also do that now for CREATE INDEX
CONCURRENTLY.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
@ 2020-08-27 17:25 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2020-08-27 17:25 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On 2020-Aug-27, Michael Paquier wrote:
> The error message is really confusing though, so for now I would
> recommend to just drop an error if trying the operation on a
> partitioned table, and we also do that now for CREATE INDEX
> CONCURRENTLY.
Yeah, let's throw an error if the table is partitioned. My bug -- I'll
go fix it now.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-08-27 19:22 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:51 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Jan Mußler <jan.mussler@zalando.de>
2020-08-27 23:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 13+ messages in thread
From: Alvaro Herrera @ 2020-08-27 19:22 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On 2020-Aug-27, Alvaro Herrera wrote:
> On 2020-Aug-27, Michael Paquier wrote:
>
> > The error message is really confusing though, so for now I would
> > recommend to just drop an error if trying the operation on a
> > partitioned table, and we also do that now for CREATE INDEX
> > CONCURRENTLY.
>
> Yeah, let's throw an error if the table is partitioned. My bug -- I'll
> go fix it now.
... as attached.
I first tried to add a hack directly in index_drop, but that doesn't
really work because there's no way to tell whether the partitioned index
is going to be dropped first or the index partition -- as that code runs
after the dependency tree has been walked. The condition has to be
checked before starting the object-drop code proper.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-Raise-error-on-concurrent-drop-of-partitioned-index.patch (text/x-diff, 0B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-08-27 19:51 ` Jan Mußler <jan.mussler@zalando.de>
1 sibling, 0 replies; 13+ messages in thread
From: Jan Mußler @ 2020-08-27 19:51 UTC (permalink / raw)
To: ; +Cc: pgsql-bugs@lists.postgresql.org
Hi Alvaro,
thank you for looking into this on short notice. Looks better now with an
error messaging hinting at the problem.
-- Jan
Am Do., 27. Aug. 2020 um 21:22 Uhr schrieb Alvaro Herrera <
alvherre@2ndquadrant.com>:
> On 2020-Aug-27, Alvaro Herrera wrote:
>
> > On 2020-Aug-27, Michael Paquier wrote:
> >
> > > The error message is really confusing though, so for now I would
> > > recommend to just drop an error if trying the operation on a
> > > partitioned table, and we also do that now for CREATE INDEX
> > > CONCURRENTLY.
> >
> > Yeah, let's throw an error if the table is partitioned. My bug -- I'll
> > go fix it now.
>
> ... as attached.
>
> I first tried to add a hack directly in index_drop, but that doesn't
> really work because there's no way to tell whether the partitioned index
> is going to be dropped first or the index partition -- as that code runs
> after the dependency tree has been walked. The condition has to be
> checked before starting the object-drop code proper.
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-08-27 23:22 ` Michael Paquier <michael@paquier.xyz>
2020-08-29 01:34 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
1 sibling, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2020-08-27 23:22 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On Thu, Aug 27, 2020 at 03:22:18PM -0400, Alvaro Herrera wrote:
> I first tried to add a hack directly in index_drop, but that doesn't
> really work because there's no way to tell whether the partitioned index
> is going to be dropped first or the index partition -- as that code runs
> after the dependency tree has been walked. The condition has to be
> checked before starting the object-drop code proper.
Yes, adding that to RemoveRelations() makes sense. Thanks for the
patch.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 23:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
@ 2020-08-29 01:34 ` Michael Paquier <michael@paquier.xyz>
2020-08-31 19:48 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 01:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 2 replies; 13+ messages in thread
From: Michael Paquier @ 2020-08-29 01:34 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On Fri, Aug 28, 2020 at 08:22:42AM +0900, Michael Paquier wrote:
> Yes, adding that to RemoveRelations() makes sense. Thanks for the
> patch.
I got some room to test the patch, and the place of the check looks
good to me. I think that I would move the new check before we set
PERFORM_DELETION_CONCURRENTLY for non-temporary relations though, as a
partition tree can be temporary as long as all its members are
temporary.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 23:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-29 01:34 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
@ 2020-08-31 19:48 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 13+ messages in thread
From: Alvaro Herrera @ 2020-08-31 19:48 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On 2020-Aug-29, Michael Paquier wrote:
> On Fri, Aug 28, 2020 at 08:22:42AM +0900, Michael Paquier wrote:
> > Yes, adding that to RemoveRelations() makes sense. Thanks for the
> > patch.
>
> I got some room to test the patch, and the place of the check looks
> good to me. I think that I would move the new check before we set
> PERFORM_DELETION_CONCURRENTLY for non-temporary relations though, as a
> partition tree can be temporary as long as all its members are
> temporary.
Good point. Will push shortly with that change.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 23:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-29 01:34 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
@ 2020-09-01 01:25 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 01:30 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 02:58 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
1 sibling, 2 replies; 13+ messages in thread
From: Alvaro Herrera @ 2020-09-01 01:25 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On 2020-Aug-29, Michael Paquier wrote:
> On Fri, Aug 28, 2020 at 08:22:42AM +0900, Michael Paquier wrote:
> > Yes, adding that to RemoveRelations() makes sense. Thanks for the
> > patch.
>
> I got some room to test the patch, and the place of the check looks
> good to me. I think that I would move the new check before we set
> PERFORM_DELETION_CONCURRENTLY for non-temporary relations though, as a
> partition tree can be temporary as long as all its members are
> temporary.
Actually I think you're wrong; if I put it before the check, then if I
do "drop index concurrently some_temp_partitioned_index" then it would
fail; but if I put it after the check, then it does a normal
non-concurrent index and it works. I'm not sure it's necessary to break
a case that otherwise works ...
(But for that to work I need to test the flag in the bitmask rather than
the option in the command, as in the attached).
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
v2-0001-Raise-error-on-concurrent-drop-of-partitioned-ind.patch (text/x-diff, 0B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 23:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-29 01:34 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-09-01 01:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-09-01 01:30 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 13+ messages in thread
From: Alvaro Herrera @ 2020-09-01 01:30 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
Oh, and this:
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
docs.patch (text/x-diff, 1.1K) download
diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml
index 0aedd71bd6..85cf23bca2 100644
--- a/doc/src/sgml/ref/drop_index.sgml
+++ b/doc/src/sgml/ref/drop_index.sgml
@@ -52,16 +52,18 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r
<para>
There are several caveats to be aware of when using this option.
Only one index name can be specified, and the <literal>CASCADE</literal> option
is not supported. (Thus, an index that supports a <literal>UNIQUE</literal> or
<literal>PRIMARY KEY</literal> constraint cannot be dropped this way.)
Also, regular <command>DROP INDEX</command> commands can be
performed within a transaction block, but
<command>DROP INDEX CONCURRENTLY</command> cannot.
+ Lastly, indexes on partitioned tables cannot be dropped using this
+ option.
</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>
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 23:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-29 01:34 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-09-01 01:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-09-01 02:58 ` Michael Paquier <michael@paquier.xyz>
2020-09-01 17:44 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2020-09-01 02:58 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On Mon, Aug 31, 2020 at 09:25:53PM -0400, Alvaro Herrera wrote:
> Actually I think you're wrong; if I put it before the check, then if I
> do "drop index concurrently some_temp_partitioned_index" then it would
> fail; but if I put it after the check, then it does a normal
> non-concurrent index and it works. I'm not sure it's necessary to break
> a case that otherwise works ...
Hmm. Right. I agree that it would be better to not break that case.
And it means that there is a gap in the regression tests here, so I'd
like to add a test. Please see the attached to achieve that, which
includes your own code changes and the doc parts (I didn't see a point
in changing the new sentence for temporary relations as the follow-up
<para> mentions that).
> (But for that to work I need to test the flag in the bitmask rather than
> the option in the command, as in the attached).
Make sense.
--
Michael
Attachments:
reindex-drop-part-michael.patch (text/x-diff, 4.5K) download
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d2b15a3387..3e57c7f9e1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1373,6 +1373,17 @@ RemoveRelations(DropStmt *drop)
flags |= PERFORM_DELETION_CONCURRENTLY;
}
+ /*
+ * Concurrent index drop cannot be used with partitioned indexes,
+ * either.
+ */
+ if ((flags & PERFORM_DELETION_CONCURRENTLY) != 0 &&
+ get_rel_relkind(relOid) == RELKIND_PARTITIONED_INDEX)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot drop partitioned index \"%s\" concurrently",
+ rel->relname)));
+
/* OK, we're ready to delete this one */
obj.classId = RelationRelationId;
obj.objectId = relOid;
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index f78865ef81..7e78a07af8 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -174,6 +174,8 @@ create table idxpart1 partition of idxpart for values from (0) to (10);
drop index idxpart1_a_idx; -- no way
ERROR: cannot drop index idxpart1_a_idx because index idxpart_a_idx requires it
HINT: You can drop index idxpart_a_idx instead.
+drop index concurrently idxpart_a_idx; -- unsupported
+ERROR: cannot drop partitioned index "idxpart_a_idx" concurrently
drop index idxpart_a_idx; -- both indexes go away
select relname, relkind from pg_class
where relname like 'idxpart%' order by relname;
@@ -194,6 +196,24 @@ select relname, relkind from pg_class
(2 rows)
drop table idxpart;
+-- DROP behavior with temporary partitioned indexes
+create temp table idxpart_temp (a int) partition by range (a);
+create index on idxpart_temp(a);
+create temp table idxpart1_temp partition of idxpart_temp
+ for values from (0) to (10);
+drop index idxpart1_temp_a_idx; -- error
+ERROR: cannot drop index idxpart1_temp_a_idx because index idxpart_temp_a_idx requires it
+HINT: You can drop index idxpart_temp_a_idx instead.
+-- non-concurrent drop is enforced here, so it is a valid case.
+drop index concurrently idxpart_temp_a_idx;
+select relname, relkind from pg_class
+ where relname like 'idxpart_temp%' order by relname;
+ relname | relkind
+--------------+---------
+ idxpart_temp | p
+(1 row)
+
+drop table idxpart_temp;
-- ALTER INDEX .. ATTACH, error cases
create table idxpart (a int, b int) partition by range (a, b);
create table idxpart1 partition of idxpart for values from (0, 0) to (10, 10);
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 35d159f41b..260233ae73 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -92,6 +92,7 @@ create table idxpart (a int) partition by range (a);
create index on idxpart (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
drop index idxpart1_a_idx; -- no way
+drop index concurrently idxpart_a_idx; -- unsupported
drop index idxpart_a_idx; -- both indexes go away
select relname, relkind from pg_class
where relname like 'idxpart%' order by relname;
@@ -100,6 +101,17 @@ drop table idxpart1; -- the index on partition goes away too
select relname, relkind from pg_class
where relname like 'idxpart%' order by relname;
drop table idxpart;
+-- DROP behavior with temporary partitioned indexes
+create temp table idxpart_temp (a int) partition by range (a);
+create index on idxpart_temp(a);
+create temp table idxpart1_temp partition of idxpart_temp
+ for values from (0) to (10);
+drop index idxpart1_temp_a_idx; -- error
+-- non-concurrent drop is enforced here, so it is a valid case.
+drop index concurrently idxpart_temp_a_idx;
+select relname, relkind from pg_class
+ where relname like 'idxpart_temp%' order by relname;
+drop table idxpart_temp;
-- ALTER INDEX .. ATTACH, error cases
create table idxpart (a int, b int) partition by range (a, b);
diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml
index 0aedd71bd6..85cf23bca2 100644
--- a/doc/src/sgml/ref/drop_index.sgml
+++ b/doc/src/sgml/ref/drop_index.sgml
@@ -57,6 +57,8 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r
Also, regular <command>DROP INDEX</command> commands can be
performed within a transaction block, but
<command>DROP INDEX CONCURRENTLY</command> cannot.
+ Lastly, indexes on partitioned tables cannot be dropped using this
+ option.
</para>
<para>
For temporary tables, <command>DROP INDEX</command> is always
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 23:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-29 01:34 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-09-01 01:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 02:58 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
@ 2020-09-01 17:44 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 23:03 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2020-09-01 17:44 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On 2020-Sep-01, Michael Paquier wrote:
> On Mon, Aug 31, 2020 at 09:25:53PM -0400, Alvaro Herrera wrote:
> > Actually I think you're wrong; if I put it before the check, then if I
> > do "drop index concurrently some_temp_partitioned_index" then it would
> > fail; but if I put it after the check, then it does a normal
> > non-concurrent index and it works. I'm not sure it's necessary to break
> > a case that otherwise works ...
>
> Hmm. Right. I agree that it would be better to not break that case.
> And it means that there is a gap in the regression tests here, so I'd
> like to add a test. Please see the attached to achieve that, which
> includes your own code changes and the doc parts
Agreed -- thanks for that.
> (I didn't see a point in changing the new sentence for temporary
> relations as the follow-up <para> mentions that).
Yeah, I had come to the same conclusion.
Pushed now to all branches, thanks.
Thanks, Jan, for reporting this bug.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message.
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 23:22 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-08-29 01:34 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-09-01 01:25 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 02:58 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Michael Paquier <michael@paquier.xyz>
2020-09-01 17:44 ` Re: BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-09-01 23:03 ` Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 13+ messages in thread
From: Michael Paquier @ 2020-09-01 23:03 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: jan.mussler@zalando.de; pgsql-bugs@lists.postgresql.org
On Tue, Sep 01, 2020 at 01:44:08PM -0400, Alvaro Herrera wrote:
> Pushed now to all branches, thanks.
Thanks for taking care of it.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2020-09-01 23:03 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-26 20:11 BUG #16594: DROP INDEX CONCURRENTLY fails on partitioned table with a non helpful error message. PG Bug reporting form <noreply@postgresql.org>
2020-08-27 01:00 ` Michael Paquier <michael@paquier.xyz>
2020-08-27 17:25 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:22 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-08-27 19:51 ` Jan Mußler <jan.mussler@zalando.de>
2020-08-27 23:22 ` Michael Paquier <michael@paquier.xyz>
2020-08-29 01:34 ` Michael Paquier <michael@paquier.xyz>
2020-08-31 19:48 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 01:25 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 01:30 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 02:58 ` Michael Paquier <michael@paquier.xyz>
2020-09-01 17:44 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-01 23:03 ` Michael Paquier <michael@paquier.xyz>
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