7.4 COPY BINARY Format Change

Lee Kindness <lkindness@csl.co.uk> writes:

I've attached a patch which lets COPY read in the 7.1 format. However
i'm not convinced this is the right way to go - I think the format
which is output by 7.4 should be identical to the 7.1 format.

You are greatly underestimating the changes that occurred in COPY BINARY.
If the format difference had been as minor as you think, I would not
have gratuitously broken compatibility.

The real change that occurred here is that the individual data fields
go through per-datatype send/receive routines, which in addition to
implementing a mostly machine-independent binary format also provide
defenses against bad input data.

To continue to read the old COPY BINARY format, we'd have to bypass
those routines and allow direct read of the internal data formats.
This was a security risk before and would be a much bigger one now,
seeing that we allow COPY BINARY FROM STDIN to unprivileged users. It
is trivial to crash the backend by feeding it bad internal-format data.

(I don't believe that the patch works anyway, given that you aren't doing
anything to disable use of the per-datatype receive routine. It might
work as-is for text fields, and for integers on bigendian machines, but
not for much else.)

We are not going back to the pre-7.4 format. Sorry.

regards, tom lane

Tom,

Tom Lane writes:

Lee Kindness <lkindness@csl.co.uk> writes:

I've attached a patch which lets COPY read in the 7.1 format. However
i'm not convinced this is the right way to go - I think the format
which is output by 7.4 should be identical to the 7.1 format.

You are greatly underestimating the changes that occurred in COPY BINARY.
If the format difference had been as minor as you think, I would not
have gratuitously broken compatibility.

The real change that occurred here is that the individual data fields
go through per-datatype send/receive routines, which in addition to
implementing a mostly machine-independent binary format also provide
defenses against bad input data.

To continue to read the old COPY BINARY format, we'd have to bypass
those routines and allow direct read of the internal data formats.
This was a security risk before and would be a much bigger one now,
seeing that we allow COPY BINARY FROM STDIN to unprivileged users. It
is trivial to crash the backend by feeding it bad internal-format
data.

Well in that case the docs need attention. They describe the
"envelope" surrounding the tuples, but no mention is made of the
format they are in. It is reasonable to assume that this format was
the native binary format, as in earlier releases.

I've got applications which create binary "bulkload" files which are
loaded into the database using COPY FROM. Currently they write the
data out using simple fwrite calls. What do I need to do to make this
code work with 7.4? Is there any docs describing the "binary" format
for each of the datatypes or do I need to reverse-engineer a dump file
or look in the source? Are the routines in libpq/pqformat.c intended
to be used by client applications to read/write the binary COPY files?
If so they also need documented in the libpq docs and that
documentation linked to from the COPY docs.

(I don't believe that the patch works anyway, given that you aren't doing
anything to disable use of the per-datatype receive routine. It might
work as-is for text fields, and for integers on bigendian machines, but
not for much else.)

Yeah, I didn't spend a lot of effort in that respect - after all I
said myself I didn't see the patch being accepted...

We are not going back to the pre-7.4 format. Sorry.

Well as pointed out in my earlier message nothing has changed which
requires the format to change - there is no real reason it's now
"PGCOPY" and the integer layout field has disappeared. The change for
the byte swapping should have been indicated by an entry in the flags
field.

I am still willing to make a patch which does this (to aid those
writing COPY format files) and to fully support the reading of the old
format tuples. However i'm not going to waste both our time if this
patch is not going to be positively considered...

I think it's worthwhile reiterating that this change will be a real
pain for PostgreSQL users when migrating to 7.4. To be honest i'd
probably stick with 7.3 until the subsequent major release. Have a
think what benefit this incompatibility gives users of COPY
BINARY... I can't think of much use of byte swapping when 99% of the
use of COPY BINARY FROM is to improve performance over using
INSERT. Both the reader and writer will be using the same binary
integer/float/etc formats!

So, will I look at implementing these changes? Or not?

L.

Lee Kindness <lkindness@csl.co.uk> writes:

The real change that occurred here is that the individual data fields
go through per-datatype send/receive routines, which in addition to
implementing a mostly machine-independent binary format also provide
defenses against bad input data.

Well in that case the docs need attention. They describe the
"envelope" surrounding the tuples, but no mention is made of the
format they are in. It is reasonable to assume that this format was
the native binary format, as in earlier releases.

Yeah, there should be some mention of that in the COPY ref page I guess
--- it's mentioned in the frontend protocol chapter, but not under COPY.
In my defense I'd point out that the contents of individual fields have
never been documented under COPY.

What do I need to do to make this
code work with 7.4? Is there any docs describing the "binary" format
for each of the datatypes or do I need to reverse-engineer a dump file
or look in the source?

ATM, I'd recommend looking in the sources to see what the datatype
send/receive routines do.

I have been thinking about documenting the binary formats during beta,
but am unsure where to put the info. We never documented the internal
formats before either, so there's no obvious place.

Are the routines in libpq/pqformat.c intended
to be used by client applications to read/write the binary COPY files?

They are not designed to be used outside the backend environment,
although possibly some enterprising person could adapt them. I am not
sure there's any value in it though. Copying the backend code helps
only if what you want to get out of the transmission is the same as the
backend's internal format, which for anything more complex than
int/float/text seems a bit dubious.

We are not going back to the pre-7.4 format. Sorry.

Well as pointed out in my earlier message nothing has changed which
requires the format to change - there is no real reason it's now
"PGCOPY" and the integer layout field has disappeared.

Given that the interpretation of the field contents has changed
drastically, I thought it better to make an obvious incompatible
change. We could perhaps have kept the skeleton the same, but to
what end? An app trying to read or write the file as if it were
pre-7.4 data would fail miserably anyway.

I am still willing to make a patch which does this (to aid those
writing COPY format files) and to fully support the reading of the old
format tuples. However i'm not going to waste both our time if this
patch is not going to be positively considered...

My vote will be to reject it because of the security problem.

I can't think of much use of byte swapping when 99% of the
use of COPY BINARY FROM is to improve performance over using
INSERT. Both the reader and writer will be using the same binary
integer/float/etc formats!

You must think that the universe consists exclusively of Intel hardware.
In my view, standardizing on a machine-independent binary format will
greatly *expand* the usefulness of COPY BINARY, since the files will not
be tied to a single architecture.

regards, tom lane

Tom Lane writes:

Lee Kindness <lkindness@csl.co.uk> writes:

Well in that case the docs need attention. They describe the
"envelope" surrounding the tuples, but no mention is made of the
format they are in. It is reasonable to assume that this format was
the native binary format, as in earlier releases.

Yeah, there should be some mention of that in the COPY ref page I guess
--- it's mentioned in the frontend protocol chapter, but not under COPY.
In my defense I'd point out that the contents of individual fields have
never been documented under COPY.

True, the docs have always skipped the specifics for the
tuples. But now that the format has evolved beyond a simple dump of
the bytes the tuple format does need discussing.

What do I need to do to make this
code work with 7.4? Is there any docs describing the "binary" format
for each of the datatypes or do I need to reverse-engineer a dump file
or look in the source?

ATM, I'd recommend looking in the sources to see what the datatype
send/receive routines do.

I have been thinking about documenting the binary formats during beta,
but am unsure where to put the info. We never documented the internal
formats before either, so there's no obvious place.

Perhaps the documentation of the binary format should be taken out of
the COPY docs and moved into the client interfaces documentation? the
COPY docs would of course reference the new location. Just now the
tuples could be "documented" simply by referring the reader to the
relevant functions in the relevant source files. After all the source
is the best documentation for this sort of thing.

Are the routines in libpq/pqformat.c intended
to be used by client applications to read/write the binary COPY files?

They are not designed to be used outside the backend environment,
although possibly some enterprising person could adapt them. I am not
sure there's any value in it though. Copying the backend code helps
only if what you want to get out of the transmission is the same as the
backend's internal format, which for anything more complex than
int/float/text seems a bit dubious.

I think there is a lot of use for a binary COPY file API within libpq
- routines to open a file, write/read a header and write/read common
datatypes. This would remove the need for most people using the binary
version of COPY to even know the file format. This would also isolate
people who use this API from any future changes.

Would libpq or contrib be the best place for this? Would you agree
this is a good idea for 7.4? I've already got something along these
lines:

extern FILE *lofsdb_Bulk_Open(char **filename);
extern void lofsdb_Bulk_Close(FILE *f, char *filename);
extern void lofsdb_Bulk_Write_NCols(FILE *f, short ncols);
extern void lofsdb_Bulk_Write(FILE *f, void *data, size_t sz, size_t count, short ind);
extern void lofsdb_Bulk_WriteText(FILE *f, char *data, short ind);
extern void lofsdb_Bulk_WriteBytea(FILE *f, char *data, size_t len, short ind);
extern void lofsdb_Bulk_WriteTime(FILE *f, double t, short ind);
extern void lofsdb_Bulk_WriteTimeNow(FILE *f);

which could form the basis of a contrib module to handle writing out
7.1 through to 7.4 format files. Naturally lofsdb_Bulk_Write needs to
go and be replaced by specific functions.

Well as pointed out in my earlier message nothing has changed which
requires the format to change - there is no real reason it's now
"PGCOPY" and the integer layout field has disappeared.

Given that the interpretation of the field contents has changed
drastically, I thought it better to make an obvious incompatible
change. We could perhaps have kept the skeleton the same, but to
what end? An app trying to read or write the file as if it were
pre-7.4 data would fail miserably anyway.

Yeah, but someone (actually you!) went to the effort of making the 7.1
format extensible and documenting it as such... It could have handled
the changes.

I am still willing to make a patch which does this (to aid those
writing COPY format files) and to fully support the reading of the old
format tuples. However i'm not going to waste both our time if this
patch is not going to be positively considered...

My vote will be to reject it because of the security problem.

In which case I think my time would be better spent looking at the API
described above.

I can't think of much use of byte swapping when 99% of the
use of COPY BINARY FROM is to improve performance over using
INSERT. Both the reader and writer will be using the same binary
integer/float/etc formats!

You must think that the universe consists exclusively of Intel hardware.
In my view, standardizing on a machine-independent binary format will
greatly *expand* the usefulness of COPY BINARY, since the files will not
be tied to a single architecture.

Well my testing (or lack of) of the earlier patch would seem to
indicate it was done on non-Intel box (Solaris)! I've got access here
to Solaris (2.5 through to 9), AIX (4.1 to 4.3.3), HPUX (9, 10, 11)
and of course Linux flavours - our apps run on these UNIX versions. So
i'm well aware of binary format issues (for fun look into the SEG-D
and SEG-Y formats used within the seismic industry).

However, is COPY BINARY meant/designed to be used as transfer or
backup mechanism? I have trouble coming up with many uses where a
binary file generated on one server would be loaded into another
server running on a different architecture.

Regards, Lee.

Lee Kindness <lkindness@csl.co.uk> writes:

However, is COPY BINARY meant/designed to be used as transfer or
backup mechanism?

I think you're overlooking a key consideration: COPY BINARY is not
an isolated feature anymore. By design it uses the same data
representations as are used for binary query parameters and results
in the rest of the 7.4 FE/BE protocol.

I could see some value in providing byte-swapping routines in libpq
to convert between local and network representations for integers and
floats. The underlying facilities (ntohl etc) are readily available,
of course, but it's a small matter that is easy to get wrong.

I'm not sure it's worth packaging up COPY BINARY logic per se. I think
you'd end up with an API not materially simpler than dealing with the
format directly. And I'm unconvinced it'd actually be used widely,
whereas I do expect binary transfer of individual values to be common.

regards, tom lane

Tom,

Tom Lane writes:

Lee Kindness <lkindness@csl.co.uk> writes:

However, is COPY BINARY meant/designed to be used as transfer or
backup mechanism?

I think you're overlooking a key consideration: COPY BINARY is not
an isolated feature anymore. By design it uses the same data
representations as are used for binary query parameters and results
in the rest of the 7.4 FE/BE protocol.

Yeah, what i've overlooked is that an implementation detail now forms
part of an external interface into PostgreSQL - this is a major
change.

I could see some value in providing byte-swapping routines in libpq
to convert between local and network representations for integers and
floats. The underlying facilities (ntohl etc) are readily available,
of course, but it's a small matter that is easy to get wrong.

I'm not sure it's worth packaging up COPY BINARY logic per se. I think
you'd end up with an API not materially simpler than dealing with the
format directly. And I'm unconvinced it'd actually be used widely,
whereas I do expect binary transfer of individual values to be common.

Would I be right is guessing a binary CURSOR would return in values in
the same format as a binary COPY, hence your expectation of more
individual transfers/conversions? Actually with the new FE/BE protocol
there is little call for the binary cursor now, yeah?

What I proposed in my email yesterday is really just completing the
new functions (PQnfields, PQputCopyData, PQgetCopyData and friends)
described at:

http://developer.postgresql.org/docs/postgres/libpq-copy.html

so they don't stop at just giving you a blob of binary data and saying
it has n fields - functions would be available to iterate over the
fields and get the data out in a format which is immediately
useful. Without this do you not think PQgetCopyData is of limited use
except for being used by psql (which I guess isn't using it yet). Same
for the writing functions.

This is slightly different from my earlier example (on the connection
rather than file-based) but functionally similar.

BTW, do you have any examples of using PQgetCopyData - none in the
source and can't find anything with Google.

Regards, Lee.

Lee Kindness <lkindness@csl.co.uk> writes:

Would I be right is guessing a binary CURSOR would return in values in
the same format as a binary COPY, hence your expectation of more
individual transfers/conversions? Actually with the new FE/BE protocol
there is little call for the binary cursor now, yeah?

Binary cursors per se are obsolete --- you can get the result of any
query in binary form, if you ask politely. And you can send data in
binary form, too, using parameters. I have not gotten around to
benchmarking a prepared INSERT with binary parameters against a binary
COPY, but I expect the differential is not nearly as bad as it is for a
source-form INSERT. It could well be that binary COPY is obsolete for
the purposes you're using it for.

so they don't stop at just giving you a blob of binary data and saying
it has n fields - functions would be available to iterate over the
fields and get the data out in a format which is immediately
useful.

You can iterate over the fields of a text COPY and get the data out,
too, if you think it useful. I haven't seen a huge call for support
for that, and so I don't believe it's important for binary COPY either.
I do see a need for converting individual binary data values in the
context of query parameters and results.

regards, tom lane

I've just sent off patches to pgsql-patches to:

1. Slight clarification to the COPY BINARY format docs

2. A contrib/binarycopy module which wraps-up the detail of creating a
file which can be used as input to COPY BINARY. User can create either
7.1 or 7.4 format files using the same API, without needing to know
the file format, without needing to know the individual binary
format of each field and without needing to explicitly byte-swap.

#2 will be used extensively within Concept Systems code which
interfaces to PostgreSQL. It really simplifies the creation of the
binary files.

Thanks, Lee.

Lee Kindness writes: