Re: Prepared transactions vs novice DBAs, again

Joshua D. Drake wrote:
> On Wed, 2009-04-22 at 13:48 -0400, Tom Lane wrote:
>> The main
>> objection to just setting max_prepared_transactions to zero by default
>> is that it would kill our ability to test the feature in the standard
>> regression tests.
>
> That kills it for me. Unless we want to change the way we test.

I think we should change the way we test it. Could we simply make
max_prepared_transactions = 0 the default, but put
"max_prepared_transactions = 5" into the config file in "make check"?
That seems like the best alternative, it doesn't seem right to build
extra config options or other infrastructure into the server just so
that the regression tests can test a feature.

Perhaps we should also make the manual more clear on the fact that
PREPARE TRANSACTION isn't supposed to be used by casual users, but only
by an external transaction monitor implementation. It wouldn't have
helped at all in the recent case on pgsql-admin, though, as the format
of the global transaction suggests that it was issued through the JDBC
driver. Very likely none of the developer were aware either that there's
2PC in action. Nothing short of disabling PREPARE TRANSACTION in default
configuration will help with that.

Printing a warning similar to the "database \"%s\" must be vacuumed
within %u transactions" would be a good idea as well. That doesn't need
to be limited to prepared transactions; a warning whenever there's an
excessively old transaction active would be nice. People sometimes leave
behind a psql session with a transaction open when they go for vacation etc.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com