On Mon, Feb 28, 2011 at 2:42 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Sun, Feb 27, 2011 at 7:39 PM, Craig Ringer
>> AFAIK, the `timestamp' type moved from a floating-point to an integer
>> representation internally, which would've affected the binary protocol
>> representation. That was even a compile-time config option, so it could be
>> different between two different Pg installs with the same version.
>
> Actually, this has always been a compile time option on the server as
> far as i remember and there is protocol support for it -- libpq tells
> you how it has been set...you've always had to deal with this
It should be 'integer_datetimes' setting reported by PQparameterStatus I assume.
> (libpqtypes, which I've mentioned several times as the correct way to
> 'do' binary with libpq, handles this for you). Only the default
> setting was changed. Timestamps are the only type afaik that are like
> this so they are something of a special case. There *was* a binary
> wire format change for timestamps some time back iirc that was
> unrelated to the int/float property. They are unlikely to change
> again unless the server's internal representation changes.
>
> Small aside about timestamps: aside from bytea they stand to benefit
> the most out of all the common types by moving to binary, especially
> if you are moving them to/from something like a C struct tm on the
> client. int, float, etc are hardly noticeable and barely worth the
> trouble.
Not only barely noticeable: I've talked with somebody who has done
some performance test and has found better performance in text mode
for small integers: apparently the time spent parsing the integer text
format was less than the overhead to pass the 8 bits of an int64 just
to represent '3'. Probably an edge case but it restates that for ints
there isn't a lot to gain from the binary format.
So it looks like we could assume the bin representation relatively
stable, but still at each postgresql major version it should be
checked if the internal representation of any data type changed. OTOH
not so many data types would really benefit from being transferred as
binary (from a quick scan to the data types list, the datetime types
look indeed the ones where there would be the most to gain).
-- Daniele
