Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8d4f2ec

Browse files
committed
Change the default value of max_prepared_transactions to zero, and add
documentation warnings against setting it nonzero unless active use ofprepared transactions is intended and a suitable transaction manager has beeninstalled. This should help to prevent the type of scenario we've seenseveral times now where a prepared transaction is forgotten and eventuallycauses severe maintenance problems (or even anti-wraparound shutdown).The only real reason we had the default be nonzero in the first place was tosupport regression testing of the feature. To still be able to do that,tweak pg_regress to force a nonzero value during "make check". Since wecannot force a nonzero value in "make installcheck", add a variant regressiontest "expected" file that shows the results that will be obtained whenmax_prepared_transactions is zero.Also, extend the HINT messages for transaction wraparound warnings to mentionthe possibility that old prepared transactions are causing the problem.All per today's discussion.
1 parentbae8102 commit8d4f2ec

File tree

10 files changed

+305
-53
lines changed

10 files changed

+305
-53
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.214 2009/04/02 22:44:10 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.215 2009/04/23 00:23:45 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -785,18 +785,18 @@ SET ENABLE_SEQSCAN TO OFF;
785785
<quote>prepared</> state simultaneously (see <xref
786786
linkend="sql-prepare-transaction"
787787
endterm="sql-prepare-transaction-title">).
788-
Setting this parameter to zero disables the prepared-transaction
789-
feature.
790-
The default is five transactions.
788+
Setting this parameter to zero (which is the default)
789+
disables the prepared-transaction feature.
791790
This parameter can only be set at server start.
792791
</para>
793792

794793
<para>
795-
If you are not using prepared transactions, this parameter may as
796-
well be set to zero. If you are using them, you will probably
797-
want <varname>max_prepared_transactions</varname> to be at least
798-
as large as <xref linkend="guc-max-connections">, to avoid unwanted
799-
failures at the prepare step.
794+
If you are not planning to use prepared transactions, this parameter
795+
should be set to zero to prevent accidental creation of prepared
796+
transactions. If you are using prepared transactions, you will
797+
probably want <varname>max_prepared_transactions</varname> to be at
798+
least as large as <xref linkend="guc-max-connections">, so that every
799+
session can have a prepared transaction pending.
800800
</para>
801801

802802
<para>

‎doc/src/sgml/ref/prepare_transaction.sgml

Lines changed: 26 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/prepare_transaction.sgml,v 1.8 2008/11/14 10:22:47 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/prepare_transaction.sgml,v 1.9 2009/04/23 00:23:45 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -30,7 +30,7 @@ PREPARE TRANSACTION <replaceable class="PARAMETER">transaction_id</replaceable>
3030

3131
<para>
3232
<command>PREPARE TRANSACTION</command> prepares the current transaction
33-
for two-phase commit. After this command, the transaction is no longer
33+
for two-phase commit. After this command, the transaction is no longer
3434
associated with the current session; instead, its state is fully stored on
3535
disk, and there is a very high probability that it can be committed
3636
successfully, even if a database crash occurs before the commit is
@@ -100,7 +100,7 @@ PREPARE TRANSACTION <replaceable class="PARAMETER">transaction_id</replaceable>
100100
If the transaction modified any run-time parameters with <command>SET</>
101101
(without the <literal>LOCAL</> option),
102102
those effects persist after <command>PREPARE TRANSACTION</>, and will not
103-
be affected by any later <command>COMMIT PREPARED</command> or
103+
be affected by any later <command>COMMIT PREPARED</command> or
104104
<command>ROLLBACK PREPARED</command>. Thus, in this one respect
105105
<command>PREPARE TRANSACTION</> acts more like <command>COMMIT</> than
106106
<command>ROLLBACK</>.
@@ -112,34 +112,36 @@ PREPARE TRANSACTION <replaceable class="PARAMETER">transaction_id</replaceable>
112112
system view.
113113
</para>
114114

115-
<para>
116-
From a performance standpoint, it is unwise to leave transactions in
117-
the prepared state for a long time: this will for instance interfere with
118-
the ability of <command>VACUUM</> to reclaim storage. Keep in mind also
119-
that the transaction continues to hold whatever locks it held.
120-
The intended
121-
usage of the feature is that a prepared transaction will normally be
122-
committed or rolled back as soon as an external transaction manager
123-
has verified that other databases are also prepared to commit.
124-
</para>
125-
126-
<para>
127-
If you make any serious use of prepared transactions, you will probably
128-
want to increase the value of <xref
129-
linkend="guc-max-prepared-transactions">, as the default setting is
130-
quite small (to avoid wasting resources for those who don't use it).
131-
It is recommendable to make it at least equal to
132-
<xref linkend="guc-max-connections">, so that every session can have
133-
a prepared transaction pending.
134-
</para>
115+
<caution>
116+
<para>
117+
It is unwise to leave transactions in the prepared state for a long time.
118+
This will interfere with the ability of <command>VACUUM</> to reclaim
119+
storage, and in extreme cases could cause the database to shut down
120+
to prevent transaction ID wraparound (see <xref
121+
linkend="vacuum-for-wraparound">). Keep in mind also that the transaction
122+
continues to hold whatever locks it held. The intended usage of the
123+
feature is that a prepared transaction will normally be committed or
124+
rolled back as soon as an external transaction manager has verified that
125+
other databases are also prepared to commit.
126+
</para>
127+
128+
<para>
129+
If you have not set up an external transaction manager to track prepared
130+
transactions and ensure they get closed out promptly, it is best to keep
131+
the prepared-transaction feature disabled by setting
132+
<xref linkend="guc-max-prepared-transactions"> to zero. This will
133+
prevent accidental creation of prepared transactions that might then
134+
be forgotten and eventually cause problems.
135+
</para>
136+
</caution>
135137
</refsect1>
136138

137139
<refsect1 id="sql-prepare-transaction-examples">
138140
<title id="sql-prepare-transaction-examples-title">Examples</title>
139141
<para>
140142
Prepare the current transaction for two-phase commit, using
141143
<literal>foobar</> as the transaction identifier:
142-
144+
143145
<programlisting>
144146
PREPARE TRANSACTION 'foobar';
145147
</programlisting>

‎src/backend/access/transam/twophase.c

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
*$PostgreSQL: pgsql/src/backend/access/transam/twophase.c,v 1.51 2009/01/01 17:23:36 momjian Exp $
10+
*$PostgreSQL: pgsql/src/backend/access/transam/twophase.c,v 1.52 2009/04/23 00:23:45 tgl Exp $
1111
*
1212
* NOTES
1313
*Each global transaction is associated with a global transaction
@@ -68,7 +68,7 @@
6868
#defineTWOPHASE_DIR "pg_twophase"
6969

7070
/* GUC variable, can't be changed after startup */
71-
intmax_prepared_xacts=5;
71+
intmax_prepared_xacts=0;
7272

7373
/*
7474
* This struct describes one global transaction that is in prepared state
@@ -228,6 +228,13 @@ MarkAsPreparing(TransactionId xid, const char *gid,
228228
errmsg("transaction identifier \"%s\" is too long",
229229
gid)));
230230

231+
/* fail immediately if feature is disabled */
232+
if (max_prepared_xacts==0)
233+
ereport(ERROR,
234+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
235+
errmsg("prepared transactions are disabled"),
236+
errhint("Set max_prepared_transactions to a nonzero value.")));
237+
231238
LWLockAcquire(TwoPhaseStateLock,LW_EXCLUSIVE);
232239

233240
/*

‎src/backend/access/transam/varsup.c

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Copyright (c) 2000-2009, PostgreSQL Global Development Group
77
*
88
* IDENTIFICATION
9-
* $PostgreSQL: pgsql/src/backend/access/transam/varsup.c,v 1.83 2009/01/01 17:23:36 momjian Exp $
9+
* $PostgreSQL: pgsql/src/backend/access/transam/varsup.c,v 1.84 2009/04/23 00:23:45 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -86,14 +86,16 @@ GetNewTransactionId(bool isSubXact)
8686
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
8787
errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
8888
NameStr(ShmemVariableCache->limit_datname)),
89-
errhint("Stop the postmaster and use a standalone backend to vacuum database \"%s\".",
89+
errhint("Stop the postmaster and use a standalone backend to vacuum database \"%s\".\n"
90+
"You might also need to commit or roll back old prepared transactions.",
9091
NameStr(ShmemVariableCache->limit_datname))));
9192
elseif (TransactionIdFollowsOrEquals(xid,ShmemVariableCache->xidWarnLimit))
9293
ereport(WARNING,
9394
(errmsg("database \"%s\" must be vacuumed within %u transactions",
9495
NameStr(ShmemVariableCache->limit_datname),
9596
ShmemVariableCache->xidWrapLimit-xid),
96-
errhint("To avoid a database shutdown, execute a database-wide VACUUM in \"%s\".",
97+
errhint("To avoid a database shutdown, execute a database-wide VACUUM in \"%s\".\n"
98+
"You might also need to commit or roll back old prepared transactions.",
9799
NameStr(ShmemVariableCache->limit_datname))));
98100
}
99101

@@ -299,7 +301,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid,
299301
(errmsg("database \"%s\" must be vacuumed within %u transactions",
300302
NameStr(*oldest_datname),
301303
xidWrapLimit-curXid),
302-
errhint("To avoid a database shutdown, execute a database-wide VACUUM in \"%s\".",
304+
errhint("To avoid a database shutdown, execute a database-wide VACUUM in \"%s\".\n"
305+
"You might also need to commit or roll back old prepared transactions.",
303306
NameStr(*oldest_datname))));
304307
}
305308

‎src/backend/utils/misc/guc.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Written by Peter Eisentraut <peter_e@gmx.net>.
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.502 2009/04/0723:27:34 momjian Exp $
13+
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.503 2009/04/23 00:23:45 tgl Exp $
1414
*
1515
*--------------------------------------------------------------------
1616
*/
@@ -1504,7 +1504,7 @@ static struct config_int ConfigureNamesInt[] =
15041504
NULL
15051505
},
15061506
&max_prepared_xacts,
1507-
5,0,INT_MAX,NULL,NULL
1507+
0,0,INT_MAX /4,NULL,NULL
15081508
},
15091509

15101510
#ifdefLOCK_DEBUG

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -106,10 +106,12 @@
106106
#shared_buffers = 32MB# min 128kB
107107
# (change requires restart)
108108
#temp_buffers = 8MB# min 800kB
109-
#max_prepared_transactions =5#can be 0 or more
109+
#max_prepared_transactions =0#zero disables the feature
110110
# (change requires restart)
111111
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
112112
# per transaction slot, plus lock space (see max_locks_per_transaction).
113+
# It is not advisable to set max_prepared_transactions nonzero unless you
114+
# actively intend to use prepared transactions.
113115
#work_mem = 1MB# min 64kB
114116
#maintenance_work_mem = 16MB# min 1MB
115117
#max_stack_depth = 2MB# min 100kB

‎src/test/regress/expected/prepared_xacts.out

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -214,4 +214,6 @@ SELECT gid FROM pg_prepared_xacts;
214214

215215
-- Clean up
216216
DROP TABLE pxtest2;
217+
DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled
218+
ERROR: table "pxtest3" does not exist
217219
DROP TABLE pxtest4;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp