Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] COPY BINARY is broken...
Bruce Momjian [EMAIL PROTECTED] writes: Tom, this is fixed, right? Yes. regards, tom lane
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] COPY BINARY is broken...
Bruce Momjian [EMAIL PROTECTED] 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
Re: [HACKERS] COPY BINARY is broken...
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
Re: [HACKERS] COPY BINARY is broken...
Adriaan Joubert [EMAIL PROTECTED] 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
[HACKERS] COPY BINARY is broken...
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
Re: [HACKERS] COPY BINARY is broken...
Alfred Perlstein [EMAIL PROTECTED] 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
RE: [HACKERS] COPY BINARY is broken...
Alfred Perlstein [EMAIL PROTECTED] 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
RE: [HACKERS] 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
Re: [HACKERS] COPY BINARY is broken...
* Tom Lane [EMAIL PROTECTED] [001201 14:57] wrote: Alfred Perlstein [EMAIL PROTECTED] 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 - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [HACKERS] 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 [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.