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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Apr 16, 2020 at 2:17 PM Hamid Akhtar <hamid(dot)akhtar(at)gmail(dot)com> wrote:
>> So is the concern performance overhead rather than the need for such a feature?

> No, I don't think this would have any significant overhead. My concern
> is that I think it's the wrong way to solve the problem.

FWIW, I agree with Robert that this patch is a bad idea. His
recommendation is to use an external monitoring tool, which is not a
self-contained solution, but this isn't either: you'd need to add an
external log-scraping tool to spot the warnings.

Even if I liked the core idea, loading the functionality onto VACUUM seems
like a fairly horrid design choice. It's quite unrelated to what that
command does. In the autovac code path, it's going to lead to multiple
autovac workers all complaining simultaneously about the same problem.
But having manual vacuums complain about issues unrelated to the task at
hand is also a seriously poor bit of UX design. Moreover, that won't do
all that much to surface problems, since most(?) installations never run
manual vacuums; or if they do, the "manual" runs are really done by a cron
job or the like, which is not going to notice the warnings. So you still
need a log-scraping tool.

If we were going to go down the path of periodically logging warnings
about old prepared transactions, some single-instance background task
like the checkpointer would be a better place to do the work in. But
I'm not really recommending that, because I agree with Robert that
we just plain don't want this functionality.

regards, tom lane