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

Question

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.

I see: "canceling statement due to user request". That would indicate something is asking that the statement being cancelled. What client are you using to run the script? What happens in the script after the CREATE INDEX portion? Are you sure that idx_trades_ticker_ts does exist? Do you have postgresql.conf *_timeout(s) set? Add answers as update to question. — Adrian Klaver
– Adrian Klaver, Commented Apr 19, 2022 at 18:04

Daniel Vérité · Accepted Answer · 2022-04-19 20:11:56Z

4

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.

edited Apr 19, 2022 at 20:11

answered Apr 19, 2022 at 18:24

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Over a year ago

you were right, it was a locking issue. Thanks! I have a bunch of very long running transactions and, since then, everything has started to go downhill very quick. I'll investigate.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related