COPY BINARY is broken...

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?

Switch to new format.

Vadim

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?

It could be useful if only single scan would be required.
But I have no strong opinion about keeping it.

Vadim

* 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."

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

* 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."

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.

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.

Hi,

I would very much like some way of writing binary data to a database.
Copy binary recently broke on me after upgrading to 7.0. I have large
simulation codes and writing lots of floats to the database by
converting them to text first is 1) a real pain, 2) slow and 3) can lead
to unexpected loss in precision.

I think binary writes would actually be solved better and safer through
some type of CORBA interface, but previous discussions seemed to
indicate that that is even more of a pain than fixing the current binary
interface.

So I agree that the current version is a problem, but I do think
something needs to be put in place. Not everybody only writes a few
numbers from a web page into the database -- some have masses of data to
dump into a database. For all I care it doesn't even have to look like
SQL, but can be purely accessible through libpq.

Adriaan

Adriaan Joubert <a.joubert@albourne.com> writes:

Copy binary recently broke on me after upgrading to 7.0.

I think you're talking about binary copy via the frontend, which has a
different set of problems. To fix that, we need to make some protocol
changes, which would (preferably) also apply to non-binary frontend
copy, which would create a compatibility problem. (The reason the
protocol is broken is there's no reasonable way to find or signal the
end of the COPY data stream after an error.)

I think that's worth doing, but there's no time to design and implement
it for 7.1. Maybe for 7.2.

I think binary writes would actually be solved better and safer through
some type of CORBA interface,

CORBA would provide a more machine-independent interface, but migrating
to CORBA would be a huge task, and I'm not sure the payoff is worth
it...

regards, tom lane

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

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

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom, this is fixed, right?

Yes.

regards, tom lane