I have windows server 2008 R2 and postgresql 9.3 Server is still for testing so I'm alone and only have a .net application doing consult with npgsql to the db, mostly doing some store procedures and some update with NpgsqlDataAdapter
In pgAdmin sometimes I'm doing a simple query and then take forever like PostgreSQL is frozen.
I go to service stop the postgresql service but in the task manager still appear some "postgres.exe" process.
I cant restart postegresql service until i kill those orphan process one by one by hand
So what can cause the postgresql service fronzen?
Why are those orphan process still living after i stop the service?
http://i62.tinypic.com/w0hvdd.png
UPDATE log file (is there a better way to post a file?)
The store procedure is moving data from a db to another using dblink. And fail doing the DROP TABLE before recreate the table. There is also 2 other thread working with the db and those are still running without trouble.
2014-07-08 11:42:10 VET LOG: database system was interrupted; last known up at 2014-07-08 11:36:44 VET
2014-07-08 11:42:10 VET FATAL: the database system is starting up
2014-07-08 11:42:10 VET LOG: database system was not properly shut down; automatic recovery in progress
2014-07-08 11:42:10 VET FATAL: the database system is starting up
2014-07-08 11:42:10 VET LOG: redo starts at A9/47827B0
2014-07-08 11:42:14 VET FATAL: the database system is starting up
2014-07-08 11:42:15 VET LOG: record with zero length at A9/5683970
2014-07-08 11:42:15 VET LOG: redo done at A9/5683930
2014-07-08 11:42:15 VET LOG: last completed transaction was at log time 2014-07-08 11:39:23.99-04:30
2014-07-08 11:42:16 VET FATAL: the database system is starting up
2014-07-08 11:42:17 VET LOG: database system is ready to accept connections
2014-07-08 11:42:17 VET LOG: autovacuum launcher started
2014-07-08 12:27:35 VET ERROR: canceling statement due to user request
2014-07-08 12:27:35 VET CONTEXT: SQL statement "DROP TABLE if exists app.vzla_traffic" PL/pgSQL function app."LoadTraffic"() line 7 at SQL statement
2014-07-08 12:27:35 VET STATEMENT: select app."LoadTraffic"()
2014-07-08 12:32:32 VET ERROR: canceling statement due to user request
2014-07-08 12:32:32 VET CONTEXT: SQL statement "DROP TABLE if exists app.vzla_traffic" PL/pgSQL function app."LoadTraffic"() line 7 at SQL statement
2014-07-08 12:32:32 VET STATEMENT: select app."LoadTraffic"()
2014-07-08 12:37:30 VET ERROR: canceling statement due to user request
2014-07-08 12:37:30 VET CONTEXT: SQL statement "DROP TABLE if exists app.vzla_traffic" PL/pgSQL function app."LoadTraffic"() line 7 at SQL statement
2014-07-08 12:37:30 VET STATEMENT: select app."LoadTraffic"()
i guess the canceling statement due to user request is the .net app canceling because timeout. Because running the storeproc in pgAdmin didnt end neither for +100 seg. and timeout is at 70seg
log_destination = redirect_ stderrhope next time error occurs i have some more evidence. – Juan Carlos Oropeza Jul 8 '14 at 17:05log_checkpoint = on. Personally I'd attach a debugger to the "orphan" backends to see what they were doing and what type of backend they are, but that's probably not within your comfort zone unless you've done a bit of development work. In case it is: wiki.postgresql.org/wiki/… – Craig Ringer Jul 9 '14 at 0:02log_checkpoint. Do i have to turn off the service and restart to that change be active or just updating the config make it work? – Juan Carlos Oropeza Jul 9 '14 at 15:26SELECT pg_reload_conf()orpg_ctl reloadis sufficient. – Craig Ringer Jul 10 '14 at 6:12