1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
--
-- Commit Timestamp
--
SHOW track_commit_timestamp;
track_commit_timestamp
------------------------
on
(1 row)
CREATE TABLE committs_test(id serial, ts timestamptz default now());
INSERT INTO committs_test DEFAULT VALUES;
INSERT INTO committs_test DEFAULT VALUES;
INSERT INTO committs_test DEFAULT VALUES;
SELECT id,
pg_xact_commit_timestamp(xmin) >= ts,
pg_xact_commit_timestamp(xmin) <= now(),
pg_xact_commit_timestamp(xmin) - ts < '60s' -- 60s should give a lot of reserve
FROM committs_test
ORDER BY id;
id | ?column? | ?column? | ?column?
----+----------+----------+----------
1 | t | t | t
2 | t | t | t
3 | t | t | t
(3 rows)
DROP TABLE committs_test;
SELECT pg_xact_commit_timestamp('0'::xid);
ERROR: cannot retrieve commit timestamp for transaction 0
SELECT pg_xact_commit_timestamp('1'::xid);
pg_xact_commit_timestamp
--------------------------
(1 row)
SELECT pg_xact_commit_timestamp('2'::xid);
pg_xact_commit_timestamp
--------------------------
(1 row)
SELECT x.xid::text::bigint > 0 as xid_valid,
x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
roident != 0 AS valid_roident
FROM pg_last_committed_xact() x;
xid_valid | ts_in_range | valid_roident
-----------+-------------+---------------
t | t | f
(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 != 0 AS valid_roident
FROM pg_last_committed_xact() x;
ts_in_range | valid_roident
-------------+---------------
t | f
(1 row)
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range,
roident != 0 AS valid_roident
FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
ts_in_range | valid_roident
-------------+---------------
t | f
(1 row)
-- Test transaction with replication origin
SELECT pg_replication_origin_create('test_commit_ts: get_origin') != 0
AS valid_roident;
valid_roident
---------------
t
(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, r.roname
FROM pg_last_committed_xact() x, pg_replication_origin r
WHERE r.roident = x.roident;
ts_in_range | roname
-------------+----------------------------
t | test_commit_ts: get_origin
(1 row)
SELECT x.timestamp <@ tstzrange('-infinity'::timestamptz, now()) AS ts_in_range, r.roname
FROM pg_xact_commit_timestamp_origin(:'txid_with_origin') x, pg_replication_origin r
WHERE r.roident = x.roident;
ts_in_range | roname
-------------+----------------------------
t | test_commit_ts: get_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)
|
