Re: PG8.3->10 migration data differences

2018-09-11 Thread Adrian Klaver 

On 9/11/18 1:41 AM, Csaba Ragasits wrote:

Hello,

We would like to migrate from 8.3 to 10 version. We've hundreds 
databases with different structures. That reason we're working on an 
automatic data comparing process.


I've found the following storage settings:
- pg83: Date/time type storage:   64-bit integers
- pg10: Date/time type storage:   64-bit integers

When I running the following select from psql (Select '09/10/18 
07:10:25.110'::timestamp;)

The results are same:
-pg83: 2018-09-10 07:10:25.11
-pg10: 2018-09-10 07:10:25.11

When I select it from a table (Select v.entry_timestamp from t_vis v):
The results are different:
- pg83: 2015-08-28 21:25:07.70
- pg10: 2015-08-28 21:25:07.7


Actually the result is the same:

select '2015-08-28 21:25:07.70'::timestamp = '2015-08-28 
21:25:07.7'::timestamp;


 ?column?
--
 t

The formatting is different.



The field type:
entry_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,

Our difference every time the last 0 values. The time values are same, 
but our field based data comparing mechanism every time mark it as error.


As Ron stated it should not.



thx,
Csaba



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PG8.3->10 migration data differences

2018-09-11 Thread Ron 

Then fix your field-based data comparing mechanism.

On 09/11/2018 03:41 AM, Csaba Ragasits wrote:

Hello,

We would like to migrate from 8.3 to 10 version. We've hundreds databases 
with different structures. That reason we're working on an automatic data 
comparing process.


I've found the following storage settings:
- pg83: Date/time type storage:   64-bit integers
- pg10: Date/time type storage:   64-bit integers

When I running the following select from psql (Select '09/10/18 
07:10:25.110'::timestamp;)

The results are same:
-pg83: 2018-09-10 07:10:25.11
-pg10: 2018-09-10 07:10:25.11

When I select it from a table (Select v.entry_timestamp from t_vis v):
The results are different:
- pg83: 2015-08-28 21:25:07.70
- pg10: 2015-08-28 21:25:07.7

The field type:
entry_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,

Our difference every time the last 0 values. The time values are same, but 
our field based data comparing mechanism every time mark it as error.


thx,
Csaba


--
Angular momentum makes the world go 'round.

Re: PG8.3->10 migration data differences

2018-09-11 Thread Csaba Ragasits 
Hello,

We would like to migrate from 8.3 to 10 version. We've hundreds databases
with different structures. That reason we're working on an automatic data
comparing process.

I've found the following storage settings:
- pg83: Date/time type storage:   64-bit integers
- pg10: Date/time type storage:   64-bit integers

When I running the following select from psql (Select '09/10/18
07:10:25.110'::timestamp;)
The results are same:
-pg83: 2018-09-10 07:10:25.11
-pg10: 2018-09-10 07:10:25.11

When I select it from a table (Select v.entry_timestamp from t_vis v):
The results are different:
- pg83: 2015-08-28 21:25:07.70
- pg10: 2015-08-28 21:25:07.7

The field type:
entry_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,

Our difference every time the last 0 values. The time values are same, but
our field based data comparing mechanism every time mark it as error.

thx,
Csaba


2018-09-10 16:21 GMT+02:00 Adrian Klaver :

> On 9/10/18 6:43 AM, Csaba Ragasits wrote:
>
>> I think I found the solution.
>>
>> When I set this parameter on the pg10 client connection, the pg10 REAL
>> value format same as the pg83 value:
>>
>> SET extra_float_digits = 0;
>>
>> Interesting, because the default value is 0 in the postgresql.conf:
>> #extra_float_digits = 0 # min -15, max 3
>>
>> Do you have any ideas how can I format the miliseconds too?
>>
>
> select version();
>version
> 
> 
>  PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
> 4.8.5, 64-bit
>
> test=# select '09/10/18 07:10:25.100'::timestamp;
>timestamp
> ---
>  2018-09-10 07:10:25.1
> (1 row)
>
> test=# select '09/10/18 07:10:25.111'::timestamp;
> timestamp
> -
>  2018-09-10 07:10:25.111
>
> Milliseconds will display if they are significant.
>
>
> If you want to format the output:
>
> test=# select to_char('09/10/18 07:10:25.100'::timestamp, 'MM/DD/YY HH:MI:
> SS.MS');
> to_char
> ---
>  09/10/18 07:10:25.100
> (1 row)
>
>
>
>> Thx,
>> Csaba
>>
>> 2018-09-10 12:00 GMT+02:00 Peter J. Holzer > hjp-pg...@hjp.at>>:
>>
>> On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
>> > On 10/09/2018 11:22, Csaba Ragasits wrote:
>> > Hello,
>> > > We would like to migrate our old databases to new
>> postgres server,
>> > with the simple backup-restore process. The process run fine
>> > without errors but when we're  checking the source and the
>> > migrated datas as field level, we found the following
>> differences:
>> [...]
>> > > Field type: REAL
>> > pg93: 2.2
>> > pg10: 2.2005
>> > > > Those have to do with rounding. Precision for real is 6
>> decimal
>> > digits. Your difference is on the 8-th digit.
>>
>> Nitpick: Precision is 24 binary digits which is *about* 6 decimal
>> digits. The number which is actually stored (on both pg93 and pg10) is
>> actually 2.200476837158203125 (1.00011001100110011001101 * 2^1 in
>> binary). Apparently Pg9.3 rounds this to "2.2" on output while Pg10
>> prints the more precise (but still not exact) "2.2005".
>>
>> (I would argue that the Pg9.3 output is better, since it represents
>> the
>> same value in fewer digits, but always printing the minimum number of
>> digits necessary is surprisingly difficult.)
>>
>>  hp
>>
>> -- _  | Peter J. Holzer| we build much bigger, better
>> disasters now
>> |_|_) || because we have much more sophisticated
>> | |   | h...@hjp.at  | management tools.
>> __/   | http://www.hjp.at/ | -- Ross Anderson 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: PG8.3->10 migration data differences

2018-09-10 Thread Adrian Klaver 

On 9/10/18 6:43 AM, Csaba Ragasits wrote:

I think I found the solution.

When I set this parameter on the pg10 client connection, the pg10 REAL 
value format same as the pg83 value:


SET extra_float_digits = 0;

Interesting, because the default value is 0 in the postgresql.conf:
#extra_float_digits = 0 # min -15, max 3

Do you have any ideas how can I format the miliseconds too?


select version();
   version

 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 
4.8.5, 64-bit


test=# select '09/10/18 07:10:25.100'::timestamp;
   timestamp
---
 2018-09-10 07:10:25.1
(1 row)

test=# select '09/10/18 07:10:25.111'::timestamp;
timestamp
-
 2018-09-10 07:10:25.111

Milliseconds will display if they are significant.


If you want to format the output:

test=# select to_char('09/10/18 07:10:25.100'::timestamp, 'MM/DD/YY 
HH:MI:SS.MS');

to_char
---
 09/10/18 07:10:25.100
(1 row)




Thx,
Csaba

2018-09-10 12:00 GMT+02:00 Peter J. Holzer >:


On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
> On 10/09/2018 11:22, Csaba Ragasits wrote:
>     Hello,
> 
>     We would like to migrate our old databases to new postgres server,

>     with the simple backup-restore process. The process run fine
>     without errors but when we're  checking the source and the
>     migrated datas as field level, we found the following differences:
[...]
> 
>     Field type: REAL

>     pg93: 2.2
>     pg10: 2.2005
> 
> 
> Those have to do with rounding. Precision for real is 6 decimal

> digits. Your difference is on the 8-th digit.

Nitpick: Precision is 24 binary digits which is *about* 6 decimal
digits. The number which is actually stored (on both pg93 and pg10) is
actually 2.200476837158203125 (1.00011001100110011001101 * 2^1 in
binary). Apparently Pg9.3 rounds this to "2.2" on output while Pg10
prints the more precise (but still not exact) "2.2005".

(I would argue that the Pg9.3 output is better, since it represents the
same value in fewer digits, but always printing the minimum number of
digits necessary is surprisingly difficult.)

         hp

-- 
    _  | Peter J. Holzer    | we build much bigger, better disasters now

|_|_) |                    | because we have much more sophisticated
| |   | h...@hjp.at          | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PG8.3->10 migration data differences

2018-09-10 Thread Adrian Klaver 

On 9/10/18 6:43 AM, Csaba Ragasits wrote:

I think I found the solution.

When I set this parameter on the pg10 client connection, the pg10 REAL 
value format same as the pg83 value:


In your previous post you mentioned both 9.3 and 8.3.

Is 8.3 the actual version you are migrating from?



SET extra_float_digits = 0;

Interesting, because the default value is 0 in the postgresql.conf:
#extra_float_digits = 0 # min -15, max 3

Do you have any ideas how can I format the miliseconds too?


Assuming you are moving from 8.3 what does:

pg_controldata -D /your_83/data_dir

show for Date/time type storage: ?

vs

pg_controldata -D /your_10/data_dir



Thx,
Csaba

2018-09-10 12:00 GMT+02:00 Peter J. Holzer >:


On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
> On 10/09/2018 11:22, Csaba Ragasits wrote:
>     Hello,
> 
>     We would like to migrate our old databases to new postgres server,

>     with the simple backup-restore process. The process run fine
>     without errors but when we're  checking the source and the
>     migrated datas as field level, we found the following differences:
[...]
> 
>     Field type: REAL

>     pg93: 2.2
>     pg10: 2.2005
> 
> 
> Those have to do with rounding. Precision for real is 6 decimal

> digits. Your difference is on the 8-th digit.

Nitpick: Precision is 24 binary digits which is *about* 6 decimal
digits. The number which is actually stored (on both pg93 and pg10) is
actually 2.200476837158203125 (1.00011001100110011001101 * 2^1 in
binary). Apparently Pg9.3 rounds this to "2.2" on output while Pg10
prints the more precise (but still not exact) "2.2005".

(I would argue that the Pg9.3 output is better, since it represents the
same value in fewer digits, but always printing the minimum number of
digits necessary is surprisingly difficult.)

         hp

-- 
    _  | Peter J. Holzer    | we build much bigger, better disasters now

|_|_) |                    | because we have much more sophisticated
| |   | h...@hjp.at          | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PG8.3->10 migration data differences

2018-09-10 Thread Tom Lane 
"Peter J. Holzer"  writes:
> On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
>> On 10/09/2018 11:22, Csaba Ragasits wrote:
>>> Field type: REAL
>>> pg93: 2.2
>>> pg10: 2.2005

>> Those have to do with rounding. Precision for real is 6 decimal
>> digits. Your difference is on the 8-th digit.

> Nitpick: Precision is 24 binary digits which is *about* 6 decimal
> digits. The number which is actually stored (on both pg93 and pg10) is
> actually 2.200476837158203125 (1.00011001100110011001101 * 2^1 in
> binary). Apparently Pg9.3 rounds this to "2.2" on output while Pg10
> prints the more precise (but still not exact) "2.2005". 

Well, more specifically:

regression=# set extra_float_digits to 2;
SET
regression=# select 2.2::real;
 float4 

2.2
(1 row)

regression=# set extra_float_digits to 3;
SET
regression=# select 2.2::real;
   float4   

 2.2005
(1 row)

8.3 did not let you set extra_float_digits as high as 3, so you couldn't
get the "2.2005" result there, but given that and a correct
implementation of sprintf you would have.

I surmise that the OP is comparing the output of pg_dump in the two
versions.  pg_dump always sets extra_float_digits to the maximum the
server will allow, and the reason is to ensure that the dumped value will
reload as the same binary bit pattern (again, assuming correct float I/O
functionality in libc).  We used to think that 2 extra digits beyond the
nominal precision was enough to guarantee that, but there are cases where
you need 3, so it got changed.

regards, tom lane

Re: PG8.3->10 migration data differences

2018-09-10 Thread Csaba Ragasits 
I think I found the solution.

When I set this parameter on the pg10 client connection, the pg10 REAL
value format same as the pg83 value:

SET extra_float_digits = 0;

Interesting, because the default value is 0 in the postgresql.conf:
#extra_float_digits = 0 # min -15, max 3

Do you have any ideas how can I format the miliseconds too?

Thx,
Csaba

2018-09-10 12:00 GMT+02:00 Peter J. Holzer :

> On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
> > On 10/09/2018 11:22, Csaba Ragasits wrote:
> > Hello,
> >
> > We would like to migrate our old databases to new postgres server,
> > with the simple backup-restore process. The process run fine
> > without errors but when we're  checking the source and the
> > migrated datas as field level, we found the following differences:
> [...]
> >
> > Field type: REAL
> > pg93: 2.2
> > pg10: 2.2005
> >
> >
> > Those have to do with rounding. Precision for real is 6 decimal
> > digits. Your difference is on the 8-th digit.
>
> Nitpick: Precision is 24 binary digits which is *about* 6 decimal
> digits. The number which is actually stored (on both pg93 and pg10) is
> actually 2.200476837158203125 (1.00011001100110011001101 * 2^1 in
> binary). Apparently Pg9.3 rounds this to "2.2" on output while Pg10
> prints the more precise (but still not exact) "2.2005".
>
> (I would argue that the Pg9.3 output is better, since it represents the
> same value in fewer digits, but always printing the minimum number of
> digits necessary is surprisingly difficult.)
>
> hp
>
> --
>_  | Peter J. Holzer| we build much bigger, better disasters now
> |_|_) || because we have much more sophisticated
> | |   | h...@hjp.at | management tools.
> __/   | http://www.hjp.at/ | -- Ross Anderson 
>

Re: PG8.3->10 migration data differences

2018-09-10 Thread Peter J. Holzer 
On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
> On 10/09/2018 11:22, Csaba Ragasits wrote:
> Hello,
> 
> We would like to migrate our old databases to new postgres server,
> with the simple backup-restore process. The process run fine
> without errors but when we're  checking the source and the
> migrated datas as field level, we found the following differences:
[...]
> 
> Field type: REAL
> pg93: 2.2
> pg10: 2.2005
> 
> 
> Those have to do with rounding. Precision for real is 6 decimal
> digits. Your difference is on the 8-th digit.

Nitpick: Precision is 24 binary digits which is *about* 6 decimal
digits. The number which is actually stored (on both pg93 and pg10) is
actually 2.200476837158203125 (1.00011001100110011001101 * 2^1 in
binary). Apparently Pg9.3 rounds this to "2.2" on output while Pg10
prints the more precise (but still not exact) "2.2005". 

(I would argue that the Pg9.3 output is better, since it represents the
same value in fewer digits, but always printing the minimum number of
digits necessary is surprisingly difficult.)

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: PG8.3->10 migration data differences

2018-09-10 Thread Achilleas Mantzios 

On 10/09/2018 11:22, Csaba Ragasits wrote:

Hello,

We would like to migrate our old databases to new postgres server, with the simple backup-restore process. The process run fine without errors but when we're  checking the source and the migrated 
datas as field level, we found the following differences:


For example:

Field type: TIMESTAMP WITHOUT TIME ZONE NOT NULL
pg93: 2015-08-28 21:25:07.70
pg10: 2015-08-28 21:25:07.7

Those two are absolutely the same value. 7/10 = 70/100


Field type: REAL
pg93: 2.2
pg10: 2.2005


Those have to do with rounding. Precision for real is 6 decimal digits. Your 
difference is on the 8-th digit.

When I check the 8.3 pg_dump file, it contains the pg83 correct values.

Do you have any ideas, why different this values?

Thx,
Csaba




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

PG8.3->10 migration data differences

2018-09-10 Thread Csaba Ragasits 
Hello,

We would like to migrate our old databases to new postgres server, with the
simple backup-restore process. The process run fine without errors but when
we're  checking the source and the migrated datas as field level, we found
the following differences:

For example:

Field type: TIMESTAMP WITHOUT TIME ZONE NOT NULL
pg93: 2015-08-28 21:25:07.70
pg10: 2015-08-28 21:25:07.7

Field type: REAL
pg93: 2.2
pg10: 2.2005

When I check the 8.3 pg_dump file, it contains the pg83 correct values.

Do you have any ideas, why different this values?

Thx,
Csaba