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

On Wed, 15 Apr 2020 at 03:19, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Feb 19, 2020 at 10:05 AM Hamid Akhtar <hamid(dot)akhtar(at)gmail(dot)com>
> wrote:
> > Attached is version 1 of POC patch for notifying of orphaned
> > prepared transactions via warnings emitted to a client
> > application and/or log file. It applies to PostgreSQL branch
> > "master" on top of "e2e02191" commit.
>
> I think this is a bad idea and that we should reject the patch. It's
> true that forgotten prepared transactions are a problem, but it's also
> true that you can monitor for that yourself using the
> pg_prepared_xacts view. If you do, you will have a lot more
> flexibility than this patch gives you, or than any similar patch ever
> can give you.
>

I agree. It's going to cause nothing but problems.

I am generally a fan of improvements that make PostgreSQL easier to use,
easier to monitor and understand, harder to break accidentally, etc. But
not when those improvements come at the price of correct behaviour for
standard, externally specified interfaces.

Nothing allows us to just throw away prepared xacts. Doing so violates the
very definition of what a prepared xact is. It's like saying "hey, this
table is bloated, lets discard all rows with xmin < foo because we figure
the user probably doesn't care about them; though they're visible to some
still-running xacts, but those xacts haven't accessed the table.". Um. No.
We can't do that.

If you want this, write an extension that does it as a background worker.
You can munge the prepared xacts state in any manner you please from there.

I advocated for visibility / monitoring improvements upthread that might
help mitigate the operational issues. Because I do agree that there's a
problem with users having to watch the logs or query obscure state to
understand what the system is doing and why bloat is being created by
abandoned prepared xacts.

Just discarding the prepared xacts is not the answer though.

--
Craig Ringer http://www.2ndQuadrant.com/
2ndQuadrant - PostgreSQL Solutions for the Enterprise