MIME-Version: 1.0
References: <CAFCRh--O5DdQ=cR_Ph5sKoVurpC4A1F1_qB1H9VuiwAZR=F5+A@mail.gmail.com>
 <CAFCRh-9W8mXh9MfRe-Z5bAcN5FVbsXuSW60-QPVMB1jHC4+bzQ@mail.gmail.com> <32fa25b0-7e5e-40d2-b3a8-e1cd1dbd833d@aklaver.com>
In-Reply-To: <32fa25b0-7e5e-40d2-b3a8-e1cd1dbd833d@aklaver.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 7 Nov 2024 18:14:46 +0100
Message-ID: <CAFCRh-_7CqU5Or7N+RYj_5XVz_C46o5v4vD8w2G22BTcKU6bGQ@mail.gmail.com>
Subject: Re: About the stability of COPY BINARY data
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_7CqU5Or7N%2BRYj_5XVz_C46o5v4vD8w2G22BTcKU6bGQ%40mail.gmail.com>
Precedence: bulk

On Thu, Nov 7, 2024 at 5:37=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver=
.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 c=
hange
> > 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 "caref=
ul"
    about COPY BINARY's stability, thus my asking for confirmation here.