2

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 stored 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 can't restart PostgreSQL service until I kill those orphan process one by one by hand.

So what can cause the postgresql service frozen?

Why are those orphan process still living after I stop the service?

a busy cat

Log file

The stored 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 stored procedure in pgAdmin didn't end neither for +100 seg. and timeout is at 70seg.

  • Odd issue. Can you enable checkpoint logging please? log_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:02
  • @CraigRinger i made the change for log_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:26
  • @JuanCarlosOropeza A SELECT pg_reload_conf() or pg_ctl reload is sufficient. – Craig Ringer Jul 10 '14 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.