Обсуждение: PG COPY from version 8 to 9 issue with timezonetz
--
#pragma mark signature
[[self mailClient] send:[Mail messageWithText:@"From: Brent Gulanowski\nTo: You"];
On Thursday, March 17, 2011 10:10:49 am Brent Gulanowski wrote:
> We use PG COPY to successfully in PG 8 to copy a database between two
> servers. Works perfectly.
>
> When the target server is PG 9, *some* fields of type timezonetz end up
> garbled. Basically the beginning of the string is wrong:
>
> 152037-01-10 16:53:56.719616-05
>
> It should be 2011-03-16 or similar.
>
> In this case, the source computer is running Mac OS X 10.6.6 on x86_64
> (MacBook Pro Core i5), and the destination computer is running Debian Lenny
> on Xeon (Core i7).
>
> I looked at the documentation on the copy command, and the PG9 release
> notes, but I didn't see anything that might explain this problem.
>
> We are using the WITH BINARY option. It has been suggested to disable that.
> What are the down sides of that? I'm guessing just performance with binary
> columns.
I think the bigger downsides come from using it:) See below for more information:
http://www.postgresql.org/docs/9.0/interactive/sql-copy.html
"Binary Format
The binary format option causes all data to be stored/read as binary format rather than as text. It is somewhat faster than the text and CSV formats, but a binary-format file is less portable across machine architectures and PostgreSQL versions. Also, the binary format is very data type specific; for example it will not work to output binary data from a smallint column and read it into an integer column, even though that would work fine in text format.
The binary file format consists of a file header, zero or more tuples containing the row data, and a file trailer. Headers and data are in network byte order. "
--
Adrian Klaver
adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> Thursday 17 March 2011 19:18:25 > On Thursday, March 17, 2011 10:10:49 am Brent Gulanowski wrote: > > We use PG COPY to successfully in PG 8 to copy a database between two > > servers. Works perfectly. > > > > When the target server is PG 9, *some* fields of type timezonetz end up > > garbled. Basically the beginning of the string is wrong: > > > > 152037-01-10 16:53:56.719616-05 > > > > It should be 2011-03-16 or similar. > > > > In this case, the source computer is running Mac OS X 10.6.6 on x86_64 > > (MacBook Pro Core i5), and the destination computer is running Debian > > Lenny on Xeon (Core i7). > > > > I looked at the documentation on the copy command, and the PG9 release > > notes, but I didn't see anything that might explain this problem. > > > > We are using the WITH BINARY option. It has been suggested to disable > > that. What are the down sides of that? I'm guessing just performance > > with binary columns. > > I think the bigger downsides come from using it:) See below for more > information: > > http://www.postgresql.org/docs/9.0/interactive/sql-copy.html > > "Binary Format > The binary format option causes all data to be stored/read as binary format > rather than as text. It is somewhat faster than the text and CSV formats, > but a binary-format file is less portable across machine architectures and > PostgreSQL versions. Also, the binary format is very data type specific; > for example it will not work to output binary data from a smallint column > and read it into an integer column, even though that would work fine in > text format. > The binary file format consists of a file header, zero or more tuples > containing the row data, and a file trailer. Headers and data are in > network byte order. " Actually binary mode is faster in some situations, and slower with other, in any case it should save space in backup files or during transmission (e.g. binary tz takes 8 bytes, text takes more) But this may be due to encoding of timestamptz, you could have 8 version compiled with float timestamps, and 9 with integer tiemstamps or vice versa. Regards, Radek