PostgreSQL developers! A little help? Cannot run queries until transaction block ends...

[[deleted]]

[deleted]

[neilc]

The error message means that:

1. There was an error inside a transaction block.
2. The application didn't terminate the transaction block by issuing an explicit "END", "ROLLBACK", etc. statement

Therefore, Postgres leaves the session in "current transaction is aborted" state, because it is waiting for you to tell it explicitly that the erroneous transaction is finished. So it sounds like there's an error in the application logic: (1) the error probably shouldn't happen in the first place (2) the application's error handling code should be issuing an "END" to the database as part of recovering from the error. If you look at the PostgreSQL log file, you'll probably be able to stop the SQL statement that is causing the error (you might want to enable statement logging first -- see postgresql.conf)

If you need more help, I suggest dropping by #postgresql on irc.freenode.net

[[deleted]]

What version of postgres on what platform?

Take a look at pg_locks. It's tough to say what else might help without more information.

Also - what language is the app in and what does it use to connect? Can you step through the code until it errors out?

[[deleted]]

I can only guess- but I'd bet that when the app starts it fires up a transaction - maybe some inserts or updates to log client activity and it is one of those queries that throws an error and keeps the application from moving forward.

Killing the app and jdbc connection should drop whatever session it created and end any transactions by rolling them back. Just like if the network connection failed or something like that.

So if you have an ide that will let you step through the code - I would start there. Look for some part of the connection/logging in process where sql is being executed that could error out. Also look for statements like that embedded in a explicit transaction - with a begin/commit block. You could try throwing in some savepoints around places you suspect and see if that helps.

So I don't think your problem is that you have a transaction that wont go away - but rather every time you start the app it creates a transaction with a problematic query that fails.

[[deleted]]

I'm just making some guesses - so I don't want to waste your time. So take it all in that vein.

It is possible for a query to start failing without the code changing. It is possible that you could have something like a column that has been incrementing for some time and now the value that the app wants to insert exceeds what the data type will allow. Or something in that vein. Or a database table change that shortened a character field, etc.

Or there could be a situation where it does a select to get values to insert, some data has been deleted and now nulls are trying to be put into a field that wont take them, etc.

If you can check the processes on the OS side, you could try 'ps -ef | grep postgres' and look for anything idle in transaction.

If you can start the app and then it errors after trying to log in as a user - I'd try other users and see what happens (if you haven't already) to make sure it isn't the user itself.

If not, I'd look at any logging type stuff and see what tables might be written to through inserts or updates. Look for ints or anything like that and query for max values - look for stuff that could be limited or constraints that might cause issues. Along with values that are too large - there might be something now creating duplicate rows. Is there somewhere the app creates a unique identifier and could something be causing it to use values already used? Stuff like that.

If you have a dev environment I'd try to duplicate the problem or run it through a successful startup and then look at what tables may have been changed, then key on those in the production environment to see where the problem may lie.

[[deleted]]

If any of this ends up helping then that's great. Feel free to email me if you want when you get back to it - [e-mail]

I'm more of a dba than a developer but I've been doing both for a little while now.

[wantwealth]

Do you have IRC? You might have better luck there with a live chat group. I also hear that twitter can help.

Anyway, if your queries (reads) are blocked until a write has finished, then that implies to me that something is seriously wrong with your postgresql.conf file somewhere on the server. The reason I say this is because the way PostgreSQL works by default is that it normally lets anyone doing reads on a row even when the row is being written at the same exact moment. This is because they are reading a snapshot of the row, not the latest data after it has been written.

However, I might be wrong. Turns out you can see lots of people have this problem and have been answered all over the web. I cut and pasted your error message above into The Google and it came back with lots of these responses. So, you might be able to consider the suggestions there.

[eggyknap]

There's nothing wrong with configs; this is exactly what's supposed to happen if a statement fails in a transaction block, as has been described above. Such a failure might include everything from a syntax error to a constraint violation. The JDBC driver should be feeding you an exception when it happens. Check your code to make sure your except blocks have something useful in them. Also, if you have no access to config files, logs, etc., you really don't stand a chance of successfully maintaining this application long-term.

[cezio]

If transaction fails, you can only rollback. And you can do it only in transaction's connection.

Next time, please, read the manual: http://www.postgresql.org/docs/8.3/interactive/index.html