Re: Auto-tuning work_mem and maintenance_work_mem

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

From: "Magnus Hagander" <magnus@hagander.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