Re: Create Index CONCURRENTLY Hangs Indefinitely.

[Adarsh Sharma <eddy.adarsh@xxxxxxxxx>]

Hi Wei,

There are many aspects that comes into picture :

1. Did you check that your create index concurrently command is in waiting or running state in pg_stat_activity.

2. Terminate the session by Ctrl+C results into INVALID index. You might want to drop that as well.

3. Increase the work_mem to ~ 3-4 GB before creating the index.

4. Create Index Concurrently usually takes longer time as compared with Create Index Command.

thanks

On Tue, 19 May 2015 at 16:01 Wei Shan <weishan.ang@xxxxxxxxx> wrote:

Hi all,

Today I tried to create an index online, however it took far too long that I cancelled the query.

1. psql version => 9.2.4

2. Table structure as follows. (7.5GB in size)

evocsp=# \d certificatedata

       Table "public.certificatedata"

        Column        |  Type   | Modifiers

----------------------+---------+-----------

 fingerprint          | text    | not null

 base64cert           | text    |

 cafingerprint        | text    |

 certificateprofileid | integer | not null

 expiredate           | bigint  | not null

 issuerdn             | text    | not null

 revocationdate       | bigint  | not null

 revocationreason     | integer | not null

 rowprotection        | text    |

 rowversion           | integer | not null

 serialnumber         | text    | not null

 status               | integer | not null

 subjectdn            | text    | not null

 subjectkeyid         | text    |

 tag                  | text    |

 type                 | integer | not null

 updatetime           | bigint  | not null

 username             | text    |

Indexes:

    "certificatedata_pkey" PRIMARY KEY, btree (fingerprint)

    "certificatedata_idx7" btree (certificateprofileid)

3. The query ran was => CREATE INDEX CONCURRENTLY certificatedata_idx2 ON CertificateData (username) tablespace tablespace_index;

4. After it took more than 2hrs+, I went to terminate the session via Ctrl-C, it returned the following error.

WARNING:  canceling wait for synchronous replication due to user request

DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.

^CCancel request sent

ERROR:  canceling statement due to user request

Any input or ideas are welcome.

Thanks!

--

Regards,
Ang Wei Shan