4

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;
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
rdbmsNoob
  • 459
  • 6
  • 24

1 Answers1

5

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!

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 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 '21 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 '21 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 '21 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 '21 at 10:09