Re: Auto-tuning work_mem and maintenance_work_mem

From: "Andres Freund" <andres(at)2ndquadrant(dot)com>
> I've seen several sites shutting down because of forgotten prepared
> transactions causing bloat and anti-wraparound shutdowns.

From: "Magnus Hagander" <magnus(at)hagander(dot)net>
> I would say *using* an external transaction manager *is* the irregular
> thing. The current default *is* friendly for normal users, for example
> see the comments from Andres about what happens if you make a mistake.
> So I definitely agree with your sentiment that we should be more
> friendly for normal users - but in this case we are.
>
> If I look through all the customers I've worked with, only a handful
> have actually used a transaction manager. And of those, at least half
> of them were using it even though they didn't need it, because they
> didn't know what it was.

I understand that you mean by *irregular* that there are few people who use
distributed transactions. I guess so too: there are not many users who
require distributed transactions in the real world. I meant by *irregular*
that almost all users should use distributed transactions through an
external transaction manager incorporated in Java EE application servers or
MSDTC.

The distributed transaction features like XA and Java Transaction API (JTA)
are established. They are not irregular for those who need them; they were
developed and exist for a long time, because they were/are needed.

I don't think the default value of zero for max_prepared_transactions is
friendly for normal and not-normal users. Normal users, who properly use
external transaction manager, won't be caught by the trouble Andres
mentioned, because the external transaction manager soon resolves prepared
(in-doubt) transactions.

Not-normal users, who uses PREPARE TRANSACTION statement or
XAResource.prepare() directly from their applications without using external
transaction manager or without need based on proper understanding, won't
escape from Andres's concern. They will see the following message and
follow it blindly to make their applications succeed.

ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.

So, the current default of zero is not only unfriendly for normal users but
also non-helpful for those who make mistakes.

Regards
MauMau