Does a cancelled REINDEX CONCURRENTLY need to be messy?
Hi,
It's documented that a failed REINDEX can leave behind a transient
index, and I'm not going to speculate on all the conditions that could
lead to this situation. However, cancelling a REINDEX CONCURRENTLY
will reliably leave behind the index it was building (<index
name>_ccnew).
Doesn't a cancellation instruct the process that the user has made a
decision regarding the fate of the new version of the index? Is there
a situation where the incomplete transient index might need to be
inspected following a cancellation?
Because if not, why not get it to tidy up after itself? If the
process crashed, fair enough, but it just doesn't sit well that
leftover artifacts of an aborted operation aren't tidied up,
especially since subsequent attempts to REINDEX will find these
invalid transient versions and attempt to REINDEX them. Why should
the user need to know about them and take manual action in the case of
a cancellation?
I get the feeling that this is deliberate, and perhaps an attempt to
mitigate locking issues, or some other explanation, but the rationale
isn't immediately apparent to me if this is the case.
Thanks
Thom
On 6/29/23 11:13, Thom Brown wrote:
I get the feeling that this is deliberate, and perhaps an attempt to
mitigate locking issues, or some other explanation, but the rationale
isn't immediately apparent to me if this is the case.
I have always assumed the reason is that there might be other
transactions using the index so if we are going to drop it on rollback
we might get stuck forever waiting for an exclusive lock on the index.
How do you get around that? Rollback being stuck waiting forever is
certainly not a nice behavior.
Andreas
Andreas Karlsson <andreas@proxel.se> writes:
On 6/29/23 11:13, Thom Brown wrote:
I get the feeling that this is deliberate, and perhaps an attempt to
mitigate locking issues, or some other explanation, but the rationale
isn't immediately apparent to me if this is the case.
I have always assumed the reason is that there might be other
transactions using the index so if we are going to drop it on rollback
we might get stuck forever waiting for an exclusive lock on the index.
How do you get around that? Rollback being stuck waiting forever is
certainly not a nice behavior.
Right. The whole point of CONCURRENTLY is to never take an exclusive
lock. But once we reach the stage where the index is open for other
transactions to insert into, it's difficult to back out in a nice way.
Now that we have DROP INDEX CONCURRENTLY, you could imagine switching
into that code path --- but that *also* involves waiting for other
transactions, so you still have the problem that the transaction may
appear to be stuck and not responding to cancel.
(IIRC, cancelling DROP INDEX CONCURRENTLY also leads to a messy
situation, in that the index is still there but might not be fully
functional.)
regards, tom lane
ALTER TABLE DETACH CONCURRENTLY had to deal with this also, and it did it by having a COMPLETE option you can run later in case things got stuck the first time around. I suppose we could do something similar, where the server automatically does the needful, whatever that is.
On Thu, 29 Jun 2023, 14:45 Álvaro Herrera, <alvherre@alvh.no-ip.org> wrote:
ALTER TABLE DETACH CONCURRENTLY had to deal with this also, and it did it
by having a COMPLETE option you can run later in case things got stuck the
first time around. I suppose we could do something similar, where the
server automatically does the needful, whatever that is.
So there doesn't appear to be provision for deferred activities.
Could, perhaps, the fact that it is an invalid index that has no locks on
it, and is dependent on the table mean it could be removed by a VACUUM?
I just don't like the idea of the user needing to remove broken things.
Thom
On 2023-Jul-01, Thom Brown wrote:
On Thu, 29 Jun 2023, 14:45 Álvaro Herrera, <alvherre@alvh.no-ip.org> wrote:
ALTER TABLE DETACH CONCURRENTLY had to deal with this also, and it did it
by having a COMPLETE option you can run later in case things got stuck the
first time around. I suppose we could do something similar, where the
server automatically does the needful, whatever that is.So there doesn't appear to be provision for deferred activities.
There is not.
Could, perhaps, the fact that it is an invalid index that has no locks on
it, and is dependent on the table mean it could be removed by a VACUUM?
Well, I definitely agree that it would be useful to have *something*
that automatically removes debris (I'm not sure VACUUM is the best place
to do it. Perhaps we could have autovacuum check for it, and do it
separately of vacuum proper.)
On the whole, the reason we don't have such a mechanism AFAIK is that
nobody has presented a credible implementation for it. There was a push
to use UNDO to remove orphan files; if we had that, we could also use it
to implement cleanup of dead indexes and partially-detached partitions.
However, that project crashed and burned a long time ago and has seen no
resurrection as yet.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)
On Mon, Jul 03, 2023 at 07:46:27PM +0200, Alvaro Herrera wrote:
On 2023-Jul-01, Thom Brown wrote:
On Thu, 29 Jun 2023, 14:45 Álvaro Herrera, <alvherre@alvh.no-ip.org> wrote:
ALTER TABLE DETACH CONCURRENTLY had to deal with this also, and it did it
by having a COMPLETE option you can run later in case things got stuck the
first time around. I suppose we could do something similar, where the
server automatically does the needful, whatever that is.
I could imagine a code path for manual and automatic operations for
REINDEX (?) at table level and at database level, but using this
keyword would be strange, as well. CONCURRENTLY cannot work on system
indexes so SYSTEM does not make sense, and index level is no different
than a DROP.
Well, I definitely agree that it would be useful to have *something*
that automatically removes debris (I'm not sure VACUUM is the best place
to do it. Perhaps we could have autovacuum check for it, and do it
separately of vacuum proper.)
Being able to reuse some of the worker/launcher parts from autovacuum
could make things easier for a bgworker implementation, perhaps?
--
Michael
On 2023-Jul-04, Michael Paquier wrote:
On Mon, Jul 03, 2023 at 07:46:27PM +0200, Alvaro Herrera wrote:
Perhaps we could have autovacuum check for it, and do it
separately of vacuum proper.)Being able to reuse some of the worker/launcher parts from autovacuum
could make things easier for a bgworker implementation, perhaps?
TBH I don't understand what you are thinking about.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"I can see support will not be a problem. 10 out of 10." (Simon Wittber)
(http://archives.postgresql.org/pgsql-general/2004-12/msg00159.php)
