[PG19] COPY (query) TO ... (FORMAT json) uses the table's column names
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254074psql -h localhost -U postgresBuilt from patchset v2 (message #2), October 07, 2026 at 09:18 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254074_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254074_2 && git checkout t254074_2Patchset v2 (message #2) is on t254074_2
Hi,
I used Opus to analyze the new features in PG 19, and this is one of
the things it found. COPY (query) TO with FORMAT json can name the
JSON keys after the scanned table's columns, not the query's.
create temp table u1 (a int, b int);
create temp table u2 (b int, a int);
insert into u1 values (10, 1);
insert into u2 values (20, 2);
copy (select * from u1 union all select * from u2)
to stdout (format json);
{"a":10,"b":1}
{"b":20,"a":2}
The query's columns are (a, b), so the second row should be
{"a":20,"b":2}. row_to_json() over the same query gives that. Column
aliases are lost the same way, when the query returns all columns of
a table in order.
copy (select a as x, b as y from u1) to stdout (format json);
{"a":10,"b":1}
I expected {"x":10,"y":1}. The attached copy-json-query-columns.sql
runs both cases.
I think the reason is CopyToJsonOneRow() rebuilds the tuple with
the query's descriptor only when the slot's tdtypeid is RECORDOID.
Then the scan node that does not project returns a slot with the table's
row type, so the datum goes to composite_to_json().
0001 always forms the tuple with the query's descriptor on the query
path. That is one heap_form_tuple() per row in place of the tuple
copy the old path made. I have not measured it. Another fix is to
pass the descriptor to composite_to_json() and skip the extra tuple,
but that changes json.c and looks like too much for 19.
COPY TO json is new in 19, so I think this should be an open item.
Thanks,
Shihao
Attachments:
t254074_1v1-0002-Add-tests-for-JSON-keys-of-COPY-query-TO.patchapplication/octet-stream; name=v1-0002-Add-tests-for-JSON-keys-of-COPY-query-TO.patchDownload+16-1
v1-0001-Fix-JSON-keys-of-COPY-query-TO-with-FORMAT-json.patchapplication/octet-stream; name=v1-0001-Fix-JSON-keys-of-COPY-query-TO-with-FORMAT-json.patchDownload+4-4
On 2026-10-05 Mo 3:05 PM, shihao zhong wrote:
Hi,
I used Opus to analyze the new features in PG 19, and this is one of
the things it found. COPY (query) TO with FORMAT json can name the
JSON keys after the scanned table's columns, not the query's.create temp table u1 (a int, b int);
create temp table u2 (b int, a int);
insert into u1 values (10, 1);
insert into u2 values (20, 2);copy (select * from u1 union all select * from u2)
to stdout (format json);
{"a":10,"b":1}
{"b":20,"a":2}The query's columns are (a, b), so the second row should be
{"a":20,"b":2}. row_to_json() over the same query gives that. Column
aliases are lost the same way, when the query returns all columns of
a table in order.copy (select a as x, b as y from u1) to stdout (format json);
{"a":10,"b":1}I expected {"x":10,"y":1}. The attached copy-json-query-columns.sql
runs both cases.
Yes, definitely a bug.
I think the reason is CopyToJsonOneRow() rebuilds the tuple with
the query's descriptor only when the slot's tdtypeid is RECORDOID.Then the scan node that does not project returns a slot with the table's
row type, so the datum goes to composite_to_json().0001 always forms the tuple with the query's descriptor on the query
path. That is one heap_form_tuple() per row in place of the tuple
copy the old path made. I have not measured it. Another fix is to
pass the descriptor to composite_to_json() and skip the extra tuple,
but that changes json.c and looks like too much for 19.
Your solution is apparently correct, but when I measured it there was a
performance hit of 4% to 7% in some cases, which we really don't want if
we can avoid it. So I (and Opus) came up with the attached
patch.heap_copy_tuple_as_datum() stamps the copy with whatever
descriptor it's given, so we can keep the copy and just pass it the
query's descriptor. That is safe because a scan only skips projection
when tlist_matches_tupdesc() holds, and that excludes dropped columns
and columns with missing values, so the tuple's physical layout matches
the query's descriptor. With that change the timings are
indistinguishable from master. Virtual slots still use heap_form_tuple()
as in your patch.
Please test this out and see if you can break it again ;-)
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Hi Andrew,
Thanks for v2. I tried to break it and couldn't.
I compared COPY (query) TO STDOUT (format json) against row_to_json()
over the same query on about 37 shapes (fast defaults, dropped and
added columns, inheritance, partitions with different column order,
toasted values, joins, Sort/Limit/LockRows over a plain scan), with
parallel plans forced, on an assert-enabled build. The output was
identical every time, and the new Assert never fired.
It also fixes a case not in the report or the tests: a view with
renamed columns, or a CTE with a column alias list, selected through
an inheritance parent. Before, the parent's rows used the table's
column names and the child's rows used the query's, in the same
result.
Regards,
João Marcelo
Em seg., 5 de out. de 2026 às 18:05, Andrew Dunstan <andrew@dunslane.net>
escreveu:
Show quoted text
On 2026-10-05 Mo 3:05 PM, shihao zhong wrote:
Hi,
I used Opus to analyze the new features in PG 19, and this is one of
the things it found. COPY (query) TO with FORMAT json can name the
JSON keys after the scanned table's columns, not the query's.create temp table u1 (a int, b int);
create temp table u2 (b int, a int);
insert into u1 values (10, 1);
insert into u2 values (20, 2);copy (select * from u1 union all select * from u2)
to stdout (format json);
{"a":10,"b":1}
{"b":20,"a":2}The query's columns are (a, b), so the second row should be
{"a":20,"b":2}. row_to_json() over the same query gives that. Column
aliases are lost the same way, when the query returns all columns of
a table in order.copy (select a as x, b as y from u1) to stdout (format json);
{"a":10,"b":1}I expected {"x":10,"y":1}. The attached copy-json-query-columns.sql
runs both cases.Yes, definitely a bug.
I think the reason is CopyToJsonOneRow() rebuilds the tuple with
the query's descriptor only when the slot's tdtypeid is RECORDOID.Then the scan node that does not project returns a slot with the table's
row type, so the datum goes to composite_to_json().0001 always forms the tuple with the query's descriptor on the query
path. That is one heap_form_tuple() per row in place of the tuple
copy the old path made. I have not measured it. Another fix is to
pass the descriptor to composite_to_json() and skip the extra tuple,
but that changes json.c and looks like too much for 19.Your solution is apparently correct, but when I measured it there was a
performance hit of 4% to 7% in some cases, which we really don't want if
we can avoid it. So I (and Opus) came up with the attached
patch.heap_copy_tuple_as_datum() stamps the copy with whatever
descriptor it's given, so we can keep the copy and just pass it the
query's descriptor. That is safe because a scan only skips projection
when tlist_matches_tupdesc() holds, and that excludes dropped columns
and columns with missing values, so the tuple's physical layout matches
the query's descriptor. With that change the timings are
indistinguishable from master. Virtual slots still use heap_form_tuple()
as in your patch.Please test this out and see if you can break it again ;-)
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Hi Andrew,
Please test this out and see if you can break it again ;-)
v2 looks good to me, and I could not break it with my previous
tests.
so we can keep the copy and just pass it the
query's descriptor.
I think that makes sense, haven't thought about performance impact
at v1.
Thanks,
Shihao
On 2026-10-05 Mo 9:04 PM, shihao zhong wrote:
Hi Andrew,
Please test this out and see if you can break it again ;-)
v2 looks good to me, and I could not break it with my previous
tests.so we can keep the copy and just pass it the
query's descriptor.I think that makes sense, haven't thought about performance impact
at v1.
OK thanks to both of you. I will wait a day or so in case anyone else
wants to chime in, and commit late Thursday or Friday morning.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
On Tue, Oct 6, 2026 at 5:39 PM Andrew Dunstan <andrew@dunslane.net> wrote:
OK thanks to both of you. I will wait a day or so in case anyone else wants to chime in, and commit late Thursday or Friday morning.
While it looks like this is on its way to being resolved, I just
wanted to chime in with RMT hat to see if it should be tracked with an
open item.
- Melanie
On 2026-10-07 We 11:42 AM, Melanie Plageman wrote:
On Tue, Oct 6, 2026 at 5:39 PM Andrew Dunstan <andrew@dunslane.net> wrote:
OK thanks to both of you. I will wait a day or so in case anyone else wants to chime in, and commit late Thursday or Friday morning.
While it looks like this is on its way to being resolved, I just
wanted to chime in with RMT hat to see if it should be tracked with an
open item.
Looks like someone has added one. I will add update it when I commit the
fix.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com