pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: movead.li@highgo.ca <movead.li@highgo.ca>
To: michael <michael@paquier.xyz>
To: petr <petr@2ndquadrant.com>
Cc: simon <simon@2ndquadrant.com>
Cc: madankumar1993 <madankumar1993@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Cc: craig <craig@2ndquadrant.com>
Cc: alvherre <alvherre@2ndquadrant.com>
Cc: andres <andres@anarazel.de>
Cc: soumyadeep2007 <soumyadeep2007@gmail.com>
Subject: Re: A patch for get origin from commit_ts.
Date: Sat, 4 Jul 2020 18:01:28 +0800
Message-ID: <202007041801258116499@highgo.ca> (raw )
References: <2020051116430836450630@highgo.ca >
<20200512014537.GF88791@paquier.xyz >
<2020051316291300911447@highgo.ca >
<CAMtsaPEm47-mD5br8O8fVsZ0PnowtNij4O3UNsuG6Zb7dSCAaQ@mail.gmail.com >
<CANP8+jJu-bta+4KBRDf2XKsHybDBY1Ew_zvcuoTdhES+ZWLGcQ@mail.gmail.com >
<20200702015852.GE10408@paquier.xyz >
<4ee522fa-b549-4a8d-9d9e-cb27be774627@2ndquadrant.com >
<20200703051059.GB1729@paquier.xyz >
>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.patch (application/octet-stream, 15.0K) download
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');
view thread (31+ messages) latest in thread
Message-ID: <202007041801258116499@highgo.ca>
Permalink: ../202007041801258116499@highgo.ca/
Also on: postgresql.org/message-id/202007041801258116499@highgo.ca
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: movead.li@highgo.ca, michael@paquier.xyz, petr@2ndquadrant.com, simon@2ndquadrant.com, madankumar1993@gmail.com, pgsql-hackers@lists.postgresql.org, craig@2ndquadrant.com, alvherre@2ndquadrant.com, andres@anarazel.de, soumyadeep2007@gmail.com
Subject: Re: A patch for get origin from commit_ts.
In-Reply-To: <202007041801258116499@highgo.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox