The following bug has been logged on the website:
Bug reference: 18926
Logged by: Robb Shecter
Email address: robb@public.law
PostgreSQL version: 17.5
Operating system: Both Ubuntu and MacOS
Description:
Thank you — that’s a crucial update. Here's the revised version of the
message for the `pgsql-bugs@postgresql.org` mailing list, now reflecting
that the issue **also occurs on Ubuntu in production**, making it likely a
PostgreSQL 17.5 upstream bug rather than a Homebrew or ARM-specific issue.
Hi all,
I'm encountering a bug in PostgreSQL 17.5 where a `COPY ... TO STDOUT WITH
(FORMAT binary)` operation produces malformed output: the file begins with
`GPOCYP` instead of the expected `PGCOPY`. This behavior occurs consistently
across environments, including:
- macOS 14.4.1 on Apple Silicon (M1), PostgreSQL 17.5 installed via Homebrew
- Ubuntu 22.04 in a production environment, PostgreSQL 17.5 installed via
apt/postgresql.org
This suggests the issue is not specific to platform, CPU architecture, or
package manager.
---
### Steps to Reproduce
1. Run this SQL script:
```sql
-- mini_test.sql
CREATE TEMP TABLE m1_psql_test (id int);
INSERT INTO m1_psql_test VALUES (1);
COPY (SELECT * FROM m1_psql_test) TO STDOUT WITH (FORMAT binary);
````
2. Execute:
```bash
psql your_db -f mini_test.sql > output.bin
hexdump -C output.bin | head -n 3
```
3. Output (from both macOS M1 and Ubuntu):
```
00000000 47 50 4f 43 59 50 ff 0a 0d 0a 00 00 00 00 00 00
|GPOCYP..........|
```
Expected:
```
00000000 50 47 43 4f 50 59 0a ff 0d 0a 00 00 00 00 00 00
|PGCOPY..........|
```
---
### Notes
* This issue appears in both `psql -c "COPY ..."` and `psql -f` execution
styles.
* The symptom — flipped bytes like `PG` → `GP`, `\n\xff` → `\xff\n` — looks
like a 16-bit byte swap, suggesting a possible endianness bug or
serialization bug.
* The output breaks any downstream tooling expecting a valid binary COPY
stream and results in errors like `COPY file signature not recognized`.
---
### Environment Info
**macOS (M1 Homebrew)**:
* `psql (PostgreSQL) 17.5 (Homebrew)`
* `uname -m`: `arm64`
* macOS 14.4.1 (Sonoma)
**Ubuntu (x86\_64 production)**:
* `psql (PostgreSQL) 17.5 (Debian 17.5-1.pgdg22.04+1)`
* `uname -m`: `x86_64`
* Installed via postgresql.org APT repo
---
### Questions
* Could this indicate a regression in PostgreSQL 17’s binary `COPY TO
STDOUT` handling?
* Has anyone else seen this behavior in 17.5?
* Is there a workaround short of downgrading or switching to CSV?
I’m happy to provide test environments, dumps, or help isolate the patch if
needed.
Thanks,
Robb Shecter
[robb@public.law](mailto:robb@public.law)
