Re: About the stability of COPY BINARY data

On Thu, Nov 7, 2024 at 5:37 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 11/6/24 08:20, Dominique Devienne wrote:
> >>From https://www.postgresql.org/docs/current/sql-copy.html:
> > |> binary-format file is less portable across machine architectures
> > and PostgreSQL versions
> >
> > In my experience, the binary encoding of binding/resultset/copy is
> > endian neutral (network byte order), so what is the less portable
> > across machine architectures that warning about?
> >
> > Also, does the code for per-type _send() and _recv() functions really change
> > across versions of PostgreSQL? How common are instances of such
> > changes across versions? Any examples of such backward-incompatible
> > changes, in the past?
> >
> > The binary data contains OIDs, but if sticking to built-in types,
> > which OIDs are unlikely to change across versions?
> >
> > I'm obviously storing COPY BINARY data (we have lots of bytea
> > columns), and I wonder how bad it is long term, and across PostgreSQL
> > versions.
>
> If I where to hazard a guess this plays a part:
>
> https://www.postgresql.org/docs/current/sql-copy.html
>
> "To determine the appropriate binary format for the actual tuple data
> you should consult the PostgreSQL source, in particular the *send and
> *recv functions for each column's data type (typically these functions
> are found in the src/backend/utils/adt/ directory of the source
> distribution)."

Hi Adrian.

Well, sure. The questions above are whether those type-specific formats are:
1) architecture dependent. (that's not my experience).
2) change across PostgreSQL versions.

Not what the actual formats are. --DD

PS: I'm surprised I didn't get answers. Seems to me to doc is overly "careful"
about COPY BINARY's stability, thus my asking for confirmation here.