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

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. That said, implementing something similar for
this feature is too much of an overhead both in terms of code complexity
and resources utilisation (if the feature is implemented).

I'm currently working on other options to tackle this problem.

On Tue, 28 Jan 2020 at 9:04 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On Thu, 23 Jan 2020 at 15:04, Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> > It seems to me that what you are describing here is a set of
> > properties good for a monitoring tool that we don't necessarily need
> > to maintain in core. There are already tools able to do that in ways
> > I think are better than what we could ever design, like
> > check_pgactivity and such.
>
> I really have to disagree here.
>
> Relying on external tools gives users who already have to piece
> together a lot of fragments even more moving parts to keep track of.
> It introduces more places where new server releases may not be
> supported in a timely manner by various tools users rely on. More
> places where users may get wrong or incomplete information from
> outdated or incorrect tools. I cite the monstrosity that
> "check_postgres.pl" has become as a specific example of why pushing
> our complexity onto external tools is not always the right answer.
>
> We already have a number of views that prettify information to help
> administrators operate the server. You could argue that
> pg_stat_activity and pg_stat_replication are unnecessary for example;
> users should use external tools to query pg_stat_get_activity(),
> pg_stat_get_wal_senders(), pg_authid and pg_database directly to get
> the information they need. Similarly, we could do away with
> pg_stat_user_indexes and the like, as they're just convenience views
> over lower level information exposed by the server.
>
> But can you really imagine using postgres day to day without
> pg_stat_activity?
>
> It is my firm opinion that visibility into locking behaviour and lock
> waits is of a similar level of importance. So is giving users some way
> to get insight into table and index bloat on our MVCC database. With
> the enormous uptake of various forms of replication and HA it's also
> important that users also be able to see what's affecting resource
> retention - holding down vacuum, retaining WAL, etc.
>
> The server knows more than any tools. Views in the server can also be
> maintained along with the server to address changes in how it manages
> things like resource retention, so external tools get a more
> consistent insight into server behaviour.
>
> > I'd rather just focus in the core code on the basics with views
> > that map directly to what we have in memory and/or disk.
>
> Per above, I just can't agree with this. PostgreSQL is a system with
> end users who need to interact with it, most of whom will not know how
> its innards work. If we're going to position it even more as a
> component in some larger stack such that it's not expected to really
> be used standalone, then we should make some effort to guide users
> toward the other components they will need *in our own documentation*
> and ensure they're tested and maintained.
>
> Proposals to do that with HA and failover tooling, backup tooling etc
> have never got off the ground. I think we do users a great disservice
> there personally. I don't expect any proposal to bless specific
> monitoring tools to be any more successful.
>
> More importantly, I fail to see why every monitoring tool should
> reinvent the same information collection queries and views, each with
> their own unique bugs and quirks, when we can provide information
> users need directly from the server.
>
> In any case I guess it's all hot air unless I pony up a patch to show
> how I think it should work.
>
> --
> 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