Subject: About the stability of COPY BINARY data

From: Dominique Devienne <[email-masked]>
Date: Wed, 6 Nov 2024 17:20:12 +0100

>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.

Thanks for any insights, --DD

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 7 Nov 2024 08:37:35 -0800

> columns), and I wonder how bad it is long term, and across PostgreSQL

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)."

-- 
Adrian Klaver
[email-masked]

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 7 Nov 2024 18:14:46 +0100

> are found in the src/backend/utils/adt/ directory of the source

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.

---

From: "Daniel Verite" <[email-masked]>
Date: Thu, 07 Nov 2024 18:39:02 +0100

Dominique Devienne wrote:

> 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?

For the timestamp types, I think these functions were
sending/expecting float8 (before version 7.3), and then float8 or
int64 depending on the server configuration up until 9.6, and since
then int64 only.
The same for the "time" field of the interval type.
There is still an "integer_datetimes" GUC reflecting this.

Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 7 Nov 2024 18:55:03 +0100

> There is still an "integer_datetimes" GUC reflecting this.

Thanks. So it did happen in a distant past.
Anything below 14 is of no concern to me though.
So again, it does sound like changes are unlikely.

And I haven't seen anything not network-byte-order,
as far architecture is concerned.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 7 Nov 2024 10:04:40 -0800

> So again, it does sound like changes are unlikely.

Yeah that is implied by:

https://www.postgresql.org/docs/current/pgupgrade.html

"Major PostgreSQL releases regularly add new features that often change 
the layout of the system tables, but the internal data storage format 
rarely changes. "

The COPY warning is there as heads up that it is a possibility.

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 7 Nov 2024 19:10:59 +0100

> > as far architecture is concerned.

But the COPY BINARY format and "the internal data storage format"
are two separate things Adrian, AFAIK.

Using binds and result sets in binary mode is part of the protocol in a way,
and not an internal implementation detail, like internal format for
tables on disk.

I'm sure I'm not the only one that using binary mode for PostgreSQL,
and any change in _send and _recv functions across versions, or have their
results be architecture dependent, would break many client codes, if
it happened.
And COPY BINARY's outer format is also public and documented, so can't
change either.

That's why I'm insisting on that phrase in the documentation, which
gives the wrong impressions IMHO.
Shouldn't it be removed or amended? Thanks, --DD