| 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 www.gtwm.co.uk - company / www.agilebase.co.uk - product |
