Re: BUG #14237: Terrible performance after accidentally running 'drop index' for index still being created

On 08/07/2016, 16:08, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> dwaller(at)microsoft(dot)com writes:
> > Summary: While running ‘create index concurrently’ on a very large table,
> > running ‘drop index’ for the same index caused Postgres to perform terribly
> > badly, until the ‘create index’ server process was killed. I would expect
> > that the ‘drop index’ would either fail immediately, or wait, without
> > performance impact, until the ‘create index concurrently’ had completed.
>
> Hmm, the DROP INDEX should have blocked waiting for an exclusive lock on
> the table, and then other queries on the table should have queued up
> behind that lock request. I think the fact that they didn't just stop
> dead probably indicates that when the deadlock checker ran, it concluded
> it needed to let them jump the queue and go in front of the DROP INDEX to
> avoid a deadlock --- likely because the lock manager could see that the
> DROP was blocked by the CREATE INDEX CONCURRENTLY while the latter was
> waiting for the other queries to finish. So your slowdown corresponds to
> an extra wait of deadlock_timeout ms per query. This is not a bug, but
> designed behavior.
>
> > I would expect that running ‘drop index’ while that index is still being
> > created would either fail immediately, or wait, without performance impact,
> > until the ‘create index concurrently’ had completed.
>
> If the DROP's lock request is not allowed to block other incoming requests
> for a table lock, the DROP could face indefinite lock starvation. That is
> not better. Reducing deadlock_timeout to zero is not going to improve
> your overall performance, either.

Thank you for the detailed explanation. This all seems very sensible, and
reasonable behaviour from Postgres. Yet... it still 'allowed' me to shoot myself
painfully in the foot. User error, I agree, yet people make mistakes - could
Postgres behave more gracefully?

For example, would it be at all feasible for Postgres to handle DDL statements
differently from regular requests? In this example it was pointless for DROP
INDEX to take any locks while there was already another DDL statement (CREATE
INDEX) running. Could it have been added to a queue of DDL statements against
that table and not attempted to take a lock until CREATE INDEX completed and
DROP INDEX then reached the head of the queue?

Thanks,

David