🌐 AI搜索 & 代理 主页
Skip to content

Commit b1e48bb

Browse files
michaelpqmichaelpq
committed
Include replication origins in SQL functions for commit timestamp
This includes two changes: - Addition of a new function pg_xact_commit_timestamp_origin() able, for a given transaction ID, to return the commit timestamp and replication origin of this transaction. An equivalent function existed in pglogical. - Addition of the replication origin to pg_last_committed_xact(). The commit timestamp manager includes already APIs able to return the replication origin of a transaction on top of its commit timestamp, but the code paths for replication origins were never stressed as those functions have never looked for a replication origin, and the SQL functions available have never included this information since their introduction in 73c986a. While on it, refactor a test of modules/commit_ts/ to use tstzrange() to check that a transaction timestamp is within the wanted range, making the test a bit easier to read. Bump catalog version. Author: Movead Li Reviewed-by: Madan Kumar, Michael Paquier Discussion: https://postgr.es/m/2020051116430836450630@highgo.ca
1 parent cd22d3c commit b1e48bb

File tree

7 files changed

+295
-18
lines changed

7 files changed

+295
-18
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -23397,6 +23397,21 @@ SELECT collation for ('foo' COLLATE "de_DE");
2339723397
</para></entry>
2339823398
</row>
2339923399

23400+
<row>
23401+
<entry role="func_table_entry"><para role="func_signature">
23402+
<indexterm>
23403+
<primary>pg_xact_commit_timestamp_origin</primary>
23404+
</indexterm>
23405+
<function>pg_xact_commit_timestamp_origin</function> ( <type>xid</type> )
23406+
<returnvalue>record</returnvalue>
23407+
( <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
23408+
<parameter>roident</parameter> <type>oid</type>)
23409+
</para>
23410+
<para>
23411+
Returns the commit timestamp and replication origin of a transaction.
23412+
</para></entry>
23413+
</row>
23414+
2340023415
<row>
2340123416
<entry role="func_table_entry"><para role="func_signature">
2340223417
<indexterm>
@@ -23405,11 +23420,12 @@ SELECT collation for ('foo' COLLATE "de_DE");
2340523420
<function>pg_last_committed_xact</function> ()
2340623421
<returnvalue>record</returnvalue>
2340723422
( <parameter>xid</parameter> <type>xid</type>,
23408-
<parameter>timestamp</parameter> <type>timestamp with time zone</type> )
23423+
<parameter>timestamp</parameter> <type>timestamp with time zone</type>,
23424+
<parameter>roident</parameter> <type>oid</type> )
2340923425
</para>
2341023426
<para>
23411-
Returns the transaction ID and commit timestamp of the latest
23412-
committed transaction.
23427+
Returns the transaction ID, commit timestamp and replication origin
23428+
of the latest committed transaction.
2341323429
</para></entry>
2341423430
</row>
2341523431
</tbody>

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

Lines changed: 66 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -361,7 +361,7 @@ TransactionIdGetCommitTsData(TransactionId xid, TimestampTz *ts,
361361
* is concerned, anyway; it's up to the caller to ensure the value is useful
362362
* for its purposes.)
363363
*
364-
* ts and extra are filled with the corresponding data; they can be passed
364+
* ts and nodeid are filled with the corresponding data; they can be passed
365365
* as NULL if not wanted.
366366
*/
367367
TransactionId
@@ -417,28 +417,38 @@ pg_xact_commit_timestamp(PG_FUNCTION_ARGS)
417417
}
418418

419419

420+
/*
421+
* pg_last_committed_xact
422+
*
423+
* SQL-callable wrapper to obtain some information about the latest
424+
* committed transaction: transaction ID, timestamp and replication
425+
* origin.
426+
*/
420427
Datum
421428
pg_last_committed_xact(PG_FUNCTION_ARGS)
422429
{
423430
TransactionId xid;
431+
RepOriginId nodeid;
424432
TimestampTz ts;
425-
Datum values[2];
426-
bool nulls[2];
433+
Datum values[3];
434+
bool nulls[3];
427435
TupleDesc tupdesc;
428436
HeapTuple htup;
429437

430438
/* and construct a tuple with our data */
431-
xid = GetLatestCommitTsData(&ts, NULL);
439+
xid = GetLatestCommitTsData(&ts, &nodeid);
432440

433441
/*
434442
* Construct a tuple descriptor for the result row. This must match this
435443
* function's pg_proc entry!
436444
*/
437-
tupdesc = CreateTemplateTupleDesc(2);
445+
tupdesc = CreateTemplateTupleDesc(3);
438446
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "xid",
439447
XIDOID, -1, 0);
440448
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "timestamp",
441449
TIMESTAMPTZOID, -1, 0);
450+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "roident",
451+
OIDOID, -1, 0);
442452
tupdesc = BlessTupleDesc(tupdesc);
443453

444454
if (!TransactionIdIsNormal(xid))
@@ -452,13 +462,64 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
452462

453463
values[1] = TimestampTzGetDatum(ts);
454464
nulls[1] = false;
465+
466+
values[2] = ObjectIdGetDatum((Oid) nodeid);
467+
nulls[2] = false;
455468
}
456469

457470
htup = heap_form_tuple(tupdesc, values, nulls);
458471

459472
PG_RETURN_DATUM(HeapTupleGetDatum(htup));
460473
}
461474

475+
/*
476+
* pg_xact_commit_timestamp_origin
477+
*
478+
* SQL-callable wrapper to obtain commit timestamp and replication origin
479+
* of a given transaction.
480+
*/
481+
Datum
482+
pg_xact_commit_timestamp_origin(PG_FUNCTION_ARGS)
483+
{
484+
TransactionId xid = PG_GETARG_UINT32(0);
485+
RepOriginId nodeid;
486+
TimestampTz ts;
487+
Datum values[2];
488+
bool nulls[2];
489+
TupleDesc tupdesc;
490+
HeapTuple htup;
491+
bool found;
492+
493+
found = TransactionIdGetCommitTsData(xid, &ts, &nodeid);
494+
495+
/*
496+
* Construct a tuple descriptor for the result row. This must match this
497+
* function's pg_proc entry!
498+
*/
499+
tupdesc = CreateTemplateTupleDesc(2);
500+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "timestamp",
501+
TIMESTAMPTZOID, -1, 0);
502+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "roident",
503+
OIDOID, -1, 0);
504+
tupdesc = BlessTupleDesc(tupdesc);
505+
506+
if (!found)
507+
{
508+
memset(nulls, true, sizeof(nulls));
509+
}
510+
else
511+
{
512+
values[0] = TimestampTzGetDatum(ts);
513+
nulls[0] = false;
514+
515+
values[1] = ObjectIdGetDatum((Oid) nodeid);
516+
nulls[1] = false;
517+
}
518+
519+
htup = heap_form_tuple(tupdesc, values, nulls);
520+
521+
PG_RETURN_DATUM(HeapTupleGetDatum(htup));
522+
}
462523

463524
/*
464525
* Number of shared CommitTS buffers.

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202007072
56+
#define CATALOG_VERSION_NO 202007121
5757

5858
#endif

‎src/include/catalog/pg_proc.dat‎

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5946,12 +5946,21 @@
59465946
prorettype => 'timestamptz', proargtypes => 'xid',
59475947
prosrc => 'pg_xact_commit_timestamp' },
59485948

5949+
{ oid => '8456',
5950+
descr => 'get commit timestamp and replication origin of a transaction',
5951+
proname => 'pg_xact_commit_timestamp_origin', provolatile => 'v',
5952+
prorettype => 'record', proargtypes => 'xid',
5953+
proallargtypes => '{xid,timestamptz,oid}', proargmodes => '{i,o,o}',
5954+
proargnames => '{xid,timestamp,roident}',
5955+
prosrc => 'pg_xact_commit_timestamp_origin' },
5956+
59495957
{ oid => '3583',
5950-
descr => 'get transaction Id and commit timestamp of latest transaction commit',
5958+
descr => 'get transaction Id, commit timestamp and replication origin of latest transaction commit',
59515959
proname => 'pg_last_committed_xact', provolatile => 'v',
59525960
prorettype => 'record', proargtypes => '',
5953-
proallargtypes => '{xid,timestamptz}', proargmodes => '{o,o}',
5954-
proargnames => '{xid,timestamp}', prosrc => 'pg_last_committed_xact' },
5961+
proallargtypes => '{xid,timestamptz,oid}', proargmodes => '{o,o,o}',
5962+
proargnames => '{xid,timestamp,roident}',
5963+
prosrc => 'pg_last_committed_xact' },
59555964

59565965
{ oid => '3537', descr => 'get identification of SQL object',
59575966
proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',

‎src/test/modules/commit_ts/expected/commit_timestamp.out‎

Lines changed: 89 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -39,9 +39,94 @@ SELECT pg_xact_commit_timestamp('2'::xid);
3939

4040
(1 row)
4141

42-
SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now() FROM pg_last_committed_xact() x;
43-
?column? | ?column? | ?column?
44-
----------+----------+----------
45-
t | t | t
42+
SELECT x.xid::text::bigint > 0 as xid_valid,
43+
x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
44+
roident != 0 AS valid_roident
45+
FROM pg_last_committed_xact() x;
46+
xid_valid | ts_in_range | valid_roident
47+
-----------+-------------+---------------
48+
t | t | f
49+
(1 row)
50+
51+
-- Test non-normal transaction ids.
52+
SELECT * FROM pg_xact_commit_timestamp_origin(NULL); -- ok, NULL
53+
timestamp | roident
54+
-----------+---------
55+
|
56+
(1 row)
57+
58+
SELECT * FROM pg_xact_commit_timestamp_origin('0'::xid); -- error
59+
ERROR: cannot retrieve commit timestamp for transaction 0
60+
SELECT * FROM pg_xact_commit_timestamp_origin('1'::xid); -- ok, NULL
61+
timestamp | roident
62+
-----------+---------
63+
|
64+
(1 row)
65+
66+
SELECT * FROM pg_xact_commit_timestamp_origin('2'::xid); -- ok, NULL
67+
timestamp | roident
68+
-----------+---------
69+
|
70+
(1 row)
71+
72+
-- Test transaction without replication origin
73+
SELECT txid_current() as txid_no_origin \gset
74+
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
75+
roident != 0 AS valid_roident
76+
FROM pg_last_committed_xact() x;
77+
ts_in_range | valid_roident
78+
-------------+---------------
79+
t | f
80+
(1 row)
81+
82+
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
83+
roident != 0 AS valid_roident
84+
FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
85+
ts_in_range | valid_roident
86+
-------------+---------------
87+
t | f
88+
(1 row)
89+
90+
-- Test transaction with replication origin
91+
SELECT pg_replication_origin_create('test_commit_ts: get_origin') != 0
92+
AS valid_roident;
93+
valid_roident
94+
---------------
95+
t
96+
(1 row)
97+
98+
SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
99+
pg_replication_origin_session_setup
100+
-------------------------------------
101+
102+
(1 row)
103+
104+
SELECT txid_current() as txid_with_origin \gset
105+
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, r.roname
106+
FROM pg_last_committed_xact() x, pg_replication_origin r
107+
WHERE r.roident = x.roident;
108+
ts_in_range | roname
109+
-------------+----------------------------
110+
t | test_commit_ts: get_origin
111+
(1 row)
112+
113+
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, r.roname
114+
FROM pg_xact_commit_timestamp_origin(:'txid_with_origin') x, pg_replication_origin r
115+
WHERE r.roident = x.roident;
116+
ts_in_range | roname
117+
-------------+----------------------------
118+
t | test_commit_ts: get_origin
119+
(1 row)
120+
121+
SELECT pg_replication_origin_session_reset();
122+
pg_replication_origin_session_reset
123+
-------------------------------------
124+
125+
(1 row)
126+
127+
SELECT pg_replication_origin_drop('test_commit_ts: get_origin');
128+
pg_replication_origin_drop
129+
----------------------------
130+
46131
(1 row)
47132

‎src/test/modules/commit_ts/expected/commit_timestamp_1.out‎

Lines changed: 74 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,79 @@ SELECT pg_xact_commit_timestamp('2'::xid);
3434

3535
(1 row)
3636

37-
SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now() FROM pg_last_committed_xact() x;
37+
SELECT x.xid::text::bigint > 0 as xid_valid,
38+
x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
39+
roident != 0 AS valid_roident
40+
FROM pg_last_committed_xact() x;
3841
ERROR: could not get commit timestamp data
3942
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
43+
-- Test non-normal transaction ids.
44+
SELECT * FROM pg_xact_commit_timestamp_origin(NULL); -- ok, NULL
45+
timestamp | roident
46+
-----------+---------
47+
|
48+
(1 row)
49+
50+
SELECT * FROM pg_xact_commit_timestamp_origin('0'::xid); -- error
51+
ERROR: cannot retrieve commit timestamp for transaction 0
52+
SELECT * FROM pg_xact_commit_timestamp_origin('1'::xid); -- ok, NULL
53+
timestamp | roident
54+
-----------+---------
55+
|
56+
(1 row)
57+
58+
SELECT * FROM pg_xact_commit_timestamp_origin('2'::xid); -- ok, NULL
59+
timestamp | roident
60+
-----------+---------
61+
|
62+
(1 row)
63+
64+
-- Test transaction without replication origin
65+
SELECT txid_current() as txid_no_origin \gset
66+
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
67+
roident != 0 AS valid_roident
68+
FROM pg_last_committed_xact() x;
69+
ERROR: could not get commit timestamp data
70+
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
71+
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
72+
roident != 0 AS valid_roident
73+
FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
74+
ERROR: could not get commit timestamp data
75+
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
76+
-- Test transaction with replication origin
77+
SELECT pg_replication_origin_create('test_commit_ts: get_origin') != 0
78+
AS valid_roident;
79+
valid_roident
80+
---------------
81+
t
82+
(1 row)
83+
84+
SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
85+
pg_replication_origin_session_setup
86+
-------------------------------------
87+
88+
(1 row)
89+
90+
SELECT txid_current() as txid_with_origin \gset
91+
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, r.roname
92+
FROM pg_last_committed_xact() x, pg_replication_origin r
93+
WHERE r.roident = x.roident;
94+
ERROR: could not get commit timestamp data
95+
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
96+
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, r.roname
97+
FROM pg_xact_commit_timestamp_origin(:'txid_with_origin') x, pg_replication_origin r
98+
WHERE r.roident = x.roident;
99+
ERROR: could not get commit timestamp data
100+
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
101+
SELECT pg_replication_origin_session_reset();
102+
pg_replication_origin_session_reset
103+
-------------------------------------
104+
105+
(1 row)
106+
107+
SELECT pg_replication_origin_drop('test_commit_ts: get_origin');
108+
pg_replication_origin_drop
109+
----------------------------
110+
111+
(1 row)
112+

0 commit comments

Comments
 (0)