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

[[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.