On Wed, Jul 08, 2020 at 10:11:28AM +0800, movead...@highgo.ca wrote: > Yes that's is the right way, I can see it's 'roident' in pg_replication_origin > catalog too. > What's your v6 patch based on, I can not apply it.
There is a conflict in catversion.h. If you wish to test the patch, please feel free to use the attached where I have updated the attribute name to roident. -- Michael
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d951b4a36f..c8b2baa490 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5946,12 +5946,20 @@
prorettype => 'timestamptz', proargtypes => 'xid',
prosrc => 'pg_xact_commit_timestamp' },
+{ oid => '8456',
+ descr => 'get commit timestamp and replication origin of a transaction',
+ proname => 'pg_xact_commit_timestamp_origin', provolatile => 'v',
+ prorettype => 'record', proargtypes => 'xid',
+ proallargtypes => '{xid,timestamptz,oid}', proargmodes => '{i,o,o}',
+ proargnames => '{xid,timestamp,roident}',
+ prosrc => 'pg_xact_commit_timestamp_origin' },
+
{ oid => '3583',
- descr => 'get transaction Id and commit timestamp of latest transaction commit',
+ descr => 'get transaction Id, commit timestamp and replication origin of latest transaction commit',
proname => 'pg_last_committed_xact', provolatile => 'v',
prorettype => 'record', proargtypes => '',
- proallargtypes => '{xid,timestamptz}', proargmodes => '{o,o}',
- proargnames => '{xid,timestamp}', prosrc => 'pg_last_committed_xact' },
+ proallargtypes => '{xid,timestamptz,oid}', proargmodes => '{o,o,o}',
+ proargnames => '{xid,timestamp,roident}', prosrc => 'pg_last_committed_xact' },
{ oid => '3537', descr => 'get identification of SQL object',
proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 9cdb136435..0771133868 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -417,28 +417,38 @@ pg_xact_commit_timestamp(PG_FUNCTION_ARGS)
}
+/*
+ * pg_last_committed_xact
+ *
+ * SQL-callable wrapper to obtain some information about the latest
+ * committed transaction: transaction ID, timestamp and replication
+ * origin.
+ */
Datum
pg_last_committed_xact(PG_FUNCTION_ARGS)
{
TransactionId xid;
+ RepOriginId nodeid;
TimestampTz ts;
- Datum values[2];
- bool nulls[2];
+ Datum values[3];
+ bool nulls[3];
TupleDesc tupdesc;
HeapTuple htup;
/* and construct a tuple with our data */
- xid = GetLatestCommitTsData(&ts, NULL);
+ xid = GetLatestCommitTsData(&ts, &nodeid);
/*
* Construct a tuple descriptor for the result row. This must match this
* function's pg_proc entry!
*/
- tupdesc = CreateTemplateTupleDesc(2);
+ tupdesc = CreateTemplateTupleDesc(3);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "xid",
XIDOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "timestamp",
TIMESTAMPTZOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "roident",
+ OIDOID, -1, 0);
tupdesc = BlessTupleDesc(tupdesc);
if (!TransactionIdIsNormal(xid))
@@ -452,6 +462,9 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
values[1] = TimestampTzGetDatum(ts);
nulls[1] = false;
+
+ values[2] = ObjectIdGetDatum(nodeid);
+ nulls[2] = false;
}
htup = heap_form_tuple(tupdesc, values, nulls);
@@ -459,6 +472,54 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(HeapTupleGetDatum(htup));
}
+/*
+ * pg_xact_commit_timestamp_origin
+ *
+ * SQL-callable wrapper to obtain commit timestamp and origin of a given
+ * transaction.
+ */
+Datum
+pg_xact_commit_timestamp_origin(PG_FUNCTION_ARGS)
+{
+ TransactionId xid = PG_GETARG_UINT32(0);
+ RepOriginId nodeid;
+ TimestampTz ts;
+ Datum values[2];
+ bool nulls[2];
+ TupleDesc tupdesc;
+ HeapTuple htup;
+ bool found;
+
+ found = TransactionIdGetCommitTsData(xid, &ts, &nodeid);
+
+ /*
+ * Construct a tuple descriptor for the result row. This must match this
+ * function's pg_proc entry!
+ */
+ tupdesc = CreateTemplateTupleDesc(2);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "timestamp",
+ TIMESTAMPTZOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "roident",
+ OIDOID, -1, 0);
+ tupdesc = BlessTupleDesc(tupdesc);
+
+ if (!found)
+ {
+ nulls[0] = true;
+ nulls[1] = true;
+ }
+ else
+ {
+ values[0] = TimestampTzGetDatum(ts);
+ nulls[0] = false;
+ values[1] = ObjectIdGetDatum(nodeid);
+ nulls[1] = false;
+ }
+
+ htup = heap_form_tuple(tupdesc, values, nulls);
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(htup));
+}
/*
* Number of shared CommitTS buffers.
diff --git a/src/test/modules/commit_ts/expected/commit_timestamp.out b/src/test/modules/commit_ts/expected/commit_timestamp.out
index 5b7783b58f..97588c9895 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp.out
@@ -39,9 +39,89 @@ SELECT pg_xact_commit_timestamp('2'::xid);
(1 row)
-SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now() FROM pg_last_committed_xact() x;
- ?column? | ?column? | ?column?
-----------+----------+----------
- t | t | t
+SELECT x.xid::text::bigint > 0 as xid_valid,
+ x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
+ roident
+ FROM pg_last_committed_xact() x;
+ xid_valid | ts_in_range | roident
+-----------+-------------+---------
+ t | t | 0
+(1 row)
+
+-- Test non-normal transaction ids.
+SELECT * FROM pg_xact_commit_timestamp_origin(NULL); -- ok, NULL
+ timestamp | roident
+-----------+---------
+ |
+(1 row)
+
+SELECT * FROM pg_xact_commit_timestamp_origin('0'::xid); -- error
+ERROR: cannot retrieve commit timestamp for transaction 0
+SELECT * FROM pg_xact_commit_timestamp_origin('1'::xid); -- ok, NULL
+ timestamp | roident
+-----------+---------
+ |
+(1 row)
+
+SELECT * FROM pg_xact_commit_timestamp_origin('2'::xid); -- ok, NULL
+ timestamp | roident
+-----------+---------
+ |
+(1 row)
+
+-- Test transaction without replication origin
+SELECT txid_current() as txid_no_origin \gset
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_last_committed_xact() x;
+ ts_in_range | roident
+-------------+---------
+ t | 0
+(1 row)
+
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
+ ts_in_range | roident
+-------------+---------
+ t | 0
+(1 row)
+
+-- Test transaction with replication origin
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+ pg_replication_origin_create
+------------------------------
+ 1
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+ pg_replication_origin_session_setup
+-------------------------------------
+
+(1 row)
+
+SELECT txid_current() as txid_with_origin \gset
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_last_committed_xact() x;
+ ts_in_range | roident
+-------------+---------
+ t | 1
+(1 row)
+
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_xact_commit_timestamp_origin(:'txid_with_origin') x;
+ ts_in_range | roident
+-------------+---------
+ t | 1
+(1 row)
+
+SELECT pg_replication_origin_session_reset();
+ pg_replication_origin_session_reset
+-------------------------------------
+
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin');
+ pg_replication_origin_drop
+----------------------------
+
(1 row)
diff --git a/src/test/modules/commit_ts/expected/commit_timestamp_1.out b/src/test/modules/commit_ts/expected/commit_timestamp_1.out
index c10b0abc2b..84fb3b835b 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp_1.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp_1.out
@@ -34,6 +34,74 @@ SELECT pg_xact_commit_timestamp('2'::xid);
(1 row)
-SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now() FROM pg_last_committed_xact() x;
+SELECT x.xid::text::bigint > 0 as xid_valid,
+ x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
+ roident
+ FROM pg_last_committed_xact() x;
ERROR: could not get commit timestamp data
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
+-- Test non-normal transaction ids.
+SELECT * FROM pg_xact_commit_timestamp_origin(NULL); -- ok, NULL
+ timestamp | roident
+-----------+---------
+ |
+(1 row)
+
+SELECT * FROM pg_xact_commit_timestamp_origin('0'::xid); -- error
+ERROR: cannot retrieve commit timestamp for transaction 0
+SELECT * FROM pg_xact_commit_timestamp_origin('1'::xid); -- ok, NULL
+ timestamp | roident
+-----------+---------
+ |
+(1 row)
+
+SELECT * FROM pg_xact_commit_timestamp_origin('2'::xid); -- ok, NULL
+ timestamp | roident
+-----------+---------
+ |
+(1 row)
+
+-- Test transaction without replication origin
+SELECT txid_current() as txid_no_origin \gset
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_last_committed_xact() x;
+ERROR: could not get commit timestamp data
+HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
+ERROR: could not get commit timestamp data
+HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
+-- Test transaction with replication origin
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+ pg_replication_origin_create
+------------------------------
+ 1
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+ pg_replication_origin_session_setup
+-------------------------------------
+
+(1 row)
+
+SELECT txid_current() as txid_with_origin \gset
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_last_committed_xact() x;
+ERROR: could not get commit timestamp data
+HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_xact_commit_timestamp_origin(:'txid_with_origin') x;
+ERROR: could not get commit timestamp data
+HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT pg_replication_origin_session_reset();
+ pg_replication_origin_session_reset
+-------------------------------------
+
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin');
+ pg_replication_origin_drop
+----------------------------
+
+(1 row)
+
diff --git a/src/test/modules/commit_ts/sql/commit_timestamp.sql b/src/test/modules/commit_ts/sql/commit_timestamp.sql
index 4e041a5347..d4620317db 100644
--- a/src/test/modules/commit_ts/sql/commit_timestamp.sql
+++ b/src/test/modules/commit_ts/sql/commit_timestamp.sql
@@ -21,4 +21,32 @@ SELECT pg_xact_commit_timestamp('0'::xid);
SELECT pg_xact_commit_timestamp('1'::xid);
SELECT pg_xact_commit_timestamp('2'::xid);
-SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now() FROM pg_last_committed_xact() x;
+SELECT x.xid::text::bigint > 0 as xid_valid,
+ x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
+ roident
+ FROM pg_last_committed_xact() x;
+
+-- Test non-normal transaction ids.
+SELECT * FROM pg_xact_commit_timestamp_origin(NULL); -- ok, NULL
+SELECT * FROM pg_xact_commit_timestamp_origin('0'::xid); -- error
+SELECT * FROM pg_xact_commit_timestamp_origin('1'::xid); -- ok, NULL
+SELECT * FROM pg_xact_commit_timestamp_origin('2'::xid); -- ok, NULL
+
+-- Test transaction without replication origin
+SELECT txid_current() as txid_no_origin \gset
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_last_committed_xact() x;
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
+
+-- Test transaction with replication origin
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+SELECT txid_current() as txid_with_origin \gset
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_last_committed_xact() x;
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, roident
+ FROM pg_xact_commit_timestamp_origin(:'txid_with_origin') x;
+
+SELECT pg_replication_origin_session_reset();
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin');
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f065856535..59e577fa70 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23397,6 +23397,21 @@ SELECT collation for ('foo' COLLATE "de_DE");
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_xact_commit_timestamp_origin</primary>
+ </indexterm>
+ <function>pg_xact_commit_timestamp_origin</function> ( <type>xid</type> )
+ <returnvalue>record</returnvalue>
+ ( <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
+ <parameter>Oid</parameter> <type>roident</type>)
+ </para>
+ <para>
+ Returns the commit timestamp and replication origin of a transaction.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -23405,11 +23420,12 @@ SELECT collation for ('foo' COLLATE "de_DE");
<function>pg_last_committed_xact</function> ()
<returnvalue>record</returnvalue>
( <parameter>xid</parameter> <type>xid</type>,
- <parameter>timestamp</parameter> <type>timestamp with time zone</type> )
+ <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
+ <parameter>Oid</parameter> <type>roident</type> )
</para>
<para>
- Returns the transaction ID and commit timestamp of the latest
- committed transaction.
+ Returns the transaction ID, commit timestamp and replication origin
+ of the latest committed transaction.
</para></entry>
</row>
</tbody>
signature.asc
Description: PGP signature
