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
