Обсуждение: the include-timestamp data returned by pg_logical_slot_peek_changes() is always 2000-01-01 in 9.5.1

Поиск
Список
Период
Сортировка

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

От
李海龙
Дата:


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)




Вложения

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

От
Craig Ringer
Дата:
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

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

От
Andres Freund
Дата:

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,
oruse 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.

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

От
hailong.li
Дата:
<br /><br /><div class="moz-cite-prefix">On 2016年03月09日 23:31, Andres Freund wrote:<br /></div><blockquote
cite="mid:92E57CB4-CC68-4471-A459-FD96FF78139D@anarazel.de"type="cite"><blockquote style="color: #000000;"
type="cite"><prewrap="">Did you enable track_commit_timestamps in the server?
 
</pre></blockquote><pre wrap="">That's unrelated, commit ts is about an xid timestamp mapping.</pre></blockquote><br />
Yes,whether  enable track_commit_timestamp  or not, I have just done the test again  and the result is the same.<br
/><br/> Thxs all!<br /><br /> Best regards!<br /><br /><div class="moz-signature">-- <br /><b>PostgreSQL DBA hailong.li
ofQunar</b><br /><br /></div>