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
133
134
135
136
137
138
139
|
--
-- 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 > '-infinity'::timestamptz AS ts_low,
x.timestamp <= now() AS ts_high,
roident != 0 AS valid_roident
FROM pg_last_committed_xact() x;
xid_valid | ts_low | ts_high | valid_roident
-----------+--------+---------+---------------
t | 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 > '-infinity'::timestamptz AS ts_low,
x.timestamp <= now() AS ts_high,
roident != 0 AS valid_roident
FROM pg_last_committed_xact() x;
ts_low | ts_high | valid_roident
--------+---------+---------------
t | t | f
(1 row)
SELECT x.timestamp > '-infinity'::timestamptz AS ts_low,
x.timestamp <= now() AS ts_high,
roident != 0 AS valid_roident
FROM pg_xact_commit_timestamp_origin(:'txid_no_origin') x;
ts_low | ts_high | valid_roident
--------+---------+---------------
t | t | f
(1 row)
-- Test transaction with replication origin
SELECT pg_replication_origin_create('regress_commit_ts: get_origin') != 0
AS valid_roident;
valid_roident
---------------
t
(1 row)
SELECT pg_replication_origin_session_setup('regress_commit_ts: get_origin');
pg_replication_origin_session_setup
-------------------------------------
(1 row)
SELECT txid_current() as txid_with_origin \gset
SELECT x.timestamp > '-infinity'::timestamptz AS ts_low,
x.timestamp <= now() AS ts_high,
r.roname
FROM pg_last_committed_xact() x, pg_replication_origin r
WHERE r.roident = x.roident;
ts_low | ts_high | roname
--------+---------+-------------------------------
t | t | regress_commit_ts: get_origin
(1 row)
SELECT x.timestamp > '-infinity'::timestamptz AS ts_low,
x.timestamp <= now() AS ts_high,
r.roname
FROM pg_xact_commit_timestamp_origin(:'txid_with_origin') x, pg_replication_origin r
WHERE r.roident = x.roident;
ts_low | ts_high | roname
--------+---------+-------------------------------
t | t | regress_commit_ts: get_origin
(1 row)
SELECT pg_replication_origin_session_reset();
pg_replication_origin_session_reset
-------------------------------------
(1 row)
SELECT pg_replication_origin_drop('regress_commit_ts: get_origin');
pg_replication_origin_drop
----------------------------
(1 row)
|
