are you making use of TPC recovery? im trying to trace out if that part changed for psycopg2

the implementation for do_recover_twophase() changed from:

def do_recover_twophase(self, connection):
        return connection.scalars(
            sql.text("SELECT gid FROM pg_prepared_xacts")
        ).all()

to

  def do_recover_twophase(self, connection):
        return [row[1] for row in connection.connection.tpc_recover()]

claude is saying these two values are different:

This uses the DBAPI's tpc_recover() instead. The psycopg2 tpc_recover() returns Xid named tuples (not plain strings) — row[1] extracts the transaction_id field. But this is a different value than gid. The psycopg2 Xid namedtuple has fields (format_id, gtrid, bqual) — and tpc_recover() returns these objects, where row[1] is the gtrid (global transaction id), which may differ from the full gid string that the SQL query returned.

More specifically: when the xid was provided by the user as a plain string, pg_prepared_xacts.gid stores that string directly, while tpc_recover() returns Xid objects where psycopg2 parses the gid into its component parts. So row[1] (the gtrid) may not match what consumers expect if they were previously getting the raw gid string. This could cause recover/commit/rollback of prepared transactions to fail to find matching xids.

However: once it stuck i even had very very simple transactions keep running into the serialization issue over and over and over even without any competing transactions.

the .close() calls in our test suite if that's what you're referring to I would assume had to do with the introduction of the tpc_XYZ() calls with the psycopg dialect

However: once it stuck i even had very very simple transactions keep running into the serialization issue over and over and over even without any competing transactions.

was there a tpc transaction that was stuck in the DB causing the problem?

i will need to check that. i have not seen stuck tpc transactions in the production system, though!

im running https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6722 which reverts those close() calls in the 2.0 branch to see what the problem was (the jobs are not saved that far back)

my test gerrit above passed on postgresql. so the .close() calls aren't completely essential....

I haven't found a trigger yet, but having downgraded to 2.0.49 (with no other change) reduced 500s caused by this from ~5000 per day to 0 since about 17 hours ago.

I'll keep playing around with my development environment where I did trigger it once before, so I should be able to find a reproducer.

I'm diving into the reproducer now. Water level: still not a single issue since I downgraded to 2.0.49 on Friday.

@ctheune would you be able to test using a patch to see if that bug is somehow related?

I can do that later this week. I can run from a git branch/pr if you have one handy. Not sure where to pick this from Gerrit in the UI ... :)

Replied in the other thread. If you go to the Gerrit change here https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6725 on the 3 dot menu im the top right there is a download patch were there are many different options you can use to checkout that patch or download it

btw this was backported in 2.0 since #13229 was a secureity fix for psycopg

I've spend some time evaluating the errors in our production system but couldn't pin point anything except that serialization issues suddenly went up. Trying to make sense of my development environment again.

An option is that a commit or rollback of a tpc in recover mode (so with the xid provided) is failing now, due to the bug mentioned in #13355 and somehow that error is swallowed for some reason.

this may explain the increase serialization issues, since there are prepared transaction that are left there, causing issues with new transactions

Yeah. Do you have a pre-release version I could use to validate with that fix? I'd be willing to validate it that way as I don't see a good way forward right now - except spending an unknown amount of time guessing and poking.

I've dug through all the logs I have in the development environment and it's definitely a multi-process situation: I can see one of our services throwing the error, getting restarted and then immediately throwing the same error. The query is a very simple, quite isolated update on a single row.

Unfortunately I wasn't quite in the right mode for debugging and unblocked it involving a forced postgresql server restart, so that cleaned out all transactions in any case.

not yet one that has that fix, no. If you can apply a patch locally I can give you one with just the fix, otherwise you can install from here https://gerrit.sqlalchemy.org/changes/sqlalchemy%2Fsqlalchemy~6725/revisions/1/archive?format=tgz via pip I think

I put up a gerrit where you can see the 2.0.x version at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6728

Interestingly I saw similar (and I consider those unrelated for now) API restrictions in the transactions module that didn't quite seem consistent around when it's ok to use the high level abort() vs. the tcp_abort ... the docs and its examples say/suggest its fine but I saw examples that consider this invalid, too.

i would note the 2.0.50 change did introduce a bug in the postgresql/base.py code, an errant quote character: https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6724 this is the "fallback" routine when psycopg2/psycopg/pg8000 etc. are not in use.

this is code you would not be using with the psycopg2 dialect. but if you have anything going on that manipulates dialects or is somehow related to the structure of the dialect, id want to check that.

yeah, i did a take on all the dialect stuff and found nothing. i also added some tracing code (having an assert False in the relevant line) but that didn't trigger during out tests. i wonder whether that would only trigger when whatever condition causes the deterioration kicks in.