CODEWITHSUNDEEP
postgresqlindexingpostgresql-9.6pgadmin-4
Rj_N
Rj_N

Reputation: 204

Executed create index concurrently statmt & it was disconnected session due to timeout error.but I do see in pg_stat_activity,it is in running state

I have executed create an index on a big table from pgAdmin, and in a while, I lost connection to the server, so the execution window closed in pgAdnin. Then I reconnected to the server, and when checked pg_stat_activity, I do see that the create index statement is running (active) state, I just wondering to know whether this index being creating or stuck somewhere?

  1. client disconnected with error, cancelling statement due to statement timeout

  2. when I reconnected to the server, in pg_stat_activity. 31937 "edsadmin" "09:54:44.280176" "CREATE INDEX CONCURRENTLY idx_src_record_date ON pcd_t.l_esd_detail_report USING btree (src_record_date COLLATE pg_catalog."default") TABLESPACE pg_default;"

I'm really confused here wheather it is createing or not.

Upvotes: 1

Views: 1322

Answers (1)

Mike Caron
Mike Caron

Reputation: 14561

Late answer, but relevant none-the-less.

Based on my anecdotal experience right now, the index creation persists beyond the lifetime of the client.

I did the following:

database=> CREATE INDEX CONCURRENTLY IX_myIndex on table(column, column2);

It paused for a few minutes, then:

SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Succeeded.
psql (14.2 (Debian 14.2-1.pgdg110+1), server 10.18)

Immediately after reconnecting, I ran:

database=> \d table
                       Table "public.table"
     Column      |            Type             | Collation | Nullable | 
Default
-----------------+-----------------------------+-----------+----------+---------
... blah blah blah ...
Indexes:
    "IX_myIndex" btree (column, column2) INVALID

I waited a few more minutes, then checked again:

database=> \d table
                       Table "public.table"
     Column      |            Type             | Collation | Nullable | 
Default
-----------------+-----------------------------+-----------+----------+---------
... blah blah blah ...
Indexes:
    "IX_myIndex" btree (column, column2)

So, your index is likely fine.

Upvotes: 1

Related Questions