Postgres Pro
Downloads
Home   >   mailing lists

Thread: change in timestamp output from 8.3 to 8.4

change in timestamp output from 8.3 to 8.4

From
Joe Conway
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

I'm testing pg_migrator, and running into an annoyance with timestamps
while trying to verify the conversion. My steps were:

install pg_migrator under 8.4 cluster
run pg_migrator in copy mode -- pg_migrator reports success
start up 8.4 cluster
restore pg_control file in 8.3 cluster
start 8.3 cluster
run pg_dump from 8.4 against both 8.3 and 8.4 clusters
compare the dump files

diffs:
1. Two functions were left in the 8.4 databasepg_toasttbl_drop(oid)pg_toasttbl_recreate(oid, oid)

2. Sequences had additional "START WITH 1"

3. All timestamps in the data that have two decimal places on seconds,  but end with zero, in the 8.3 dump become one
decimalplace in the  8.4 dump
 

The first two items are not much of an issue, but the third makes
validation of the data very painful.

I have to apologize for missing it if this has been discussed already,
but was this change intentional, and if so is there any way to force the
old behavior?

Thanks,

Joe
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iQIcBAEBCAAGBQJKdieMAAoJEDfy90M199hlX5cP/iedAGnua275aR+GBj3M7psp
x5ek93JMxs7U+MkWQ5ufcMbfhd5N+cRauZbnPAHoAa9yqdUanCubH+p2xqSlLOPn
QISf/P11uJKnUOE2AuVUXVso2bBlp6WBpSxt5DgcpjsXgXWXNGmoVwRLb67+ZwX8
Hp7Kmi+OQewNfHwwkpybWvshx5N/BCrNfNfL/gqdEJTPG/H/7YjTomkKQIwdZDAt
NhgSaNL/HcE9iISkkW1tlTNTZhCitow/6LIONQju5X6Un7aUWA8ke8AvWnobeieL
xqzQbkwxa8Xhi4Yk61omupdXyYhJDHsOG1WUe4vlResmaYpeTzv3ztlhrfvBaJzv
gom7B8xLmcwl3+znp0l78TDti2CcvgY5MThvTdymUFa0clYDf93bG8qHq90pw17P
URA0QGLDnhCJThmV9nugw2dgpJCISR2pqsi9YYCX6zLa2dUw9IMWAfZMjyP4K3kP
IzGUB8ppfRadOC5iavsoRdFwY6eClhU+jCTLlUkyN+KcmgJy5H1TwmrhBUEX0reW
jkDZvnTs1+fx4S+yfJQYPpcpxsNp0a8RFAmzWPAF8p7uLNjZUsJtOC3rWNiUGdF8
o8PQPur8xBB5FebmZ0o1ig1ncW6SFREJ8+r1pz/w78fKjlADSy80tEGySPjGRLIL
2cpDvGcfJfR5UyVhB5n1
=6fOT
-----END PGP SIGNATURE-----

Re: change in timestamp output from 8.3 to 8.4

From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> writes:
> 1. Two functions were left in the 8.4 database
>     pg_toasttbl_drop(oid)
>     pg_toasttbl_recreate(oid, oid)

This is pg_migrator's fault --- it should probably clean those up
when it's done.

> 3. All timestamps in the data that have two decimal places on seconds,
>    but end with zero, in the 8.3 dump become one decimal place in the
>    8.4 dump

> I have to apologize for missing it if this has been discussed already,
> but was this change intentional, and if so is there any way to force the
> old behavior?

Yes, it was intentional.  The discussion about changing it was
around here:
http://archives.postgresql.org/pgsql-hackers/2008-10/msg00394.php

There's no exposed way to undo it, but you could probably revert the
logic change in TrimTrailingZeros() until you'd convinced yourself
things were okay.
        regards, tom lane

Re: change in timestamp output from 8.3 to 8.4

From
Joe Conway
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>> 1. Two functions were left in the 8.4 database
>>     pg_toasttbl_drop(oid)
>>     pg_toasttbl_recreate(oid, oid)
>
> This is pg_migrator's fault --- it should probably clean those up
> when it's done.

I figured as much. Not a big deal though...

>> 3. All timestamps in the data that have two decimal places on seconds,
>>    but end with zero, in the 8.3 dump become one decimal place in the
>>    8.4 dump
>
>> I have to apologize for missing it if this has been discussed already,
>> but was this change intentional, and if so is there any way to force the
>> old behavior?
>
> Yes, it was intentional.  The discussion about changing it was
> around here:
> http://archives.postgresql.org/pgsql-hackers/2008-10/msg00394.php
>
> There's no exposed way to undo it, but you could probably revert the
> logic change in TrimTrailingZeros() until you'd convinced yourself
> things were okay.

Ah, just the shortcut I was hoping for :-)

For the record, and anyone else trying to validate 8.3 to 8.4
migrations, the attached patch against 8.3.x makes it behave the same as
8.4.x. This reduced my version-to-version data diff to zilch.

Thanks!

Joe

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iQIcBAEBCAAGBQJKdjGLAAoJEDfy90M199hlfOoP/3W+kXcvwzfVAxG/2rJWkKBP
ZRX0AG++kNDlzsMWCBSFfht2f3ERu0jnECKwnFb0+JF1EaWQJrPaGar1QqJ1IHZb
Iw9AjJE7LvwgcbC8mGmx1zTaI/DPwOvb+LvJIzUtXeoBLGyJuopvGWbcoOvZxvIX
dZkE97JOS0buTiUtUFedx/fpOv7ck/IRhlC4v83ghWl27mWwQ3K9U9S3kRSy3jB+
ajG0MQ3dSfK7usUgV8tfzdFkHQ8+L8PhehfBgRuqNGS1BQQvbNm7sOLnyjqB35w8
gWI37EZ6NMvVMS+880Y9+ktwpgVqVtAfzshtfzJq8HwtvWSiq6OF5wDhlxtwYLdW
h7xhvK37LoXutPMcf/HjflKoI1c9IhDj6CCwHyrxDRRjzfAvpJu7C+in9JrbvhvV
P1o5PpXuxokwafmNi6Nl8TE9s9Mjagw30tPKTK36IRfbAflbRpAByc39qvSXSRWB
xjzPYVHMY0Vwxmup+A7FXvdbzgC6t2n4O18302By0PKpPkMDZw3vhkJKgWF+i4Ux
no+GdL9Al3srpw4fvqr+GwPu8VnVFo+9fzCTlVMB2ayKWEFghCZUEriNBPJwiWaB
X9LtvrU47S5QkKUNQ0Knbs7Z1jEkIYIVnTIRvm6/Uo+5v2dAgss/0qF6wOx0gbUO
5EqsqcZzKhtkSjWxld7t
=eJkn
-----END PGP SIGNATURE-----
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.184.2.3
diff -c -r1.184.2.3 datetime.c
*** src/backend/utils/adt/datetime.c    2 Oct 2008 13:47:44 -0000    1.184.2.3
--- src/backend/utils/adt/datetime.c    3 Aug 2009 00:26:53 -0000
***************
*** 408,424 ****
  {
      int            len = strlen(str);

! #if 0
!     /* chop off trailing one to cope with interval rounding */
!     if (strcmp(str + len - 4, "0001") == 0)
!     {
!         len -= 4;
!         *(str + len) = '\0';
!     }
! #endif
!
!     /* chop off trailing zeros... but leave at least 2 fractional digits */
!     while (*(str + len - 1) == '0' && *(str + len - 3) != '.')
      {
          len--;
          *(str + len) = '\0';
--- 408,414 ----
  {
      int            len = strlen(str);

!     while (len > 1 && *(str + len - 1) == '0' && *(str + len - 2) != '.')
      {
          len--;
          *(str + len) = '\0';

Re: change in timestamp output from 8.3 to 8.4

From
Bruce Momjian
Date:
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> > 1. Two functions were left in the 8.4 database
> >     pg_toasttbl_drop(oid)
> >     pg_toasttbl_recreate(oid, oid)
> 
> This is pg_migrator's fault --- it should probably clean those up
> when it's done.

Agreed.  The new pg_migrator 8.4.4 does clean those up when it finishes.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +