Re: binary compat

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