Thread: BUG #18926: PostgreSQL 17.5 produces malformed binary COPY output (`GPOCYP` instead of `PGCOPY`) across platform
BUG #18926: PostgreSQL 17.5 produces malformed binary COPY output (`GPOCYP` instead of `PGCOPY`) across platform
From
PG Bug reporting form
Date:
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)