Hi,
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.
Regards
Oliver Kohll
