3

Am trying to create an index concurrently on production. However when doing so the index when viewing via \d says INVALID.

Why is this the case? Never seen it happen before.

Have tried re-creating but still get the same issue:

Index:

create index concurrently idx_wallet_customer_id_credit_stake_expires on wallet (customer_id,wallet_credit_stake,wallet_expires)
where wallet_closed is null and wallet_staked is null;
Improve this question

1 Answer 1

Reset to default
4

With CONCURRENTLY the index is being built in the background, not taking exclusive locks The final step is to make the index "valid", but that cannot happen until the last concurrent transaction has finished. Check for long-running transactions. Prime suspect would be those with state = 'idle in transaction':

SELECT * FROM pg_stat_activity;

See:

Postgres 12 or later has the system view pg_stat_progress_create_index. See:

Postgres 9.6 reaches EOL in November 2021, consider upgrading to a current version!

Improve this answer
4
  • Thanks @erwin-brandstetter after looking into pg_stat_activity could see there was something running which was impacting this index. After dropping the index, I applied a bit later on and it worked without issue.
    – rdbmsNoob
    Sep 10, 2021 at 15:49
  • an issue with the concurrently option this time again, the odd thing here is the database server only has myself accessing it and yet with my 1 session open trying to create the index concurrently it still is INVALID. Very odd one, understand if it was PG 12 I could reindex concurrently sadly on 9.6 at the moment. Is there any other reason this could happen?
    – rdbmsNoob
    Sep 13, 2021 at 21:44
  • @rdbmsNoob: Hard to say. Database corruption might do that. But that hardly ever occurs, unless you have faulty hardware (which would be something to fix immediately after taking a full working! backup first). CONCURRENTLY only makes sense to allow concurrent write load. With "no other sessions", there is no point to use it in the first place. Consider: 1. upgrading to a current version 2. a new question with all relevant details if you cannot resolve it.
    – Erwin Brandstetter
    Sep 13, 2021 at 21:53
  • the database server with no sessions was only for test purposes to see if any outside interference, UPDATES/INSERTS/SELECTS etc would make a difference, just cant understand why it want let me add it in....
    – rdbmsNoob
    Sep 14, 2021 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.