Thread: binary compat
Hi, I've been idly thinking about binary COPY and recent performance efforts spent on it. The main problem I have with binary output is that I never know when it'll be any useful (except very same hardware and PostgreSQL setup)... useful meaning I get to read it back into some database. Would providing the following two functions help? SELECT * FROM pg_binary_compat(); -> version, timestamp ondisk format, architecture, you name it SELECT pg_binary_compat(version, timestamp ondisk format, ...) -> bool Equiped with this I would first setup a (somewhat portable) virtual machine where I'm sure I'm able to load the binary files, then get to use COPY BINARY everywhere possible: I have a way to easily tell when not to use it. First example would be to teach londiste about this... Now writing the function doesn't sound a lot of fun, but I don't know what to compare to be able to decide whether COPY output will get read correctly on input. It even looks to me like in some cases this would work accross major PostgreSQL versions? Or would that depend on used types, so we'd need a variant taking those into consideration? Anyone interrested in spelling out the checks to get done to implement the functions, or to implementing it, provided there's no obvious reason why it can not be made trustworthy? Regards, -- dim
On 9/1/09, Dimitri Fontaine <dfontaine@hi-media.com> wrote: > I've been idly thinking about binary COPY and recent performance efforts > spent on it. The main problem I have with binary output is that I never > know when it'll be any useful (except very same hardware and PostgreSQL > setup)... useful meaning I get to read it back into some database. > > Would providing the following two functions help? > > SELECT * FROM pg_binary_compat(); > -> version, timestamp ondisk format, architecture, you name it > > SELECT pg_binary_compat(version, timestamp ondisk format, ...) > -> bool > > Equiped with this I would first setup a (somewhat portable) virtual > machine where I'm sure I'm able to load the binary files, then get to > use COPY BINARY everywhere possible: I have a way to easily tell when > not to use it. First example would be to teach londiste about this... > > Now writing the function doesn't sound a lot of fun, but I don't know > what to compare to be able to decide whether COPY output will get read > correctly on input. It even looks to me like in some cases this would > work accross major PostgreSQL versions? Or would that depend on used > types, so we'd need a variant taking those into consideration? > > Anyone interrested in spelling out the checks to get done to implement > the functions, or to implementing it, provided there's no obvious reason > why it can not be made trustworthy? Based on plproxy experience with binary i/o, the requirements are: - same major.minor [Perhaps checking catversion would be better?] - same integer_datetimes - same server_encoding This seems to cover most (?) builtin types. Arch details (32/64 bit, little/big endian) should not matter, as binary i/o functions are supposed to use arch-independent format. As plproxy uses explicit casting ($2::typname) it did not need to worry about any oid differences. I have not looked at binary COPY details, whether it can also ignore oids. -- marko
Dimitri Fontaine wrote: > Hi, > > I've been idly thinking about binary COPY and recent performance efforts > spent on it. The main problem I have with binary output is that I never > know when it'll be any useful (except very same hardware and PostgreSQL > setup)... useful meaning I get to read it back into some database. > If you want a binary cross compatible, how about something well known, standardized, simple and cross platform like XDR? http://www.faqs.org/rfcs/rfc1014.html -- Fools ignore complexity. Pragmatists suffer it. Some can avoid it. Geniuses remove it.
> I've been idly thinking about binary COPY and recent performance efforts > spent on it. The main problem I have with binary output is that I never > know when it'll be any useful (except very same hardware and PostgreSQL > setup)... useful meaning I get to read it back into some database. I posted about patching binary copy to improve speed some time ago... I'm a bit out of time right now to work on it, but I should have the time in September to submit a patch. > Would providing the following two functions help? > > SELECT * FROM pg_binary_compat(); > -> version, timestamp ondisk format, architecture, you name it > > SELECT pg_binary_compat(version, timestamp ondisk format, ...) > -> bool > > Equiped with this I would first setup a (somewhat portable) virtual > machine where I'm sure I'm able to load the binary files, then get to > use COPY BINARY everywhere possible: I have a way to easily tell when > not to use it. First example would be to teach londiste about this... > > Now writing the function doesn't sound a lot of fun, but I don't know > what to compare to be able to decide whether COPY output will get read > correctly on input. It even looks to me like in some cases this would > work accross major PostgreSQL versions? Or would that depend on used > types, so we'd need a variant taking those into consideration? > > Anyone interrested in spelling out the checks to get done to implement > the functions, or to implementing it, provided there's no obvious reason > why it can not be made trustworthy? Actually, I've been surprised to discover that if you COPY a table which contains integers to a file in binary format, you can reload it into a table which contains dates (still using binary format) and Postgres will not complain, since a date is an integer too, of the same length... so you'll get completely garbled data, of course, and no error message. Well, the data isn't really garbled, but it's just the interpretation of it that is different (you could dump it again and reload it into an integer table, and you'd find your original integers, interpreted as integers this time). You could probably load a bunch of 4-byte text strings into an INT column, too. Thinking about it again, I wonder why I was surprised, since the binary format doesn't include any column type information. But given how careful postgres is with correctness, this could be considered a bug... it is very careful on parsing text data, and not at all on binary. What makes text copy slow is all the parsing : turning text into datums is very complex (look at the source code of strtod() in glibc...) Validation is simple : including it in binary copy shouldn't slow it down. So, I believe the COPY binary file format should include this information : 1- everything you proposed - version - timestamp format - etc (or perhaps a "binary format version" which is known for each type included in the table) 2- list of column names and types - check at COPY FROM time and issue appropriate errors : "cannot copy a column of type DATE from binary data of type INTEGER" - have a special command or a little tool (anything, even a simple python script) that can look at a COPY header and write the appropriate CREATE TABLE statement for your convenience : have you ever grumbled, when developing an app, "hm, I want to reload this dump I did a week ago for testing... what was the table like at this time ?..."