postgres plsql script loop get stuck
I have a Java program which access a Postgres database through JPA (EclipseLink). First I let JPA to create the database. Then I run through a java.sql.Connection (which I get by unwrap() on the entity manager) a pgsql script which manipulates the created database. The entity manager is configured to work with connection pool (c3p0). Part of the script drop the constraints of a list of tables. For some reason it gets stuck after dropping a number of tables. There is no other active transaction (pg_stat_activity), and there isn't any lock which wasn't granted (pg_locks). I've added debug printing before and after the drop constraint statement and they are both printed, so it seems it gets stuck in the middle of the loop. When selecting from pg_stat_activity the following results are returned for the relevant transaction:
Waiting: false State: Active Query: Empty
From checking out CPU usage and such it seems the postgres process is idle. When I run the script from pgAdmin it doesn't get stuck, only when it is run from the Java program. Usually it get stuck after 200 loop iterations, but it got stuck in other loops as well. The problem disappeared twice after rebasing the code (true Vodou). I'm more interested in Postgres analysis tools for such scenarios. Any ideas or technique to solve this deadlock will be appreciated.
- A workaround which solved the problem was to open a new jdbc connection and run it from there, but I'm still interested to understand how to tackle such problems int he future.
It sounds like a JDBC or C3P0 problem not a Postgresql one, if PGAdmin works but your JDBC method does not. Are you logging all queries by the C3P0 connection pooler to try and see if there are any db-level issues? The fact that PGAdmin succeeds leads me to believe that you should be reexamining your JDBC and C3P0 configurations for any problems.
More details about db locking can be found at the following pages:
It seems the problem was that the plsql script was using the raise notice for printing debug messages. For some reason when too much of those are printed the script would get stuck. Once they were removed it started working again.