Re: Index creation problem

Oliver Kohll wrote:
> I'm getting a problem where my application hangs in the process of
adding a field to a table. The app
> adds the field, then creates an index on the field. It hangs for ages
(minutes) until I cancel the
> query. My investigation so far has been
>
> Look at current queries:
>
> agilebasedata=# SELECT datname,procpid,current_query FROM
pg_stat_activity;
>      datname     | procpid |
current_query
>
-----------------+---------+--------------------------------------------
------------------------------
> ---------------------------------------
>  agilebaseschema |    5799 | <IDLE> in transaction
>  agilebasedata   |   18126 | SELECT datname,procpid,current_query FROM
pg_stat_activity;
>  agilebasedata   |    5844 | <IDLE>
>  agilebasedata   |    5108 | CREATE INDEX
l_ntvs1fk9de719830100m5aoi8suwo ON
> ntvs1fk9desoci59z(lower(m5aoi8suwo4jocu76) varchar_pattern_ops)
>  agilebasedata   |    5109 | <IDLE> in transaction
>  agilebaseschema |   25200 | <IDLE>
>  agilebasedata   |   29257 | <IDLE>
>  agilebasedata   |   31574 | <IDLE>
> (8 rows)
>
> As you can see, the only user query running is the CREATE INDEX.
Cancelling this with
>
> select pg_cancel_backend(5108);
>
> gets the app back on it's feet.
>
> I thought there may be some sort of deadlock, but there is only one
long running query. By the way,
> the indexing query should return quickly, as there are 0 rows in the
table. It does in fact return
> immediately when you run it manually:
>
> agilebasedata=# CREATE INDEX l_nx4uaurg3r1981190097whsqcun3e9 ON
> nx4uaurg3r94vwqhj(lower(whsqcun3e9lfzlwlv) varchar_pattern_ops);
> ERROR:  relation "l_nx4uaurg3r1981190097whsqcun3e9" already exists
> agilebasedata=# drop index l_nx4uaurg3r1981190097whsqcun3e9;
> DROP INDEX
> agilebasedata=# CREATE INDEX l_nx4uaurg3r1981190097whsqcun3e9 ON
> nx4uaurg3r94vwqhj(lower(whsqcun3e9lfzlwlv) varchar_pattern_ops);
> CREATE INDEX
>
>
> One thing to mention is that there are a lot of indexes and relations:
>
> agilebasedata=# select count(*) from pg_index;
>  count
> -------
>   2587
> (1 row)
>
> agilebasedata=# select count(*) from pg_class;
>  count
> -------
>   5361
> (1 row)
>
> I wonder if I'm running up against some sort of limit. I am going to
change the code so it doesn't add
> an index (it's not always necessary) but would like to get to the
bottom of things first.


In the example you show, some connections are "idle in transaction".
Such connections can hold locks that block your CREATE INDEX.
Could you look at pg_locks if there is a lock that is not granted?
There could also be prepared transactions holding locks, if
you use that feature.

There is also CREATE INDEX CONCURRENTLY which locks the table
less.  Does that make a difference?

Does the process for the hanging backend consume CPU?
You could try taking a stack trace to see where it hangs.

Yours,
Laurenz Albe