On Wed, Feb 19, 2020 at 10:05 AM Hamid Akhtar <hamid.akhtar@gmail.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.