I've just noticed that COPY BINARY is pretty thoroughly broken by TOAST,
because what it does is to dump out verbatim the bytes making up each
tuple of the relation.  In the case of a moved-off value, you'll get
the toast reference, which is not going to be too helpful for reloading
the table data.  In the case of a compressed-in-line datum, you'll at
least have all the data there, but the COPY BINARY reader will crash
and burn when it sees it.

Fixing this while retaining backwards compatibility with the existing
COPY BINARY file format is possible, but it seems rather a headache:
we'd need to detoast all the toasted columns, then heap_formtuple a
new tuple containing the expanded data, and finally write that out.
(Can't do it on a field-by-field basis because the file format requires
the total tuple size to precede the tuple data.)  Kind of ugly.

The existing COPY BINARY file format is entirely brain-dead anyway; for
example, it wants the number of tuples to be stored at the front, which
means we have to scan the whole relation an extra time to get that info.
Its handling of nulls is bizarre, too.  I'm thinking this might be a
good time to abandon backwards compatibility and switch to a format
that's a little easier to read and write.  Does anyone have an opinion
pro or con about that?

			regards, tom lane

Fri, 01 Dec 2000 17:35:33 -0500
Tom Lane <tgl@sss.pgh.pa.us>
COPY BINARY is broken...
* Tom Lane <tgl@sss.pgh.pa.us> [001201 14:42] wrote:
> I've just noticed that COPY BINARY is pretty thoroughly broken by TOAST,
> because what it does is to dump out verbatim the bytes making up each
> tuple of the relation.  In the case of a moved-off value, you'll get
> the toast reference, which is not going to be too helpful for reloading
> the table data.  In the case of a compressed-in-line datum, you'll at
> least have all the data there, but the COPY BINARY reader will crash
> and burn when it sees it.
> 
> Fixing this while retaining backwards compatibility with the existing
> COPY BINARY file format is possible, but it seems rather a headache:
> we'd need to detoast all the toasted columns, then heap_formtuple a
> new tuple containing the expanded data, and finally write that out.
> (Can't do it on a field-by-field basis because the file format requires
> the total tuple size to precede the tuple data.)  Kind of ugly.
> 
> The existing COPY BINARY file format is entirely brain-dead anyway; for
> example, it wants the number of tuples to be stored at the front, which
> means we have to scan the whole relation an extra time to get that info.
> Its handling of nulls is bizarre, too.  I'm thinking this might be a
> good time to abandon backwards compatibility and switch to a format
> that's a little easier to read and write.  Does anyone have an opinion
> pro or con about that?

BINARY COPY scared the bejeezus out of me, anyone using the interface
is asking for trouble and supporting it seems like a nightmare, I
would rip it out.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Fri, 1 Dec 2000 14:54:26 -0800
Alfred Perlstein <bright@wintelcom.net>
Re: COPY BINARY is broken...
Alfred Perlstein <bright@wintelcom.net> writes:
> I would rip it out.

I thought about that too, but was afraid to suggest it ;-)

How many people are actually using COPY BINARY?

			regards, tom lane

Fri, 01 Dec 2000 17:56:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: COPY BINARY is broken... 
* Tom Lane <tgl@sss.pgh.pa.us> [001201 14:57] wrote:
> Alfred Perlstein <bright@wintelcom.net> writes:
> > I would rip it out.
> 
> I thought about that too, but was afraid to suggest it ;-)

I think you'd agree that you have more fun and important things to
do than to deal with this yucky interface. :)

> How many people are actually using COPY BINARY?

I'm not using it. :)

How about adding COPY XML?











(kidding of course about the XML, but it would make postgresql more
buzzword compliant :) )

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Fri, 1 Dec 2000 15:05:11 -0800
Alfred Perlstein <bright@wintelcom.net>
Re: COPY BINARY is broken...
At 03:05 PM 12/1/00 -0800, Alfred Perlstein wrote:

>How about adding COPY XML?
>(kidding of course about the XML, but it would make postgresql more
>buzzword compliant :) )

Hey, we could add a parser and call the module MyXML ...



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Fri, 01 Dec 2000 15:47:58 -0800
Don Baccus <dhogaza@pacifier.com>
Re: COPY BINARY is broken...
On Fri, Dec 01, 2000 at 05:56:57PM -0500, Tom Lane wrote:
> Alfred Perlstein <bright@wintelcom.net> writes:
> > I would rip it out.
> 
> I thought about that too, but was afraid to suggest it ;-)
> 
> How many people are actually using COPY BINARY?
> 
I have used it, I don't think I'm actually using right now.  But, it was 
very handy. (Once I finally figured out the format through trial and error,
ouch!)  It is very nice to be able to just dump tuples in, instead of having
to format them to text, then the database has to put them back to binary
again.  So, an alternative with a clean interface would be very much
appreciated.


Sat, 2 Dec 2000 16:09:17 -0800
Samuel Sieb <samuel@sieb.net>
Re: COPY BINARY is broken...
> > Its handling of nulls is bizarre, too.  I'm thinking this might be a
> > good time to abandon backwards compatibility and switch to a format
> > that's a little easier to read and write.  Does anyone have an opinion
> > pro or con about that?
> 
> BINARY COPY scared the bejeezus out of me, anyone using the interface
> is asking for trouble and supporting it seems like a nightmare, I
> would rip it out.

Tom, just keep in mind that the format is documented in copy.sgml.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Dec 2000 15:52:16 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: COPY BINARY is broken...
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>>> Its handling of nulls is bizarre, too.  I'm thinking this might be a
>>>> good time to abandon backwards compatibility and switch to a format
>>>> that's a little easier to read and write.  Does anyone have an opinion
>>>> pro or con about that?
>> 
>> BINARY COPY scared the bejeezus out of me, anyone using the interface
>> is asking for trouble and supporting it seems like a nightmare, I
>> would rip it out.

> Tom, just keep in mind that the format is documented in copy.sgml.

Not documented *correctly*, I notice.  There are at least two errors,
plus the rather major omission that <tuple data> is not explained.

			regards, tom lane

Sun, 10 Dec 2000 17:34:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: COPY BINARY is broken... 

Tom, this is fixed, right?

> I've just noticed that COPY BINARY is pretty thoroughly broken by TOAST,
> because what it does is to dump out verbatim the bytes making up each
> tuple of the relation.  In the case of a moved-off value, you'll get
> the toast reference, which is not going to be too helpful for reloading
> the table data.  In the case of a compressed-in-line datum, you'll at
> least have all the data there, but the COPY BINARY reader will crash
> and burn when it sees it.
> 
> Fixing this while retaining backwards compatibility with the existing
> COPY BINARY file format is possible, but it seems rather a headache:
> we'd need to detoast all the toasted columns, then heap_formtuple a
> new tuple containing the expanded data, and finally write that out.
> (Can't do it on a field-by-field basis because the file format requires
> the total tuple size to precede the tuple data.)  Kind of ugly.
> 
> The existing COPY BINARY file format is entirely brain-dead anyway; for
> example, it wants the number of tuples to be stored at the front, which
> means we have to scan the whole relation an extra time to get that info.
> Its handling of nulls is bizarre, too.  I'm thinking this might be a
> good time to abandon backwards compatibility and switch to a format
> that's a little easier to read and write.  Does anyone have an opinion
> pro or con about that?
> 
> 			regards, tom lane
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 22 Jan 2001 22:46:54 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: COPY BINARY is broken...
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom, this is fixed, right?

Yes.

			regards, tom lane

Mon, 22 Jan 2001 23:20:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: COPY BINARY is broken... 