On Thu, 30 Jan 2020 at 02:04, Hamid Akhtar <hamid.akh...@gmail.com> wrote: > > So having seen the feedback on this thread, and I tend to agree with most of > what has been said here, I also agree that the server core isn't really the > ideal place to handle the orphan prepared transactions. > > Ideally, these must be handled by a transaction manager, however, I do > believe that we cannot let database suffer for failing of an external > software, and we did a similar change through introduction of idle in > transaction timeout behavior.
The difference, IMO, is that idle-in-transaction aborts don't affect anything we've promised to be durable. Once you PREPARE TRANSACTION the DB has made a promise that that txn is durable. We don't have any consistent feedback channel to back to applications and say "Hey, if you're not going to finish this up we need to get rid of it soon, ok?". If a distributed transaction manager gets consensus for commit and goes to COMMIT PREPARED a previously prepared txn only to find that it has vanished, that's a major problem, and one that may bring the entire DTM to a halt until the admin can intervene. This isn't like idle-in-transaction aborts. It's closer to something like uncommitting a previously committed transaction. I do think it'd make sense to ensure that the documentation clearly highlights the impact of abandoned prepared xacts on server resource retention and performance, preferably with pointers to appropriate views. I haven't reviewed the docs to see how clear that is already. I can also see an argument for a periodic log message (maybe from vacuum?) warning when old prepared xacts hold xmin down. Including one sent to the client application when an explicit VACUUM is executed. (In fact, it'd make sense to generalise that for all xmin-retention). But I'm really not a fan of aborting such txns. If you operate with some kind of broken global transaction manager that can forget or abandon prepared xacts, then fix it, or adopt site-local periodic cleanup tasks that understand your site's needs. -- Craig Ringer http://www.2ndQuadrant.com/ 2ndQuadrant - PostgreSQL Solutions for the Enterprise
