Re: Do we need to handle orphaned prepared transactions in the server?

On Thu, Jan 30, 2020 at 8:28 AM Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On Thu, 30 Jan 2020 at 02:04, Hamid Akhtar <hamid(dot)akhtar(at)gmail(dot)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.
>

Having seen the documentation, IMHO the document does contain enough
information for users to understand what issues can be caused by these
orphaned prepared transactions.

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

I think that opens up the debate on what we really mean by "old" and
whether that requires a syntax change when creating a prepared
transactions as Thomas Kellerer suggested earlier?

I agree that vacuum should periodically throw warnings for any prepared
xacts that are holding xmin down.

Generalising it for all xmin-retention is a fair idea IMHO, though that
does increase the overall scope here. A vacuum process should (ideally)
periodically throw out warnings for anything that is preventing it
(including
orphaned prepared transactions) from doing its routine work so that
somebody can take necessary actions.

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

--
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
CELL：+923335449950 EMAIL: mailto:hamid(dot)akhtar(at)highgo(dot)ca
SKYPE: engineeredvirus