the include-timestamp data returned by pg_logical_slot_peek_changes() is always 2000-01-01 in 9.5.1

Started by 李海龙almost 10 years ago4 messages
#1李海龙
hailong.li@qunar.com

HI, pgsql-hackers

The include-timestamp data returned by pg_logical_slot_peek_changes() is always 2000-01-01 in 9.5.1, is it not normal?

The following is the test:

[postgres@pgtest ~]$ cat /etc/issue
CentOS release 6.4 (Final)
Kernel \r on an \m

[postgres@pgtest ~]$ uname -av
Linux pgtest 3.10.94-3.centos6.x86_64 #1 SMP Thu Dec 10 17:48:01 CST 2015 x86_64 x86_64 x86_64 GNU/Linux

test on 9.5.1 :

[postgres@pgtest ~]$ psql mydb
psql (9.5.1)
Type "help" for help.

mydb=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
(1 row)

mydb=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
slot_name | xlog_position
-----------------+---------------
regression_slot | 2/CF000338
(1 row)

mydb=# CREATE TABLE data(id serial primary key, data text);
CREATE TABLE
mydb=# INSERT INTO data(data) VALUES('1');
INSERT 0 1
mydb=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');
location | xid | data
------------+------+---------------------------------------------------------
2/CF0023B8 | 1774 | BEGIN 1774
2/CF0049A8 | 1774 | COMMIT 1774 (at 2000-01-01 08:00:00+08)
2/CF0049D8 | 1775 | BEGIN 1775
2/CF00B690 | 1775 | COMMIT 1775 (at 2000-01-01 08:00:00+08)
2/CF00B760 | 1776 | BEGIN 1776
2/CF00B760 | 1776 | table public.data: INSERT: id[integer]:1 data[text]:'1'
2/CF00B898 | 1776 | COMMIT 1776 (at 2000-01-01 08:00:00+08)
(7 rows)

mydb=# INSERT INTO data(data) VALUES('2');
INSERT 0 1
mydb=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');
location | xid | data
------------+------+---------------------------------------------------------
2/CF0023B8 | 1774 | BEGIN 1774
2/CF0049A8 | 1774 | COMMIT 1774 (at 2000-01-01 08:00:00+08)
2/CF0049D8 | 1775 | BEGIN 1775
2/CF00B690 | 1775 | COMMIT 1775 (at 2000-01-01 08:00:00+08)
2/CF00B760 | 1776 | BEGIN 1776
2/CF00B760 | 1776 | table public.data: INSERT: id[integer]:1 data[text]:'1'
2/CF00B898 | 1776 | COMMIT 1776 (at 2000-01-01 08:00:00+08)
2/CF00B8D0 | 1777 | BEGIN 1777
2/CF00B8D0 | 1777 | table public.data: INSERT: id[integer]:2 data[text]:'2'
2/CF00B980 | 1777 | COMMIT 1777 (at 2000-01-01 08:00:00+08)
(10 rows)

mydb=# select now();
now
-------------------------------
2016-03-09 17:43:10.874284+08
(1 row)

mydb=# show timezone;
TimeZone
----------
PRC
(1 row)

test on 9.4.6 :

[postgres@pgtest ~]$ psql mydb
psql (9.4.6)
Type "help" for help.

mydb=# select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
(1 row)

mydb=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');

slot_name | xlog_position
-----------------+---------------
regression_slot | 0/19ECC88
(1 row)

mydb=# CREATE TABLE data(id serial primary key, data text);
CREATE TABLE
mydb=# INSERT INTO data(data) VALUES('1');
INSERT 0 1
mydb=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');

location | xid | data
-----------+------+---------------------------------------------------------
0/19ECD58 | 1811 | BEGIN 1811
0/1A24B18 | 1811 | COMMIT 1811 (at 2016-03-09 18:07:35.936228+08)
0/1A24BD8 | 1812 | BEGIN 1812
0/1A24BD8 | 1812 | table public.data: INSERT: id[integer]:1 data[text]:'1'
0/1A24D20 | 1812 | COMMIT 1812 (at 2016-03-09 18:07:44.113368+08)
(5 rows)

mydb=# INSERT INTO data(data) VALUES('2');
INSERT 0 1
mydb=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');

location | xid | data
-----------+------+---------------------------------------------------------
0/19ECD58 | 1811 | BEGIN 1811
0/1A24B18 | 1811 | COMMIT 1811 (at 2016-03-09 18:07:35.936228+08)
0/1A24BD8 | 1812 | BEGIN 1812
0/1A24BD8 | 1812 | table public.data: INSERT: id[integer]:1 data[text]:'1'
0/1A24D20 | 1812 | COMMIT 1812 (at 2016-03-09 18:07:44.113368+08)
0/1A24D20 | 1813 | BEGIN 1813
0/1A24D20 | 1813 | table public.data: INSERT: id[integer]:2 data[text]:'2'
0/1A24DF0 | 1813 | COMMIT 1813 (at 2016-03-09 18:08:02.399573+08)
(8 rows)

mydb=# select now();
now
-------------------------------
2016-03-09 18:08:11.983058+08
(1 row)

mydb=# show timezone;
TimeZone
----------
PRC
(1 row)

#2Craig Ringer
craig@2ndquadrant.com
In reply to: 李海龙 (#1)
Re: the include-timestamp data returned by pg_logical_slot_peek_changes() is always 2000-01-01 in 9.5.1

On 9 March 2016 at 18:13, 李海龙 <hailong.li@qunar.com> wrote:

HI, pgsql-hackers

The include-timestamp data returned by pg_logical_slot_peek_changes() is
always 2000-01-01 in 9.5.1, is it not normal?

Did you enable track_commit_timestamps in the server?

If not, and it's returning 2000-01-01 I think that's a bug - it should
return null if timestamps aren't recorded. (We can't really differentiate
between the real timestamp 2000-01-01 00:00:00 and the case where we didn't
record a timestamp, but I don't think we care about tx's generated on the
second of the millennium...)

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

#3Andres Freund
andres@anarazel.de
In reply to: Craig Ringer (#2)
Re: the include-timestamp data returned by pg_logical_slot_peek_changes() is always 2000-01-01 in 9.5.1

On March 9, 2016 4:26:01 AM PST, Craig Ringer <craig@2ndquadrant.com> wrote:

On 9 March 2016 at 18:13, 李海龙 <hailong.li@qunar.com> wrote:

HI, pgsql-hackers

The include-timestamp data returned by

pg_logical_slot_peek_changes() is

always 2000-01-01 in 9.5.1, is it not normal?

It's a bug in 9.5, that has been fixed. You can either update to the version from git, wait for the next minor release, or use 9.4, where the buff is not present.

Did you enable track_commit_timestamps in the server?

That's unrelated, commit ts is about an xid timestamp mapping.

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#4hailong.li
hailong.li@qunar.com
In reply to: Andres Freund (#3)
Re: the include-timestamp data returned by pg_logical_slot_peek_changes() is always 2000-01-01 in 9.5.1

On 2016年03月09日 23:31, Andres Freund wrote:

Did you enable track_commit_timestamps in the server?

That's unrelated, commit ts is about an xid timestamp mapping.

Yes, whether enable track_commit_timestamp or not, I have just done
the test again and the result is the same.

Thxs all!

Best regards!

--
*PostgreSQL DBA hailong.li of Qunar*