postgresqlpostgres15

Postgres: is there an exhaustive list of causes that make `CREATE INDEX CONCURRENTLY` end in an `INVALID` state?

Besides these potential, documented causes:

  1. Are there more?
  2. How could a CREATE INDEX CONCURRENTLY statement, with its SHARE UPDATE EXCLUSIVE lock, end up in a deadlock, mentioned in the documentation above?
  3. Is lock_timeout actually needed, when CREATE INDEX CONCURRENTLY is the only DDL statement to be run? Isn't it ok to just 'wait it out' until the lock is acquired, as the statement won't be blocking regular CRUD operations anyway. I can only think of one problematic scenario: if the incoming data distribution varies enough that not running ANALYZE (which would be blocked) would impact the planner.
Solution

  • How to cause a deadlock with CREATE INDEX CONCURRENTLY:

    Session 1:

    CREATE TABLE tab (id integer);
BEGIN;
INSERT INTO tab VALUES (1);

    Session 2:

    -- this will hang
CREATE INDEX CONCURRENTLY ON tab (id);

    Session 3:

    -- this will hang
ALTER TABLE tab ADD col2 integer;

    Session 1:

    -- this will hang
ALTER TABLE tab ADD col3 integer;

    On my PostgreSQL v16, the deadlock detector will cancel sessions 1 and 2.

    The exhaustive list when CREATE INDEX CONCURRENTLY will fail:

    This is a fool's errand. You will never be able to enumerate all possible causes. Let me give you a few to show how pointless the exercise is:

    Any other runtime error will serve.

    To be honest, I don't see the point behind that question. When you run CREATE INDEX CONCURRENTLY, you check if it succeeds or not. If not, drop the invalid index. Alternatively, if you are in the habit of creating indexes frequently, schedule a regular job that gets rid of all invalid indexes.