CODEWITHSUNDEEP
postgresql
Snorlax
Snorlax

Reputation: 837

close client before finishing command postgresql

Let's say that I would like to create an index. It will take some time. I use pgadmin. Let's say that when query is being executed pgadmin crashed for some reason (for example computer is restarted).

What would be the status of that index. Would it keep on being created and finally at some point it would be created with success or it will fail immedately or it will fail aftger some time?

Is there any way to check what is the status of index being created? (Im using postgres version 10.x)

Upvotes: 1

Views: 42

Answers (1)

klin
klin

Reputation: 121764

It is hard to answer the question if we do not know the reason for the crash of an application. If it was not caused by a server failure it is likely that the index was created correctly. You can check this by querying the system catalog pg_index. You have to know the index name, e.g.:

select indexrelid::regclass, indisvalid
from pg_index
where indexrelid::regclass::text = 'my_table_unique_col_key'

Per the documantaion:

indisvalid - If true, the index is currently valid for queries. False means the index is possibly incomplete: it must still be modified by INSERT/UPDATE operations, but it cannot safely be used for queries. If it is unique, the uniqueness property is not guaranteed true either.

If the index is not created yet (or at all due to a query failure) the above query returns no rows. You can check whether the query that creates the index is still running (see Dynamic Statistics Views):

select *
from pg_stat_activity
where query ilike 'create index%'

Upvotes: 2

Related Questions