Have a problem where a stored procedure is taking a week to run. The stored procedure should take less than a second to run. In researching a select hanging problem, three things are suggested; an autovacuum problem, a resource is locked, or there is something wrong with the stored procedure.
· Autovacuum is running. A ‘ps –elf | grep postgres’ shows:
00:00:43 postgres: logger process
00:5:50 postgres: writer process
00:3:04 postgres: wal writer process
00:00:48 postgres: autovacuum launcher process
00:00:50 postgres: stats collector process
00:01:28 postgres: operstions OPDB [local] idle
154.11.29 postgres: operstions OPDB [local] select
· The select is from running a select of a stored procedure from a ‘c’ program using the PqsendQuery function.
· Postgres.conf has both autovacuum and track_counts set to ‘on’. All other autovacuum values are left as delivered (commented out).
· A ‘select * from pg_stats_activity;’ shows no query is blocked.
· We have recently changed from using Oracle 10g (running on Red Hat AS 4.5) to PostgreSQL 9.1.2 (running on CentOS 6.3). The only differences between the two versions are:
o Syntax changes between Oracle and Postgres.
o In Oracle a commit was executed after each ‘chuck’ of work was done. A commit is no longer used in Postgres because the Postgres documentation indicates that a commit has no affect until the end of the transaction (i.e., the end of the stored procedure).
o The same stored procedure is running just fine on all of our test systems and at one of our two customer sites. All of the systems are configured the same (same operating system and software).
Lastly, in the directories used to store the tables and indexes, there are 918896 files in the tables directory and 921291 files in the indexes directory. All of the file names are just numbers (no extensions). About 60 files are added to each directory every second. On our test systems and at our other customer site, there are only about 50 files in each directory.
