Skip to content

Commit 857ee8e

Browse files
robertmhaasrobertmhaas
committed
Add a txid_status function.
If your connection to the database server is lost while a COMMIT is in progress, it may be difficult to figure out whether the COMMIT was successful or not. This function will tell you, provided that you don't wait too long to ask. It may be useful in other situations, too. Craig Ringer, reviewed by Simon Riggs and by me Discussion: http://postgr.es/m/CAMsr+YHQiWNEi0daCTboS40T+V5s_+dst3PYv_8v2wNVH+Xx4g@mail.gmail.com
1 parent 42b4b0b commit 857ee8e

File tree

7 files changed

+314
-1
lines changed

7 files changed

+314
-1
lines changed

‎doc/src/sgml/func.sgml

+27
Original file line numberDiff line numberDiff line change
@@ -17523,6 +17523,10 @@ SELECT collation for ('foo' COLLATE "de_DE");
1752317523
<primary>txid_visible_in_snapshot</primary>
1752417524
</indexterm>
1752517525

17526+
<indexterm>
17527+
<primary>txid_status</primary>
17528+
</indexterm>
17529+
1752617530
<para>
1752717531
The functions shown in <xref linkend="functions-txid-snapshot">
1752817532
provide server transaction information in an exportable form. The main
@@ -17573,6 +17577,11 @@ SELECT collation for ('foo' COLLATE "de_DE");
1757317577
<entry><type>boolean</type></entry>
1757417578
<entry>is transaction ID visible in snapshot? (do not use with subtransaction ids)</entry>
1757517579
</row>
17580+
<row>
17581+
<entry><literal><function>txid_status(<parameter>bigint</parameter>)</function></literal></entry>
17582+
<entry><type>txid_status</type></entry>
17583+
<entry>report the status of the given xact - <literal>committed</literal>, <literal>aborted</literal>, <literal>in progress</literal>, or NULL if the txid is too old</entry>
17584+
</row>
1757617585
</tbody>
1757717586
</tgroup>
1757817587
</table>
@@ -17642,6 +17651,24 @@ SELECT collation for ('foo' COLLATE "de_DE");
1764217651
<literal>xmin=10, xmax=20, xip_list=10, 14, 15</literal>.
1764317652
</para>
1764417653

17654+
<para>
17655+
<function>txid_status(bigint)</> reports the commit status of a recent
17656+
transaction. Applications may use it to determine whether a transaction
17657+
committed or aborted when the application and database server become
17658+
disconnected while a <literal>COMMIT</literal> is in progress.
17659+
The status of a transaction will be reported as either
17660+
<literal>in progress</>,
17661+
<literal>committed</>, or <literal>aborted</>, provided that the
17662+
transaction is recent enough that the system retains the commit status
17663+
of that transaction. If is old enough that no references to that
17664+
transaction survive in the system and the commit status information has
17665+
been discarded, this function will return NULL. Note that prepared
17666+
transactions are reported as <literal>in progress</>; applications must
17667+
check <link
17668+
linkend="view-pg-prepared-xacts"><literal>pg_prepared_xacts</></> if they
17669+
need to determine whether the txid is a prepared transaction.
17670+
</para>
17671+
1764517672
<para>
1764617673
The functions shown in <xref linkend="functions-commit-timestamp">
1764717674
provide information about transactions that have been already committed.

‎src/backend/utils/adt/txid.c

+132
Original file line numberDiff line numberDiff line change
@@ -21,13 +21,15 @@
2121

2222
#include "postgres.h"
2323

24+
#include "access/clog.h"
2425
#include "access/transam.h"
2526
#include "access/xact.h"
2627
#include "access/xlog.h"
2728
#include "funcapi.h"
2829
#include "miscadmin.h"
2930
#include "libpq/pqformat.h"
3031
#include "postmaster/postmaster.h"
32+
#include "storage/lwlock.h"
3133
#include "utils/builtins.h"
3234
#include "utils/memutils.h"
3335
#include "utils/snapmgr.h"
@@ -92,6 +94,70 @@ load_xid_epoch(TxidEpoch *state)
9294
GetNextXidAndEpoch(&state->last_xid, &state->epoch);
9395
}
9496

97+
/*
98+
* Helper to get a TransactionId from a 64-bit xid with wraparound detection.
99+
*
100+
* It is an ERROR if the xid is in the future. Otherwise, returns true if
101+
* the transaction is still new enough that we can determine whether it
102+
* committed and false otherwise. If *extracted_xid is not NULL, it is set
103+
* to the low 32 bits of the transaction ID (i.e. the actual XID, without the
104+
* epoch).
105+
*
106+
* The caller must hold CLogTruncationLock since it's dealing with arbitrary
107+
* XIDs, and must continue to hold it until it's done with any clog lookups
108+
* relating to those XIDs.
109+
*/
110+
static bool
111+
TransactionIdInRecentPast(uint64 xid_with_epoch, TransactionId *extracted_xid)
112+
{
113+
uint32 xid_epoch = (uint32) (xid_with_epoch >> 32);
114+
TransactionId xid = (TransactionId) xid_with_epoch;
115+
uint32 now_epoch;
116+
TransactionId now_epoch_last_xid;
117+
118+
GetNextXidAndEpoch(&now_epoch_last_xid, &now_epoch);
119+
120+
if (extracted_xid != NULL)
121+
*extracted_xid = xid;
122+
123+
if (!TransactionIdIsValid(xid))
124+
return false;
125+
126+
/* For non-normal transaction IDs, we can ignore the epoch. */
127+
if (!TransactionIdIsNormal(xid))
128+
return true;
129+
130+
/* If the transaction ID is in the future, throw an error. */
131+
if (xid_epoch > now_epoch
132+
|| (xid_epoch == now_epoch && xid > now_epoch_last_xid))
133+
ereport(ERROR,
134+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
135+
errmsg("transaction ID " UINT64_FORMAT " is in the future",
136+
xid_with_epoch)));
137+
138+
/*
139+
* ShmemVariableCache->oldestClogXid is protected by CLogTruncationLock,
140+
* but we don't acquire that lock here. Instead, we require the caller to
141+
* acquire it, because the caller is presumably going to look up the
142+
* returned XID. If we took and released the lock within this function, a
143+
* CLOG truncation could occur before the caller finished with the XID.
144+
*/
145+
Assert(LWLockHeldByMe(CLogTruncationLock));
146+
147+
/*
148+
* If the transaction ID has wrapped around, it's definitely too old to
149+
* determine the commit status. Otherwise, we can compare it to
150+
* ShmemVariableCache->oldestClogXid to determine whether the relevant CLOG
151+
* entry is guaranteed to still exist.
152+
*/
153+
if (xid_epoch + 1 < now_epoch
154+
|| (xid_epoch + 1 == now_epoch && xid < now_epoch_last_xid)
155+
|| TransactionIdPrecedes(xid, ShmemVariableCache->oldestClogXid))
156+
return false;
157+
158+
return true;
159+
}
160+
95161
/*
96162
* do a TransactionId -> txid conversion for an XID near the given epoch
97163
*/
@@ -354,6 +420,9 @@ parse_snapshot(const char *str)
354420
*
355421
* Return the current toplevel transaction ID as TXID
356422
* If the current transaction does not have one, one is assigned.
423+
*
424+
* This value has the epoch as the high 32 bits and the 32-bit xid
425+
* as the low 32 bits.
357426
*/
358427
Datum
359428
txid_current(PG_FUNCTION_ARGS)
@@ -658,3 +727,66 @@ txid_snapshot_xip(PG_FUNCTION_ARGS)
658727
SRF_RETURN_DONE(fctx);
659728
}
660729
}
730+
731+
/*
732+
* Report the status of a recent transaction ID, or null for wrapped,
733+
* truncated away or otherwise too old XIDs.
734+
*
735+
* The passed epoch-qualified xid is treated as a normal xid, not a
736+
* multixact id.
737+
*
738+
* If it points to a committed subxact the result is the subxact status even
739+
* though the parent xact may still be in progress or may have aborted.
740+
*/
741+
Datum
742+
txid_status(PG_FUNCTION_ARGS)
743+
{
744+
const char *status;
745+
uint64 xid_with_epoch = PG_GETARG_INT64(0);
746+
TransactionId xid;
747+
748+
/*
749+
* We must protect against concurrent truncation of clog entries to avoid
750+
* an I/O error on SLRU lookup.
751+
*/
752+
LWLockAcquire(CLogTruncationLock, LW_SHARED);
753+
if (TransactionIdInRecentPast(xid_with_epoch, &xid))
754+
{
755+
Assert(TransactionIdIsValid(xid));
756+
757+
if (TransactionIdIsCurrentTransactionId(xid))
758+
status = gettext_noop("in progress");
759+
else if (TransactionIdDidCommit(xid))
760+
status = gettext_noop("committed");
761+
else if (TransactionIdDidAbort(xid))
762+
status = gettext_noop("aborted");
763+
else
764+
{
765+
/*
766+
* The xact is not marked as either committed or aborted in clog.
767+
*
768+
* It could be a transaction that ended without updating clog or
769+
* writing an abort record due to a crash. We can safely assume
770+
* it's aborted if it isn't committed and is older than our
771+
* snapshot xmin.
772+
*
773+
* Otherwise it must be in-progress (or have been at the time
774+
* we checked commit/abort status).
775+
*/
776+
if (TransactionIdPrecedes(xid, GetActiveSnapshot()->xmin))
777+
status = gettext_noop("aborted");
778+
else
779+
status = gettext_noop("in progress");
780+
}
781+
}
782+
else
783+
{
784+
status = NULL;
785+
}
786+
LWLockRelease(CLogTruncationLock);
787+
788+
if (status == NULL)
789+
PG_RETURN_NULL();
790+
else
791+
PG_RETURN_TEXT_P(cstring_to_text(status));
792+
}

‎src/include/catalog/catversion.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201703231
56+
#define CATALOG_VERSION_NO 201703241
5757

5858
#endif

‎src/include/catalog/pg_proc.h

+2
Original file line numberDiff line numberDiff line change
@@ -4978,6 +4978,8 @@ DATA(insert OID = 2947 ( txid_snapshot_xip PGNSP PGUID 12 1 50 0 0 f f f f t
49784978
DESCR("get set of in-progress txids in snapshot");
49794979
DATA(insert OID = 2948 ( txid_visible_in_snapshot PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "20 2970" _null_ _null_ _null_ _null_ _null_ txid_visible_in_snapshot _null_ _null_ _null_ ));
49804980
DESCR("is txid visible in snapshot?");
4981+
DATA(insert OID = 3360 ( txid_status PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 25 "20" _null_ _null_ _null_ _null_ _null_ txid_status _null_ _null_ _null_ ));
4982+
DESCR("commit status of transaction");
49814983

49824984
/* record comparison using normal comparison rules */
49834985
DATA(insert OID = 2981 ( record_eq PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "2249 2249" _null_ _null_ _null_ _null_ _null_ record_eq _null_ _null_ _null_ ));

‎src/test/recovery/t/011_crash_recovery.pl

+46
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
#
2+
# Tests relating to PostgreSQL crash recovery and redo
3+
#
4+
use strict;
5+
use warnings;
6+
use PostgresNode;
7+
use TestLib;
8+
use Test::More tests => 3;
9+
10+
my $node = get_new_node('master');
11+
$node->init(allows_streaming => 1);
12+
$node->start;
13+
14+
my ($stdin, $stdout, $stderr) = ('', '', '');
15+
16+
# Ensure that txid_status reports 'aborted' for xacts
17+
# that were in-progress during crash. To do that, we need
18+
# an xact to be in-progress when we crash and we need to know
19+
# its xid.
20+
my $tx = IPC::Run::start(
21+
['psql', '-qAt', '-v', 'ON_ERROR_STOP=1', '-f', '-', '-d', $node->connstr('postgres')],
22+
'<', \$stdin, '>', \$stdout, '2>', \$stderr);
23+
$stdin .= q[
24+
BEGIN;
25+
CREATE TABLE mine(x integer);
26+
SELECT txid_current();
27+
];
28+
$tx->pump until $stdout =~ /[[:digit:]]+[\r\n]$/;
29+
30+
# Status should be in-progress
31+
my $xid = $stdout;
32+
chomp($xid);
33+
34+
is($node->safe_psql('postgres', qq[SELECT txid_status('$xid');]), 'in progress', 'own xid is in-progres');
35+
36+
# Crash and restart the postmaster
37+
$node->stop('immediate');
38+
$node->start;
39+
40+
# Make sure we really got a new xid
41+
cmp_ok($node->safe_psql('postgres', 'SELECT txid_current()'), '>', $xid,
42+
'new xid after restart is greater');
43+
# and make sure we show the in-progress xact as aborted
44+
is($node->safe_psql('postgres', qq[SELECT txid_status('$xid');]), 'aborted', 'xid is aborted after crash');
45+
46+
$tx->kill_kill;

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

+68
Original file line numberDiff line numberDiff line change
@@ -254,3 +254,71 @@ SELECT txid_current_if_assigned() IS NOT DISTINCT FROM BIGINT :'txid_current';
254254
(1 row)
255255

256256
COMMIT;
257+
-- test xid status functions
258+
BEGIN;
259+
SELECT txid_current() AS committed \gset
260+
COMMIT;
261+
BEGIN;
262+
SELECT txid_current() AS rolledback \gset
263+
ROLLBACK;
264+
BEGIN;
265+
SELECT txid_current() AS inprogress \gset
266+
SELECT txid_status(:committed) AS committed;
267+
committed
268+
-----------
269+
committed
270+
(1 row)
271+
272+
SELECT txid_status(:rolledback) AS rolledback;
273+
rolledback
274+
------------
275+
aborted
276+
(1 row)
277+
278+
SELECT txid_status(:inprogress) AS inprogress;
279+
inprogress
280+
-------------
281+
in progress
282+
(1 row)
283+
284+
SELECT txid_status(1); -- BootstrapTransactionId is always committed
285+
txid_status
286+
-------------
287+
committed
288+
(1 row)
289+
290+
SELECT txid_status(2); -- FrozenTransactionId is always committed
291+
txid_status
292+
-------------
293+
committed
294+
(1 row)
295+
296+
SELECT txid_status(3); -- in regress testing FirstNormalTransactionId will always be behind oldestXmin
297+
txid_status
298+
-------------
299+
300+
(1 row)
301+
302+
COMMIT;
303+
BEGIN;
304+
CREATE FUNCTION test_future_xid_status(bigint)
305+
RETURNS void
306+
LANGUAGE plpgsql
307+
AS
308+
$$
309+
BEGIN
310+
PERFORM txid_status($1);
311+
RAISE EXCEPTION 'didn''t ERROR at xid in the future as expected';
312+
EXCEPTION
313+
WHEN invalid_parameter_value THEN
314+
RAISE NOTICE 'Got expected error for xid in the future';
315+
END;
316+
$$;
317+
SELECT test_future_xid_status(:inprogress + 10000);
318+
NOTICE: Got expected error for xid in the future
319+
test_future_xid_status
320+
------------------------
321+
322+
(1 row)
323+
324+
ROLLBACK;

‎src/test/regress/sql/txid.sql

+38
Original file line numberDiff line numberDiff line change
@@ -59,3 +59,41 @@ SELECT txid_current_if_assigned() IS NULL;
5959
SELECT txid_current() \gset
6060
SELECT txid_current_if_assigned() IS NOT DISTINCT FROM BIGINT :'txid_current';
6161
COMMIT;
62+
63+
-- test xid status functions
64+
BEGIN;
65+
SELECT txid_current() AS committed \gset
66+
COMMIT;
67+
68+
BEGIN;
69+
SELECT txid_current() AS rolledback \gset
70+
ROLLBACK;
71+
72+
BEGIN;
73+
SELECT txid_current() AS inprogress \gset
74+
75+
SELECT txid_status(:committed) AS committed;
76+
SELECT txid_status(:rolledback) AS rolledback;
77+
SELECT txid_status(:inprogress) AS inprogress;
78+
SELECT txid_status(1); -- BootstrapTransactionId is always committed
79+
SELECT txid_status(2); -- FrozenTransactionId is always committed
80+
SELECT txid_status(3); -- in regress testing FirstNormalTransactionId will always be behind oldestXmin
81+
82+
COMMIT;
83+
84+
BEGIN;
85+
CREATE FUNCTION test_future_xid_status(bigint)
86+
RETURNS void
87+
LANGUAGE plpgsql
88+
AS
89+
$$
90+
BEGIN
91+
PERFORM txid_status($1);
92+
RAISE EXCEPTION 'didn''t ERROR at xid in the future as expected';
93+
EXCEPTION
94+
WHEN invalid_parameter_value THEN
95+
RAISE NOTICE 'Got expected error for xid in the future';
96+
END;
97+
$$;
98+
SELECT test_future_xid_status(:inprogress + 10000);
99+
ROLLBACK;

0 commit comments

Comments
 (0)