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

From: 李海龙 <hailong(dot)li(at)qunar(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: the include-timestamp data returned by pg_logical_slot_peek_changes() is always 2000-01-01 in 9.5.1
Date: 2016-03-09 10:13:43
Message-ID: 56DFF752.7030905@qunar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)pgtest ~]$ cat /etc/issue
CentOS release 6.4 (Final)
Kernel \r on an \m

[postgres(at)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(at)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(at)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)

Attachment Content-Type Size
hailong_li.vcf text/x-vcard 4 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Shulgin, Oleksandr 2016-03-09 10:23:23 Re: More stable query plans via more predictable column statistics
Previous Message Shulgin, Oleksandr 2016-03-09 09:58:20 Re: More stable query plans via more predictable column statistics