The following bug has been logged on the website:
Bug reference: 13699
Logged by: digoal
Email address: digoal@126.com
PostgreSQL version: 9.4.5
Operating system: CentOS 6.x x64
Description:
postgres=# create table t3(c1 float, c2 numeric);
CREATE TABLE
postgres=# \d t3
Table "public.t3"
Column | Type | Modifiers
--------+------------------+-----------
c1 | double precision |
c2 | numeric |
postgres=# insert into t3 values (1.55555555555555555555555555555555555,
1.55555555555555555555555555555555555);
INSERT 0 1
postgres=# select * from t3;
c1 | c2
------------------+---------------------------------------
1.55555555555556 | 1.55555555555555555555555555555555555
(1 row)
but:
postgres=# select * from t3 where c1>=1.55555555555556;
c1 | c2
----+----
(0 rows)
when we use logical decoding there will replace the real value with
1.55555555555556.
postgres=# SELECT * FROM
pg_create_logical_replication_slot('regression_slot', 'test_decoding');
slot_name | xlog_position
-----------------+---------------
regression_slot | 0/4C4A260
(1 row)
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot',
NULL, NULL);
location | xid | data
----------+-----+------
(0 rows)
postgres=# create table test(c1 float);
CREATE TABLE
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot',
NULL, NULL);
location | xid | data
-----------+------+-------------
0/4C4A2F0 | 9723 | BEGIN 9723
0/4C5A6D0 | 9723 | COMMIT 9723
(2 rows)
postgres=# insert into test values (1.5555555555555555555555);
INSERT 0 1
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot',
NULL, NULL);
location | xid | data
-----------+------+------------------------------------------------------------------
0/4C5A6D0 | 9724 | BEGIN 9724
0/4C5A6D0 | 9724 | table public.test: INSERT: c1[double
precision]:1.55555555555556
0/4C5A758 | 9724 | COMMIT 9724
(3 rows)
and pg_dump use this code resolve the problem.
if (AH->remoteVersion >= 90000)
ExecuteSqlStatement(AH, "SET extra_float_digits TO 3");
else if (AH->remoteVersion >= 70400)
ExecuteSqlStatement(AH, "SET extra_float_digits TO 2");
in logical decoding , when we use cstring as the output , what we can do?
or we must use bytea output?