A patch for get origin from commit_ts.

Started by movead.li@highgo.caover 5 years ago31 messages
#1movead.li@highgo.ca
movead.li@highgo.ca
1 attachment(s)

Hello hackers,

I am researching about 'origin' in PostgreSQL, mainly it used in logical
decoding to filter transaction from non-local source. I notice that the
'origin' is stored in commit_ts so that I think we are possible to get 'origin'
of a transaction from commit_ts.

But I can not fond any code to get 'origin' from commit_ts, just like it is
producing data which nobody cares about. Can I know what's the purpose
of the 'origin' in commit_ts? Do you think we should add some support
to the careless data?

For example, I add a function to get 'origin' from commit_ts:
=======================================
postgres=# select pg_xact_commit_origin('490');
pg_xact_commit_origin
-----------------------
test_origin
(1 row)

postgres=# select pg_xact_commit_origin('491');
pg_xact_commit_origin
-----------------------
test_origin1
(1 row)

postgres=#
=======================================

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

Attachments:

get_origin_from_commit_ts.patchapplication/octet-stream; name=get_origin_from_commit_ts.patchDownload
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 630df672cc..85e03efe4e 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -416,6 +416,29 @@ pg_xact_commit_timestamp(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMPTZ(ts);
 }
 
+/*
+ * SQL-callable wrapper to obtain commit origin of a transaction
+ */
+Datum
+pg_xact_commit_origin(PG_FUNCTION_ARGS)
+{
+	TransactionId 	xid = PG_GETARG_UINT32(0);
+	RepOriginId		nodeid;
+	TimestampTz 	ts;
+	bool			found;
+	char	   		*roname = NULL;
+
+	found = TransactionIdGetCommitTsData(xid, &ts, &nodeid);
+
+	if (!found || (InvalidRepOriginId == nodeid))
+		PG_RETURN_NULL();
+
+	replorigin_by_oid(nodeid, false, &roname);
+
+	PG_RETURN_TEXT_P(cstring_to_text(roname));
+}
+
+
 
 Datum
 pg_last_committed_xact(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4bce3ad8de..5e67ace32d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5945,6 +5945,12 @@
   prorettype => 'timestamptz', proargtypes => 'xid',
   prosrc => 'pg_xact_commit_timestamp' },
 
+{ oid => '3435', descr => 'get commit origin of a transaction',
+  proname => 'pg_xact_commit_origin', provolatile => 'v',
+  prorettype => 'text', proargtypes => 'xid',
+  prosrc => 'pg_xact_commit_origin' },
+
+
 { oid => '3583',
   descr => 'get transaction Id and commit timestamp of latest transaction commit',
   proname => 'pg_last_committed_xact', provolatile => 'v',
#2Michael Paquier
michael@paquier.xyz
In reply to: movead.li@highgo.ca (#1)
Re: A patch for get origin from commit_ts.

On Mon, May 11, 2020 at 04:43:11PM +0800, movead.li@highgo.ca wrote:

But I can not fond any code to get 'origin' from commit_ts, just like it is
producing data which nobody cares about. Can I know what's the purpose
of the 'origin' in commit_ts? Do you think we should add some support
to the careless data?

I have not thought about this matter, but it seems to me that you
should add this patch to the upcoming commit fest for evaluation:
https://commitfest.postgresql.org/28/

This is going to take a couple of months though as the main focus
lately is the stability of 13.
--
Michael

#3movead.li@highgo.ca
movead.li@highgo.ca
In reply to: movead.li@highgo.ca (#1)
1 attachment(s)
Re: A patch for get origin from commit_ts.

I have not thought about this matter, but it seems to me that you
should add this patch to the upcoming commit fest for evaluation:
https://commitfest.postgresql.org/28/

Thanks.

I think about it more detailed, and find it's better to show the 'roident'
other than 'roname'. Because an old 'roident' value will be used
immediately after dropped, and a new patch attached with test case
and documentation.

============================================
SELECT pg_xact_commit_origin('490');
pg_xact_commit_origin
-----------------------
1
(1 row)

SELECT pg_xact_commit_origin('491');
pg_xact_commit_origin
-----------------------
2
(1 row)
============================================

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

Attachments:

get_origin_from_commit_ts_v2.patchapplication/octet-stream; name=get_origin_from_commit_ts_v2.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d9b3598977..91e61337b7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23368,6 +23368,19 @@ 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_origin</primary>
+        </indexterm>
+        <function>pg_xact_commit_origin</function> ()
+        <returnvalue>RepOriginId</returnvalue>
+       </para>
+       <para>
+         Returns the commit origin of a transaction.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 630df672cc..113a81e80a 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -416,6 +416,26 @@ pg_xact_commit_timestamp(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMPTZ(ts);
 }
 
+/*
+ * SQL-callable wrapper to obtain commit origin of a transaction
+ */
+Datum
+pg_xact_commit_origin(PG_FUNCTION_ARGS)
+{
+	TransactionId 	xid = PG_GETARG_UINT32(0);
+	RepOriginId		nodeid;
+	TimestampTz 	ts;
+	bool			found;
+
+	found = TransactionIdGetCommitTsData(xid, &ts, &nodeid);
+
+	if (!found || (InvalidRepOriginId == nodeid))
+		PG_RETURN_NULL();
+
+	PG_RETURN_OID((Oid)nodeid);
+}
+
+
 
 Datum
 pg_last_committed_xact(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4bce3ad8de..95d2ad3797 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5945,6 +5945,12 @@
   prorettype => 'timestamptz', proargtypes => 'xid',
   prosrc => 'pg_xact_commit_timestamp' },
 
+{ oid => '3435', descr => 'get commit origin of a transaction',
+  proname => 'pg_xact_commit_origin', provolatile => 'v',
+  prorettype => 'oid', proargtypes => 'xid',
+  prosrc => 'pg_xact_commit_origin' },
+
+
 { oid => '3583',
   descr => 'get transaction Id and commit timestamp of latest transaction commit',
   proname => 'pg_last_committed_xact', provolatile => 'v',
diff --git a/src/test/modules/commit_ts/expected/commit_timestamp.out b/src/test/modules/commit_ts/expected/commit_timestamp.out
index 5b7783b58f..a50e6420fb 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp.out
@@ -45,3 +45,96 @@ SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timest
  t        | t        | t
 (1 row)
 
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_1');
+ pg_replication_origin_create 
+------------------------------
+                            1
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_2');
+ pg_replication_origin_create 
+------------------------------
+                            2
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_3');
+ pg_replication_origin_create 
+------------------------------
+                            3
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_1');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+ pg_xact_commit_origin 
+-----------------------
+                     1
+(1 row)
+
+SELECT pg_replication_origin_session_reset();
+ pg_replication_origin_session_reset 
+-------------------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_2');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+ pg_xact_commit_origin 
+-----------------------
+                     2
+(1 row)
+
+SELECT pg_replication_origin_session_reset();
+ pg_replication_origin_session_reset 
+-------------------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_3');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+ pg_xact_commit_origin 
+-----------------------
+                     3
+(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_1');
+ pg_replication_origin_drop 
+----------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_2');
+ pg_replication_origin_drop 
+----------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_3');
+ 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..db5d4ebcc0 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp_1.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp_1.out
@@ -37,3 +37,87 @@ 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;
 ERROR:  could not get commit timestamp data
 HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_1');
+ pg_replication_origin_create 
+------------------------------
+                            1
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_2');
+ pg_replication_origin_create 
+------------------------------
+                            2
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_3');
+ pg_replication_origin_create 
+------------------------------
+                            3
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_1');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+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_session_setup('test_commit_ts: get_origin_2');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+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_session_setup('test_commit_ts: get_origin_3');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+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_1');
+ pg_replication_origin_drop 
+----------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_2');
+ pg_replication_origin_drop 
+----------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_3');
+ 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..ba7ccfda42 100644
--- a/src/test/modules/commit_ts/sql/commit_timestamp.sql
+++ b/src/test/modules/commit_ts/sql/commit_timestamp.sql
@@ -22,3 +22,27 @@ 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 pg_replication_origin_create('test_commit_ts: get_origin_1');
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_2');
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_3');
+
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_1');
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+SELECT pg_replication_origin_session_reset();
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_2');
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+SELECT pg_replication_origin_session_reset();
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_3');
+SELECT txid_current() as txid \gset
+SELECT pg_xact_commit_origin(:'txid');
+SELECT pg_replication_origin_session_reset();
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_1');
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_2');
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_3');
#4Madan Kumar
madankumar1993@gmail.com
In reply to: movead.li@highgo.ca (#3)
Re: A patch for get origin from commit_ts.

Hello hackers,

We already have pg_xact_commit_timestamp() that returns the timestamp of
the commit. It may be better to have one single function returning both
timestamp and origin for a given transaction ID.

A second thing is that TransactionIdGetCommitTsData() was introdued in
core(73c986add). It has only one caller pg_xact_commit_timestamp() which
passes RepOriginId as NULL, making last argument to the
TransactionIdGetCommitTsData() a dead code in core.

Quick code search shows that it is getting used by pglogical (caller:
https://sources.debian.org/src/pglogical/2.3.2-1/pglogical_conflict.c/?hl=509#L509).
CCing Craig Ringer and Petr Jelinek for the inputs.

Warm Regards,
Madan Kumar K
"There is no Elevator to Success. You have to take the Stairs"

#5Michael Paquier
michael@paquier.xyz
In reply to: Madan Kumar (#4)
Re: A patch for get origin from commit_ts.

On Mon, Jun 29, 2020 at 06:17:27PM -0700, Madan Kumar wrote:

We already have pg_xact_commit_timestamp() that returns the timestamp of
the commit. It may be better to have one single function returning both
timestamp and origin for a given transaction ID.

A second thing is that TransactionIdGetCommitTsData() was introdued in
core(73c986add). It has only one caller pg_xact_commit_timestamp() which
passes RepOriginId as NULL, making last argument to the
TransactionIdGetCommitTsData() a dead code in core.

Quick code search shows that it is getting used by pglogical (caller:
https://sources.debian.org/src/pglogical/2.3.2-1/pglogical_conflict.c/?hl=509#L509).
CCing Craig Ringer and Petr Jelinek for the inputs.

Another question that has popped up when doing this review is what
would be the use-case of adding this information at SQL level knowing
that logical replication exists since 10? Having dead code in the
backend tree is not a good idea of course, so we can also have as
argument to simplify TransactionIdGetCommitTsData(). Now, pglogical
has pglogical_xact_commit_timestamp_origin() to get the replication
origin with its own function so providing an extending equivalent
returning one row with two fields would be nice for pglogical so as
this function is not necessary. As mentioned by Madan, the portion of
the code using TransactionIdGetCommitTsData() relies on it for
conflicts of updates (the first win, last win logic at quick glance).

I am adding Peter E in CC for an opinion, the last commits of
pglogical are from him.
--
Michael

#6movead.li@highgo.ca
movead.li@highgo.ca
In reply to: movead.li@highgo.ca (#1)
Re: A patch for get origin from commit_ts.

A second thing is that TransactionIdGetCommitTsData() was introdued in
core(73c986add). It has only one caller pg_xact_commit_timestamp() which
passes RepOriginId as NULL, making last argument to the
TransactionIdGetCommitTsData() a dead code in core.

Quick code search shows that it is getting used by pglogical (caller:
https://sources.debian.org/src/pglogical/2.3.2-1/pglogical_conflict.c/?hl=509#L509).
CCing Craig Ringer and Petr Jelinek for the inputs.

Another question that has popped up when doing this review is what
would be the use-case of adding this information at SQL level knowing
that logical replication exists since 10? Having dead code in the
backend tree is not a good idea of course, so we can also have as
argument to simplify TransactionIdGetCommitTsData(). Now, pglogical
has pglogical_xact_commit_timestamp_origin() to get the replication
origin with its own function so providing an extending equivalent
returning one row with two fields would be nice for pglogical so as
this function is not necessary. As mentioned by Madan, the portion of
the code using TransactionIdGetCommitTsData() relies on it for
conflicts of updates (the first win, last win logic at quick glance).

Thanks for the explanation, the origin in commit_ts seems useless, I am just
want to know why it appears there. It's ok to close this issue if we do not
want to touch it now.

And I am more interest in origin in wal, if data from a logical replicate or a
manual origin then many wal records will get a 'RepOriginId', 'RepOriginId'
in 'xact' wal record may help to do some filter, the other same dead code
too. So can you help me to understand why or the historical reason for that?

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

#7Simon Riggs
simon@2ndquadrant.com
In reply to: Madan Kumar (#4)
Re: A patch for get origin from commit_ts.

On Tue, 30 Jun 2020 at 02:17, Madan Kumar <madankumar1993@gmail.com> wrote:

We already have pg_xact_commit_timestamp() that returns the timestamp of
the commit.

Yes, pg_xact_commit_origin() is a good name for an additional function. +1
for this.

It may be better to have one single function returning both
timestamp and origin for a given transaction ID.

No need to change existing APIs.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/&gt;
Mission Critical Databases

#8Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Michael Paquier (#5)
Re: A patch for get origin from commit_ts.

On 2020-Jun-30, Michael Paquier wrote:

Another question that has popped up when doing this review is what
would be the use-case of adding this information at SQL level knowing
that logical replication exists since 10?

Logical replication in core is a far cry from a fully featured
replication solution. Kindly do not claim that we can now remove
features just because in-core logical replication does not use them;
this argument is ignoring the fact that we're still a long way from
developing actually powerful logical replication capabilities.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

#9Noname
michael@paquier.xyz
In reply to: Alvaro Herrera (#8)
Re: A patch for get origin from commit_ts.

On Tue, Jun 30, 2020 at 02:32:47PM -0400, Alvaro Herrera wrote:

On 2020-Jun-30, Michael Paquier wrote:

Another question that has popped up when doing this review is what
would be the use-case of adding this information at SQL level knowing
that logical replication exists since 10?

Logical replication in core is a far cry from a fully featured
replication solution. Kindly do not claim that we can now remove
features just because in-core logical replication does not use them;
this argument is ignoring the fact that we're still a long way from
developing actually powerful logical replication capabilities.

Thanks for the feedback. If that sounded aggressive in some way, this
was not my intention, so my apologies for that. Now, I have to admit
that I am worried to see in core code that stands as dead without any
actual way to test it directly. Somebody hacking this code cannot be
sure if they are breaking it or not, except if they test it with
pglogical. So it is good to close the gap here. It also brings a
second point IMO, could the documentation be improved to describe more
use-cases where these functions would be useful? The documentation
gap is not a problem this patch has to deal with, though.
--
Michael

#10Noname
michael@paquier.xyz
In reply to: Simon Riggs (#7)
Re: A patch for get origin from commit_ts.

On Tue, Jun 30, 2020 at 01:58:17PM +0100, Simon Riggs wrote:

On Tue, 30 Jun 2020 at 02:17, Madan Kumar <madankumar1993@gmail.com> wrote:

It may be better to have one single function returning both
timestamp and origin for a given transaction ID.

No need to change existing APIs.

Adding a new function able to return both fields at the same time does
not imply that we'd remove the original one, it just implies that we
would be able to retrieve both fields with a single call of
TransactionIdGetCommitTsData(), saving from an extra CommitTsSLRULock
taken, etc. That's actually what pglogical does with
its pglogical_xact_commit_timestamp_origin() in
pglogical_functions.c. So adding one function able to return one
tuple with the two fields, without removing the existing
pg_xact_commit_timestamp() makes the most sense, no?
--
Michael

#11Simon Riggs
simon@2ndquadrant.com
In reply to: Noname (#10)
Re: A patch for get origin from commit_ts.

On Thu, 2 Jul 2020 at 02:58, <michael@paquier.xyz> wrote:

On Tue, Jun 30, 2020 at 01:58:17PM +0100, Simon Riggs wrote:

On Tue, 30 Jun 2020 at 02:17, Madan Kumar <madankumar1993@gmail.com>

wrote:

It may be better to have one single function returning both
timestamp and origin for a given transaction ID.

No need to change existing APIs.

Adding a new function able to return both fields at the same time does
not imply that we'd remove the original one, it just implies that we
would be able to retrieve both fields with a single call of
TransactionIdGetCommitTsData(), saving from an extra CommitTsSLRULock
taken, etc. That's actually what pglogical does with
its pglogical_xact_commit_timestamp_origin() in
pglogical_functions.c. So adding one function able to return one
tuple with the two fields, without removing the existing
pg_xact_commit_timestamp() makes the most sense, no?

OK

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/&gt;
Mission Critical Databases

#12Petr Jelinek
petr@2ndquadrant.com
In reply to: Noname (#10)
Re: A patch for get origin from commit_ts.

On 02/07/2020 03:58, michael@paquier.xyz wrote:

On Tue, Jun 30, 2020 at 01:58:17PM +0100, Simon Riggs wrote:

On Tue, 30 Jun 2020 at 02:17, Madan Kumar <madankumar1993@gmail.com> wrote:

It may be better to have one single function returning both
timestamp and origin for a given transaction ID.

No need to change existing APIs.

Adding a new function able to return both fields at the same time does
not imply that we'd remove the original one, it just implies that we
would be able to retrieve both fields with a single call of
TransactionIdGetCommitTsData(), saving from an extra CommitTsSLRULock
taken, etc. That's actually what pglogical does with
its pglogical_xact_commit_timestamp_origin() in
pglogical_functions.c. So adding one function able to return one
tuple with the two fields, without removing the existing
pg_xact_commit_timestamp() makes the most sense, no?

Agreed, sounds reasonable.

I also (I suspect like �lvaro) parsed your original message as wanting
to remove origin from the record completely.

--
Petr Jelinek
2ndQuadrant - PostgreSQL Solutions for the Enterprise
https://www.2ndQuadrant.com/

#13Michael Paquier
michael@paquier.xyz
In reply to: Petr Jelinek (#12)
Re: A patch for get origin from commit_ts.

On Thu, Jul 02, 2020 at 10:12:02AM +0200, Petr Jelinek wrote:

On 02/07/2020 03:58, michael@paquier.xyz wrote:

Adding a new function able to return both fields at the same time does
not imply that we'd remove the original one, it just implies that we
would be able to retrieve both fields with a single call of
TransactionIdGetCommitTsData(), saving from an extra CommitTsSLRULock
taken, etc. That's actually what pglogical does with
its pglogical_xact_commit_timestamp_origin() in
pglogical_functions.c. So adding one function able to return one
tuple with the two fields, without removing the existing
pg_xact_commit_timestamp() makes the most sense, no?

Agreed, sounds reasonable.

Thanks. Movead, please note that the patch is waiting on author?
Could you send an update if you think that those changes make sense?
--
Michael

#14Movead Li
movead.li@highgo.ca
In reply to: Michael Paquier (#13)
Re: A patch for get origin from commit_ts.

Thanks. Movead, please note that the patch is waiting on author?

Could you send an update if you think that those changes make sense?

Thanks for approval the issue, I will send a patch at Monday. 
Regards,

Highgo Software (Canada/China/Pakistan)

URL : http://www.highgo.ca/

EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

#15movead.li@highgo.ca
movead.li@highgo.ca
In reply to: movead.li@highgo.ca (#1)
1 attachment(s)
Re: A patch for get origin from commit_ts.

Thanks. Movead, please note that the patch is waiting on author?
Could you send an update if you think that those changes make sense?

I make a patch as Michael Paquier described that use a new function to
return transactionid and origin, and I add a origin version to
pg_last_committed_xact() too, now it looks like below:

============================================
postgres=# SELECT txid_current() as txid \gset
postgres=# SELECT * FROM pg_xact_commit_timestamp_origin(:'txid');
timestamp | origin
-------------------------------------+--------
2020-07-04 17:52:10.199623+08 | 1
(1 row)

postgres=# SELECT * FROM pg_last_committed_xact_with_origin();
xid | timestamp | origin
-----+------------------------------------+--------
506 | 2020-07-04 17:52:10.199623+08 | 1
(1 row)

postgres=#
============================================

---
Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

Attachments:

get_origin_from_commit_ts_v3.patchapplication/octet-stream; name=get_origin_from_commit_ts_v3.patchDownload
From: ca5e93f769e1bf5e90d080cfcc8c0368ef649a7c
Author: movead <movead.li@highgo.ca>
Date: Sat Jul 4 16:57:11 2020 +0800
Subject: add functions to get origin information from commit_ts

---
 doc/src/sgml/func.sgml                                     |  32 ++++++++++++++++++++++++++
 src/backend/access/transam/commit_ts.c                     |  89 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat                            |  13 +++++++++++
 src/test/modules/commit_ts/expected/commit_timestamp.out   | 111 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/test/modules/commit_ts/expected/commit_timestamp_1.out |  93 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/test/modules/commit_ts/sql/commit_timestamp.sql        |  27 ++++++++++++++++++++++
 6 files changed, 365 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f065856535..8563eaeca2 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> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
+         <parameter>Oid</parameter> <type>origin</type>)
+       </para>
+       <para>
+         Returns the commit timestamp and origin of a transaction.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -23412,6 +23427,23 @@ SELECT collation for ('foo' COLLATE "de_DE");
         committed transaction.
        </para></entry>
       </row>
+
+       <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_last_committed_xact_with_origin</primary>
+        </indexterm>
+        <function>pg_last_committed_xact_with_origin</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>xid</parameter> <type>xid</type>,
+        <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
+        <parameter>Oid</parameter> <type>origin</type> )
+       </para>
+       <para>
+        Returns the transaction ID, commit timestamp and origin of
+        the latest committed transaction.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 9cdb136435..042a20bde2 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -459,6 +459,95 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-callable wrapper to obtain commit timestamp and origin of
+ * a 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);
+
+	if (!found)
+		PG_RETURN_NULL();
+
+	/*
+	 * 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, "origin",
+					   OIDOID, -1, 0);
+	tupdesc = BlessTupleDesc(tupdesc);
+
+	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));
+}
+
+Datum
+pg_last_committed_xact_with_origin(PG_FUNCTION_ARGS)
+{
+	TransactionId		xid;
+	TimestampTz			ts;
+	RepOriginId			nodeid;
+	Datum				values[3];
+	bool				nulls[3];
+	TupleDesc			tupdesc;
+	HeapTuple			htup;
+
+	/* and construct a tuple with our data */
+	xid = GetLatestCommitTsData(&ts, &nodeid);
+
+	/*
+	 * Construct a tuple descriptor for the result row.  This must match this
+	 * function's pg_proc entry!
+	 */
+	tupdesc = CreateTemplateTupleDesc(3);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "xid",
+					   XIDOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "timestamp",
+					   TIMESTAMPTZOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 3, "origin",
+					   OIDOID, -1, 0);
+	tupdesc = BlessTupleDesc(tupdesc);
+
+	if (!TransactionIdIsNormal(xid))
+	{
+		memset(nulls, true, sizeof(nulls));
+	}
+	else
+	{
+		values[0] = TransactionIdGetDatum(xid);
+		nulls[0] = false;
+
+		values[1] = TimestampTzGetDatum(ts);
+		nulls[1] = false;
+
+		values[2] = ObjectIdGetDatum(nodeid);
+		nulls[2] = false;
+	}
+
+	htup = heap_form_tuple(tupdesc, values, nulls);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
+}
 
 /*
  * Number of shared CommitTS buffers.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 38295aca48..662a04db30 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5946,6 +5946,12 @@
   prorettype => 'timestamptz', proargtypes => 'xid',
   prosrc => 'pg_xact_commit_timestamp' },
 
+{ oid => '4179', descr => 'get commit 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,origin}', prosrc => 'pg_xact_commit_timestamp_origin' },
+
 { oid => '3583',
   descr => 'get transaction Id and commit timestamp of latest transaction commit',
   proname => 'pg_last_committed_xact', provolatile => 'v',
@@ -5953,6 +5959,13 @@
   proallargtypes => '{xid,timestamptz}', proargmodes => '{o,o}',
   proargnames => '{xid,timestamp}', prosrc => 'pg_last_committed_xact' },
 
+{ oid => '4180',
+  descr => 'get transaction Id, commit timestamp and origin of latest transaction commit',
+  proname => 'pg_last_committed_xact_with_origin', provolatile => 'v',
+  prorettype => 'record', proargtypes => '',
+  proallargtypes => '{xid,timestamptz,oid}', proargmodes => '{o,o,o}',
+  proargnames => '{xid,timestamp,origin}', prosrc => 'pg_last_committed_xact_with_origin' },
+
 { oid => '3537', descr => 'get identification of SQL object',
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
diff --git a/src/test/modules/commit_ts/expected/commit_timestamp.out b/src/test/modules/commit_ts/expected/commit_timestamp.out
index 5b7783b58f..d83927ec2b 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp.out
@@ -45,3 +45,114 @@ SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timest
  t        | t        | t
 (1 row)
 
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_1');
+ pg_replication_origin_create 
+------------------------------
+                            1
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_2');
+ pg_replication_origin_create 
+------------------------------
+                            2
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_3');
+ pg_replication_origin_create 
+------------------------------
+                            3
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_1');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+ origin 
+--------
+      1
+(1 row)
+
+SELECT origin FROM pg_last_committed_xact_with_origin();
+ origin 
+--------
+      1
+(1 row)
+
+SELECT pg_replication_origin_session_reset();
+ pg_replication_origin_session_reset 
+-------------------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_2');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+ origin 
+--------
+      2
+(1 row)
+
+SELECT origin FROM pg_last_committed_xact_with_origin();
+ origin 
+--------
+      2
+(1 row)
+
+SELECT pg_replication_origin_session_reset();
+ pg_replication_origin_session_reset 
+-------------------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_3');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+ origin 
+--------
+      3
+(1 row)
+
+SELECT origin FROM pg_last_committed_xact_with_origin();
+ origin 
+--------
+      3
+(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_1');
+ pg_replication_origin_drop 
+----------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_2');
+ pg_replication_origin_drop 
+----------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_3');
+ 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..9e8704f4c3 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp_1.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp_1.out
@@ -37,3 +37,96 @@ 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;
 ERROR:  could not get commit timestamp data
 HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_1');
+ pg_replication_origin_create 
+------------------------------
+                            1
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_2');
+ pg_replication_origin_create 
+------------------------------
+                            2
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_3');
+ pg_replication_origin_create 
+------------------------------
+                            3
+(1 row)
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_1');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+ERROR:  could not get commit timestamp data
+HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT origin FROM pg_last_committed_xact_with_origin();
+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_session_setup('test_commit_ts: get_origin_2');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+ERROR:  could not get commit timestamp data
+HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT origin FROM pg_last_committed_xact_with_origin();
+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_session_setup('test_commit_ts: get_origin_3');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+ERROR:  could not get commit timestamp data
+HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT origin FROM pg_last_committed_xact_with_origin();
+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_1');
+ pg_replication_origin_drop 
+----------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_2');
+ pg_replication_origin_drop 
+----------------------------
+ 
+(1 row)
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_3');
+ 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..96fc9df8d5 100644
--- a/src/test/modules/commit_ts/sql/commit_timestamp.sql
+++ b/src/test/modules/commit_ts/sql/commit_timestamp.sql
@@ -22,3 +22,30 @@ 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 pg_replication_origin_create('test_commit_ts: get_origin_1');
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_2');
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_3');
+
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_1');
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+SELECT origin FROM pg_last_committed_xact_with_origin();
+SELECT pg_replication_origin_session_reset();
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_2');
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+SELECT origin FROM pg_last_committed_xact_with_origin();
+SELECT pg_replication_origin_session_reset();
+
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin_3');
+SELECT txid_current() as txid \gset
+SELECT origin FROM  pg_xact_commit_timestamp_origin(:'txid');
+SELECT origin FROM pg_last_committed_xact_with_origin();
+SELECT pg_replication_origin_session_reset();
+
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_1');
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_2');
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin_3');
#16Michael Paquier
michael@paquier.xyz
In reply to: movead.li@highgo.ca (#15)
Re: A patch for get origin from commit_ts.

On Sat, Jul 04, 2020 at 06:01:28PM +0800, movead.li@highgo.ca wrote:

I make a patch as Michael Paquier described that use a new function to
return transactionid and origin, and I add a origin version to
pg_last_committed_xact() too, now it looks like below:

+SELECT pg_replication_origin_create('test_commit_ts: get_origin_1');
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_2');
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_3');

Why do you need three replication origins to test three times the same
pattern? Wouldn't one be enough and why don't you check after the
timestamp? I would also two extra tests: one with a NULL input and an
extra one where the data could not be found.

+   found = TransactionIdGetCommitTsData(xid, &ts, &nodeid);
+
+   if (!found)
+       PG_RETURN_NULL();

This part also looks incorrect to me, I think that you should still
return two tuples, both marked as NULL. You can do that just by
switching the nulls flags to true for the two values if nothing can be
found.
--
Michael

#17movead.li@highgo.ca
movead.li@highgo.ca
In reply to: movead.li@highgo.ca (#1)
1 attachment(s)
Re: A patch for get origin from commit_ts.
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_1');
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_2');
+SELECT pg_replication_origin_create('test_commit_ts: get_origin_3');

Why do you need three replication origins to test three times the same
pattern? Wouldn't one be enough and why don't you check after the
timestamp? I would also two extra tests: one with a NULL input and an
extra one where the data could not be found.

+   found = TransactionIdGetCommitTsData(xid, &ts, &nodeid);
+
+   if (!found)
+       PG_RETURN_NULL();

This part also looks incorrect to me, I think that you should still
return two tuples, both marked as NULL. You can do that just by
switching the nulls flags to true for the two values if nothing can be
found.

Thanks for the points and follow them, new patch attached.

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

Attachments:

get_origin_from_commit_ts_v4.patchapplication/octet-stream; name=get_origin_from_commit_ts_v4.patchDownload
From: ca5e93f769e1bf5e90d080cfcc8c0368ef649a7c
Author: movead <movead.li@highgo.ca>
Date:   Mon Jul 6 11:05:55 2020 +0800
Subject: add functions to get origin information from commit_ts

---
 doc/src/sgml/func.sgml                                     | 32 ++++++++++++++++++++++++++++++++
 src/backend/access/transam/commit_ts.c                     | 94 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat                            | 13 +++++++++++++
 src/test/modules/commit_ts/expected/commit_timestamp.out   | 78 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/test/modules/commit_ts/expected/commit_timestamp_1.out | 63 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/test/modules/commit_ts/sql/commit_timestamp.sql        | 30 ++++++++++++++++++++++++++++++
 6 files changed, 310 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f065856535..8563eaeca2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23400,6 +23400,21 @@ SELECT collation for ('foo' COLLATE "de_DE");
       <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> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
+         <parameter>Oid</parameter> <type>origin</type>)
+       </para>
+       <para>
+         Returns the commit timestamp and origin of a transaction.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
          <primary>pg_last_committed_xact</primary>
         </indexterm>
         <function>pg_last_committed_xact</function> ()
@@ -23412,6 +23427,23 @@ SELECT collation for ('foo' COLLATE "de_DE");
         committed transaction.
        </para></entry>
       </row>
+
+       <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_last_committed_xact_with_origin</primary>
+        </indexterm>
+        <function>pg_last_committed_xact_with_origin</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>xid</parameter> <type>xid</type>,
+        <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
+        <parameter>Oid</parameter> <type>origin</type> )
+       </para>
+       <para>
+        Returns the transaction ID, commit timestamp and origin of
+        the latest committed transaction.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 9cdb136435..3bb290fa0c 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -459,6 +459,100 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-callable wrapper to obtain commit timestamp and origin of
+ * a 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, "origin",
+					   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));
+}
+
+Datum
+pg_last_committed_xact_with_origin(PG_FUNCTION_ARGS)
+{
+	TransactionId		xid;
+	TimestampTz			ts;
+	RepOriginId			nodeid;
+	Datum				values[3];
+	bool				nulls[3];
+	TupleDesc			tupdesc;
+	HeapTuple			htup;
+
+	/* and construct a tuple with our data */
+	xid = GetLatestCommitTsData(&ts, &nodeid);
+
+	/*
+	 * Construct a tuple descriptor for the result row.  This must match this
+	 * function's pg_proc entry!
+	 */
+	tupdesc = CreateTemplateTupleDesc(3);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "xid",
+					   XIDOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "timestamp",
+					   TIMESTAMPTZOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 3, "origin",
+					   OIDOID, -1, 0);
+	tupdesc = BlessTupleDesc(tupdesc);
+
+	if (!TransactionIdIsNormal(xid))
+	{
+		memset(nulls, true, sizeof(nulls));
+	}
+	else
+	{
+		values[0] = TransactionIdGetDatum(xid);
+		nulls[0] = false;
+
+		values[1] = TimestampTzGetDatum(ts);
+		nulls[1] = false;
+
+		values[2] = ObjectIdGetDatum(nodeid);
+		nulls[2] = false;
+	}
+
+	htup = heap_form_tuple(tupdesc, values, nulls);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
+}
 
 /*
  * Number of shared CommitTS buffers.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 38295aca48..662a04db30 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5946,6 +5946,12 @@
   prorettype => 'timestamptz', proargtypes => 'xid',
   prosrc => 'pg_xact_commit_timestamp' },
 
+{ oid => '4179', descr => 'get commit 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,origin}', prosrc => 'pg_xact_commit_timestamp_origin' },
+
 { oid => '3583',
   descr => 'get transaction Id and commit timestamp of latest transaction commit',
   proname => 'pg_last_committed_xact', provolatile => 'v',
@@ -5953,6 +5959,13 @@
   proallargtypes => '{xid,timestamptz}', proargmodes => '{o,o}',
   proargnames => '{xid,timestamp}', prosrc => 'pg_last_committed_xact' },
 
+{ oid => '4180',
+  descr => 'get transaction Id, commit timestamp and origin of latest transaction commit',
+  proname => 'pg_last_committed_xact_with_origin', provolatile => 'v',
+  prorettype => 'record', proargtypes => '',
+  proallargtypes => '{xid,timestamptz,oid}', proargmodes => '{o,o,o}',
+  proargnames => '{xid,timestamp,origin}', prosrc => 'pg_last_committed_xact_with_origin' },
+
 { oid => '3537', descr => 'get identification of SQL object',
   proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
diff --git a/src/test/modules/commit_ts/expected/commit_timestamp.out b/src/test/modules/commit_ts/expected/commit_timestamp.out
index 5b7783b58f..4c481f9179 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp.out
@@ -45,3 +45,81 @@ SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timest
  t        | t        | t
 (1 row)
 
+-- Test non-normal transaction id.
+SELECT origin FROM  pg_xact_commit_timestamp_origin('0'::xid);
+ERROR:  cannot retrieve commit timestamp for transaction 0
+SELECT origin FROM  pg_xact_commit_timestamp_origin('1'::xid);
+ origin 
+--------
+       
+(1 row)
+
+SELECT origin FROM  pg_xact_commit_timestamp_origin('2'::xid);
+ origin 
+--------
+       
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+ pg_replication_origin_create 
+------------------------------
+                            1
+(1 row)
+
+-- Test transaction without origin
+SELECT txid_current() as txid_no_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
+ ?column? | ?column? | origin 
+----------+----------+--------
+ t        | t        |      0
+(1 row)
+
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact_with_origin() x;
+ ?column? | ?column? | ?column? | origin 
+----------+----------+----------+--------
+ t        | t        | t        |      0
+(1 row)
+
+-- Test transaction with origin
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid_set_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_set_origin') x;
+ ?column? | ?column? | origin 
+----------+----------+--------
+ t        | t        |      1
+(1 row)
+
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact_with_origin() x;
+ ?column? | ?column? | ?column? | origin 
+----------+----------+----------+--------
+ t        | t        | t        |      1
+(1 row)
+
+-- Test when it can not find the transaction
+SELECT * FROM pg_xact_commit_timestamp_origin((:'txid_set_origin'::text::int + 10)::text::xid) x;
+ timestamp | origin 
+-----------+--------
+           |       
+(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..72a0c2f61a 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp_1.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp_1.out
@@ -37,3 +37,66 @@ 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;
 ERROR:  could not get commit timestamp data
 HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+-- Test non-normal transaction id.
+SELECT origin FROM  pg_xact_commit_timestamp_origin('0'::xid);
+ERROR:  cannot retrieve commit timestamp for transaction 0
+SELECT origin FROM  pg_xact_commit_timestamp_origin('1'::xid);
+ origin 
+--------
+       
+(1 row)
+
+SELECT origin FROM  pg_xact_commit_timestamp_origin('2'::xid);
+ origin 
+--------
+       
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+ pg_replication_origin_create 
+------------------------------
+                            1
+(1 row)
+
+-- Test transaction without origin
+SELECT txid_current() as txid_no_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+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.
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact_with_origin() x;
+ERROR:  could not get commit timestamp data
+HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+-- Test transaction with origin
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid_set_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_set_origin') x;
+ERROR:  could not get commit timestamp data
+HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact_with_origin() x;
+ERROR:  could not get commit timestamp data
+HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+-- Test when it can not find the transaction
+SELECT * FROM pg_xact_commit_timestamp_origin((:'txid_set_origin'::text::int + 10)::text::xid) 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..057c45b5f2 100644
--- a/src/test/modules/commit_ts/sql/commit_timestamp.sql
+++ b/src/test/modules/commit_ts/sql/commit_timestamp.sql
@@ -22,3 +22,33 @@ 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;
+
+
+-- Test non-normal transaction id.
+SELECT origin FROM  pg_xact_commit_timestamp_origin('0'::xid);
+SELECT origin FROM  pg_xact_commit_timestamp_origin('1'::xid);
+SELECT origin FROM  pg_xact_commit_timestamp_origin('2'::xid);
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+
+
+-- Test transaction without origin
+SELECT txid_current() as txid_no_origin \gset
+
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact_with_origin() x;
+
+-- Test transaction with origin
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+SELECT txid_current() as txid_set_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_set_origin') x;
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact_with_origin() x;
+
+-- Test when it can not find the transaction
+SELECT * FROM pg_xact_commit_timestamp_origin((:'txid_set_origin'::text::int + 10)::text::xid) x;
+
+SELECT pg_replication_origin_session_reset();
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin');
#18Michael Paquier
michael@paquier.xyz
In reply to: movead.li@highgo.ca (#17)
Re: A patch for get origin from commit_ts.

On Mon, Jul 06, 2020 at 11:12:30AM +0800, movead.li@highgo.ca wrote:

Thanks for the points and follow them, new patch attached.

That was fast, thanks. I have not tested the patch, but there are
two things I missed a couple of hours back. Why do you need
pg_last_committed_xact_with_origin() to begin with? Wouldn't it be
more simple to just add a new column to pg_last_committed_xact() for
the replication origin? Contrary to pg_xact_commit_timestamp() that
should not be broken for compatibility reasons because it returns only
one value, we don't have this problem with pg_last_committed_xact() as
it already returns one tuple with two values.

+{ oid => '4179', descr => 'get commit origin of a transaction',

A second thing is that the OID of the new function should be in the
range 8000..9999, as per the policy introduced in commit a6417078.
src/include/catalog/unused_oids can be used to pick up a value.
--
Michael

#19movead.li@highgo.ca
movead.li@highgo.ca
In reply to: Michael Paquier (#2)
1 attachment(s)
Re: A patch for get origin from commit_ts.

That was fast, thanks. I have not tested the patch, but there are
two things I missed a couple of hours back. Why do you need
pg_last_committed_xact_with_origin() to begin with? Wouldn't it be
more simple to just add a new column to pg_last_committed_xact() for
the replication origin? Contrary to pg_xact_commit_timestamp() that
should not be broken for compatibility reasons because it returns only
one value, we don't have this problem with pg_last_committed_xact() as
it already returns one tuple with two values.

Yes make sense, changed in new patch.

+{ oid => '4179', descr => 'get commit origin of a transaction',
A second thing is that the OID of the new function should be in the
range 8000..9999, as per the policy introduced in commit a6417078.
src/include/catalog/unused_oids can be used to pick up a value.

Thanks, very helpful information and I have follow that.

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

Attachments:

get_origin_from_commit_ts_v5.patchapplication/octet-stream; name=get_origin_from_commit_ts_v5.patchDownload
commit 8dae80ad11cb2ee0382aeb9533029b2e2090ef5a
Author: movead <movead.li@highgo.ca>
Date:   Tue Jul 7 09:41:34 2020 +0800
Subject: add functions to get origin information from commit_ts

---
 doc/src/sgml/func.sgml                                     | 20 ++++++++++++++++++--
 src/backend/access/transam/commit_ts.c                     | 60 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++----
 src/include/catalog/pg_proc.dat                            | 12 +++++++++---
 src/test/modules/commit_ts/expected/commit_timestamp.out   | 78 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/test/modules/commit_ts/expected/commit_timestamp_1.out | 63 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/test/modules/commit_ts/sql/commit_timestamp.sql        | 30 ++++++++++++++++++++++++++++++
 6 files changed, 254 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f065856535..079c697266 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> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
+         <parameter>Oid</parameter> <type>origin</type>)
+       </para>
+       <para>
+         Returns the commit timestamp and origin of a transaction.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -23405,10 +23420,11 @@ 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>origin</type> )
        </para>
        <para>
-        Returns the transaction ID and commit timestamp of the latest
+        Returns the transaction ID, commit timestamp and origin of the latest
         committed transaction.
        </para></entry>
       </row>
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 9cdb136435..47518b04f9 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -421,24 +421,27 @@ 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, "origin",
+					   OIDOID, -1, 0);
 	tupdesc = BlessTupleDesc(tupdesc);
 
 	if (!TransactionIdIsNormal(xid))
@@ -452,6 +455,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 +465,52 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
+/*
+ * SQL-callable wrapper to obtain commit timestamp and origin of
+ * a 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, "origin",
+					   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/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 38295aca48..f9f5bebe0e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5946,12 +5946,18 @@
   prorettype => 'timestamptz', proargtypes => 'xid',
   prosrc => 'pg_xact_commit_timestamp' },
 
+{ oid => '8000', descr => 'get commit timestamp and 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,origin}', 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 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,origin}', 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/test/modules/commit_ts/expected/commit_timestamp.out b/src/test/modules/commit_ts/expected/commit_timestamp.out
index 5b7783b58f..afbcf5e055 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp.out
@@ -45,3 +45,81 @@ SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timest
  t        | t        | t
 (1 row)
 
+-- Test non-normal transaction id.
+SELECT origin FROM  pg_xact_commit_timestamp_origin('0'::xid);
+ERROR:  cannot retrieve commit timestamp for transaction 0
+SELECT origin FROM  pg_xact_commit_timestamp_origin('1'::xid);
+ origin 
+--------
+       
+(1 row)
+
+SELECT origin FROM  pg_xact_commit_timestamp_origin('2'::xid);
+ origin 
+--------
+       
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+ pg_replication_origin_create 
+------------------------------
+                            1
+(1 row)
+
+-- Test transaction without origin
+SELECT txid_current() as txid_no_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
+ ?column? | ?column? | origin 
+----------+----------+--------
+ t        | t        |      0
+(1 row)
+
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact() x;
+ ?column? | ?column? | ?column? | origin 
+----------+----------+----------+--------
+ t        | t        | t        |      0
+(1 row)
+
+-- Test transaction with origin
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid_set_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_set_origin') x;
+ ?column? | ?column? | origin 
+----------+----------+--------
+ t        | t        |      1
+(1 row)
+
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact() x;
+ ?column? | ?column? | ?column? | origin 
+----------+----------+----------+--------
+ t        | t        | t        |      1
+(1 row)
+
+-- Test when it can not find the transaction
+SELECT * FROM pg_xact_commit_timestamp_origin((:'txid_set_origin'::text::int + 10)::text::xid) x;
+ timestamp | origin 
+-----------+--------
+           |       
+(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..4305895d8b 100644
--- a/src/test/modules/commit_ts/expected/commit_timestamp_1.out
+++ b/src/test/modules/commit_ts/expected/commit_timestamp_1.out
@@ -37,3 +37,66 @@ 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;
 ERROR:  could not get commit timestamp data
 HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+-- Test non-normal transaction id.
+SELECT origin FROM  pg_xact_commit_timestamp_origin('0'::xid);
+ERROR:  cannot retrieve commit timestamp for transaction 0
+SELECT origin FROM  pg_xact_commit_timestamp_origin('1'::xid);
+ origin 
+--------
+       
+(1 row)
+
+SELECT origin FROM  pg_xact_commit_timestamp_origin('2'::xid);
+ origin 
+--------
+       
+(1 row)
+
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+ pg_replication_origin_create 
+------------------------------
+                            1
+(1 row)
+
+-- Test transaction without origin
+SELECT txid_current() as txid_no_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+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.
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+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 transaction with origin
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+ pg_replication_origin_session_setup 
+-------------------------------------
+ 
+(1 row)
+
+SELECT txid_current() as txid_set_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_set_origin') x;
+ERROR:  could not get commit timestamp data
+HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+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 when it can not find the transaction
+SELECT * FROM pg_xact_commit_timestamp_origin((:'txid_set_origin'::text::int + 10)::text::xid) 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..bec9194a9b 100644
--- a/src/test/modules/commit_ts/sql/commit_timestamp.sql
+++ b/src/test/modules/commit_ts/sql/commit_timestamp.sql
@@ -22,3 +22,33 @@ 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;
+
+
+-- Test non-normal transaction id.
+SELECT origin FROM  pg_xact_commit_timestamp_origin('0'::xid);
+SELECT origin FROM  pg_xact_commit_timestamp_origin('1'::xid);
+SELECT origin FROM  pg_xact_commit_timestamp_origin('2'::xid);
+SELECT pg_replication_origin_create('test_commit_ts: get_origin');
+
+
+-- Test transaction without origin
+SELECT txid_current() as txid_no_origin \gset
+
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact() x;
+
+-- Test transaction with origin
+SELECT pg_replication_origin_session_setup('test_commit_ts: get_origin');
+SELECT txid_current() as txid_set_origin \gset
+SELECT x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_xact_commit_timestamp_origin(:'txid_set_origin') x;
+SELECT x.xid::text::bigint > 0, x.timestamp > '-infinity'::timestamptz, x.timestamp <= now(), x.origin
+FROM pg_last_committed_xact() x;
+
+-- Test when it can not find the transaction
+SELECT * FROM pg_xact_commit_timestamp_origin((:'txid_set_origin'::text::int + 10)::text::xid) x;
+
+SELECT pg_replication_origin_session_reset();
+SELECT pg_replication_origin_drop('test_commit_ts: get_origin');
#20Michael Paquier
michael@paquier.xyz
In reply to: movead.li@highgo.ca (#19)
1 attachment(s)
Re: A patch for get origin from commit_ts.

On Tue, Jul 07, 2020 at 10:02:29AM +0800, movead.li@highgo.ca wrote:

Thanks, very helpful information and I have followed that.

Cool, thanks. I have gone through your patch in details, and updated
it as the attached. Here are some comments.

'8000' as OID for the new function was not really random, so to be
fair with the other patches, I picked up the first random value
unused_oids has given me instead.

There were some indentation issues, and pgindent got that fixed.

I think that it is better to use "replication origin" in the docs
instead of just origin. I have kept "origin" in the functions for
now as that sounded cleaner to me, but we may consider using something
like "reporigin" as well as attribute name.

The tests could just use tstzrange() to make sure that the timestamps
have valid values, so I have switched to that, and did not resist to
do the same in the existing tests.

+-- Test when it can not find the transaction
+SELECT * FROM pg_xact_commit_timestamp_origin((:'txid_set_origin'::text::int +
10)::text::xid) x;
This test could become unstable, particularly if it gets used in a
parallel environment, so I have removed it.  Perhaps I am just
over-pessimistic here though..

As a side note, I think that we could just remove the alternate output
of commit_ts/, as it does not really get used because of the
NO_INSTALLCHECK present in the module's Makefile. That would be the
job of a different patch, so I have updated it accordingly. Glad to
see that you did not forget to adapt it in your own patch.

(The change in catversion.h is a self-reminder...)
--
Michael

Attachments:

get_origin_from_commit_ts_v6.patchtext/x-diff; charset=us-asciiDownload
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 54518cd40e..ee58586569 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202007061
+#define CATALOG_VERSION_NO	202007121
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 38295aca48..585823576a 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,origin}',
+  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,origin}', 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..e48f88c884 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, "origin",
+					   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, "origin",
+					   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..973750c13a 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,
+    origin
+  FROM pg_last_committed_xact() x;
+ xid_valid | ts_in_range | origin 
+-----------+-------------+--------
+ t         | t           |      0
+(1 row)
+
+-- Test non-normal transaction ids.
+SELECT * FROM pg_xact_commit_timestamp_origin(NULL); -- ok, NULL
+ timestamp | origin 
+-----------+--------
+           |       
+(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 | origin 
+-----------+--------
+           |       
+(1 row)
+
+SELECT * FROM pg_xact_commit_timestamp_origin('2'::xid); -- ok, NULL
+ timestamp | origin 
+-----------+--------
+           |       
+(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, origin
+  FROM pg_last_committed_xact() x;
+ ts_in_range | origin 
+-------------+--------
+ t           |      0
+(1 row)
+
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, origin
+  FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
+ ts_in_range | origin 
+-------------+--------
+ 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, origin
+  FROM pg_last_committed_xact() x;
+ ts_in_range | origin 
+-------------+--------
+ t           |      1
+(1 row)
+
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, origin
+  FROM pg_xact_commit_timestamp_origin(:'txid_with_origin') x;
+ ts_in_range | origin 
+-------------+--------
+ 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..34da5b8624 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,
+    origin
+  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 | origin 
+-----------+--------
+           |       
+(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 | origin 
+-----------+--------
+           |       
+(1 row)
+
+SELECT * FROM pg_xact_commit_timestamp_origin('2'::xid); -- ok, NULL
+ timestamp | origin 
+-----------+--------
+           |       
+(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, origin
+  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, origin
+  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, origin
+  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, origin
+  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..72390614e7 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,
+    origin
+  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, origin
+  FROM pg_last_committed_xact() x;
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, origin
+  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, origin
+  FROM pg_last_committed_xact() x;
+SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, origin
+  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..1080d3a666 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> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>timestamp</parameter> <type>timestamp with time zone</type>,
+         <parameter>Oid</parameter> <type>origin</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>origin</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>
#21movead.li@highgo.ca
movead.li@highgo.ca
In reply to: Madan Kumar (#4)
Re: A patch for get origin from commit_ts.

Cool, thanks. I have gone through your patch in details, and updated
it as the attached. Here are some comments.

'8000' as OID for the new function was not really random, so to be
fair with the other patches, I picked up the first random value
unused_oids has given me instead.

There were some indentation issues, and pgindent got that fixed.

I think that it is better to use "replication origin" in the docs
instead of just origin. I have kept "origin" in the functions for
now as that sounded cleaner to me, but we may consider using something
like "reporigin" as well as attribute name.

The tests could just use tstzrange() to make sure that the timestamps
have valid values, so I have switched to that, and did not resist to
do the same in the existing tests.

+-- Test when it can not find the transaction
+SELECT * FROM pg_xact_commit_timestamp_origin((:'txid_set_origin'::text::int +
10)::text::xid) x;
This test could become unstable, particularly if it gets used in a
parallel environment, so I have removed it.  Perhaps I am just
over-pessimistic here though..

As a side note, I think that we could just remove the alternate output
of commit_ts/, as it does not really get used because of the
NO_INSTALLCHECK present in the module's Makefile. That would be the
job of a different patch, so I have updated it accordingly. Glad to
see that you did not forget to adapt it in your own patch.

(The change in catversion.h is a self-reminder...)

Thanks for all of that, so many details I still need to pay attention when
submit a patch.

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

#22Michael Paquier
michael@paquier.xyz
In reply to: movead.li@highgo.ca (#21)
Re: A patch for get origin from commit_ts.

On Wed, Jul 08, 2020 at 09:31:24AM +0800, movead.li@highgo.ca wrote:

Thanks for all of that, so many details I still need to pay attention when
submit a patch.

No problem. We are all here to learn, and nothing can be perfect, if
perfection is even possible :)

Regarding the attribute name, I was actually considering to just use
"roident" instead. This is more consistent with pglogical, and that's
also the field name we use in ReplicationState[OnDisk]. What do you
think?
--
Michael

#23movead.li@highgo.ca
movead.li@highgo.ca
In reply to: Noname (#10)
Re: A patch for get origin from commit_ts.

Regarding the attribute name, I was actually considering to just use
"roident" instead. This is more consistent with pglogical, and that's
also the field name we use in ReplicationState[OnDisk]. What do you
think?

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.

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

#24Michael Paquier
michael@paquier.xyz
In reply to: movead.li@highgo.ca (#23)
1 attachment(s)
Re: A patch for get origin from commit_ts.

On Wed, Jul 08, 2020 at 10:11:28AM +0800, movead.li@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

Attachments:

rep-origin-superuser-v7.patchtext/x-diff; charset=us-asciiDownload
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>
#25movead.li@highgo.ca
movead.li@highgo.ca
In reply to: Michael Paquier (#13)
Re: A patch for get origin from commit_ts.

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.

I think everything is ok, but be careful the new patch is in Windows
format now.

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

#26Michael Paquier
michael@paquier.xyz
In reply to: movead.li@highgo.ca (#25)
Re: A patch for get origin from commit_ts.

On Thu, Jul 09, 2020 at 10:04:23AM +0800, movead.li@highgo.ca wrote:

but be careful the new patch is in Windows format now.

That would be surprising. Why do you think that?
--
Michael

#27movead.li@highgo.ca
movead.li@highgo.ca
In reply to: Michael Paquier (#16)
Re: A patch for get origin from commit_ts.

but be careful the new patch is in Windows format now.

That would be surprising. Why do you think that?

I am not sure why, I can not apply your patch, and I open it
with vscode and shows a CRLF format, if I change the patch to
LF then nothing wrong.

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

#28Michael Paquier
michael@paquier.xyz
In reply to: movead.li@highgo.ca (#27)
Re: A patch for get origin from commit_ts.

On Thu, Jul 09, 2020 at 02:19:46PM +0800, movead.li@highgo.ca wrote:

I am not sure why, I can not apply your patch, and I open it
with vscode and shows a CRLF format, if I change the patch to
LF then nothing wrong.

This looks like an issue in your environment, like with git's autocrlf
or such? rep-origin-superuser-v7.patch has no CRLF.
--
Michael

#29movead.li@highgo.ca
movead.li@highgo.ca
In reply to: Michael Paquier (#18)
Re: A patch for get origin from commit_ts.

I am not sure why, I can not apply your patch, and I open it
with vscode and shows a CRLF format, if I change the patch to
LF then nothing wrong.

This looks like an issue in your environment, like with git's autocrlf
or such? rep-origin-superuser-v7.patch has no CRLF.

Yes thanks, that's my environment problem.

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

#30Michael Paquier
michael@paquier.xyz
In reply to: Michael Paquier (#24)
Re: A patch for get origin from commit_ts.

On Wed, Jul 08, 2020 at 03:08:24PM +0900, Michael Paquier wrote:

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.

Please note that I have switched the patch as ready for committer. So
I'll try to get that done, with roident as attribute name. If
somebody prefers a different name or has an objection, please feel
free to chime in.
--
Michael

#31Michael Paquier
michael@paquier.xyz
In reply to: Michael Paquier (#30)
Re: A patch for get origin from commit_ts.

On Fri, Jul 10, 2020 at 10:06:06AM +0900, Michael Paquier wrote:

Please note that I have switched the patch as ready for committer. So
I'll try to get that done, with roident as attribute name. If
somebody prefers a different name or has an objection, please feel
free to chime in.

Hearing nothing, committed after fixing few things:
- The docs reversed <parameter> and <type>.
- The comment on top of GetLatestCommitTsData() mentioned "extra"
instead of "nodeid". Not an issue of this patch but I have just fixed
it.
- We could just have used memset for nulls when the data could not be
found in pg_xact_commit_timestamp_origin().
- Added some casts to Oid for the new ObjectIdGetDatum() calls.
- Changed the tests to not show numerical values for roident, checking
instead that the values are non-zero for the cases where we don't
expect a valid replication origin. For the valid cases, I have just
used a join with pg_replication_origin to grab roname. This makes the
tests more portable.

After applying the patch as of b1e48bb, longfin has also complained
that regression tests should prefix replication origins with
"regress_". This has been fixed with ea3e15d.
--
Michael