why would CREATE INDEX IF NOT EXISTS timeout, with postgres?-CodePudding

I have a rather large table (tens of millions of rows) and the app's startup script is running a few things, including the line:

CREATE INDEX IF NOT EXISTS idx_trades_ticker_ts ON exchange.trades (ticker, ts);

The index already exists, so I'd expect this to be a no-op, but it takes so long the connection times out.

The db then has this in the logs:

STATEMENT:  -- create index
               CREATE INDEX IF NOT EXISTS idx_trades_ticker_ts ON exchange.trades(ticker, ts)
ERROR:  canceling statement due to user request

What could be the reason?

Edit:

The cancelling due to user request is the client timing out and dropping the connection.
The index does exist in the db
Timeouts are all 30s

The issue happens both in the app's code and if I run the same query from DataGrip.

CodePudding user response：

CREATE INDEX IF NOT EXISTS idx_trades_ticker_ts ON exchange.trades (ticker, ts);

One of the first things this instruction will do is trying to acquire a lock of mode ShareLock on exchange.trades. According to the documentation:

SHARE

Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes.

Acquired by CREATE INDEX (without CONCURRENTLY).

If other concurrent transactions have already grabbed a lock that conflicts with this one, and they keep it for more than statement_timeout, your CREATE INDEX will time out. Typically, transactions that write into the table will need that lock level.

That the index already exists does not change the necessity of taking the lock, otherwise the evaluation of the NOT EXISTS clause would be subject to race conditions.

See Lock monitoring for how to check for locks at runtime.