public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrew Dunstan <[email protected]>
To: jian he <[email protected]>
Cc: Joe Conway <[email protected]>
Cc: Junwang Zhao <[email protected]>
Cc: Florents Tselai <[email protected]>
Cc: Andrey M. Borodin <[email protected]>
Cc: Dean Rasheed <[email protected]>
Cc: Daniel Verite <[email protected]>
Cc: Davin Shearer <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Emitting JSON to file using COPY TO
Date: Mon, 9 Mar 2026 08:23:09 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxGm3OhbG3=bj4nLxMzq92A84v6Q1dG+hR_ZFrfLg1B99w@mail.gmail.com>
References: <[email protected]>
<CACJufxG0=CoG64Ng7DfMP4zy0MORhTW7rRioQnNVCw-6GxRaXg@mail.gmail.com>
<CACJufxGq6aJszBhfrUQKHeKmp0r+ka7=SwHSY7Qs_6LQMwiEbw@mail.gmail.com>
<CA+v5N43-F9-Wiktg_-+aqKJz+YiCNJMAh5ootDeTKkOJ=kiaHA@mail.gmail.com>
<CACJufxEjZwrocCpt29xtmJTwhWZUu1Nt0GfHFarNGXUS5AAecw@mail.gmail.com>
<CAEG8a3LB7q1eQ0AzFTEBDFDxs3kJ=5iJA+HTmiYGya6Wb5jsRA@mail.gmail.com>
<CACJufxFLpMsbW41T65xJsw925MSE1bvOr6X+h_7sw8_qmDpTpA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CACJufxELGPA86veVNNBoqUDLwwQbQKdiYLKtwLb7DoSgV6f8sw@mail.gmail.com>
<[email protected]>
<CACJufxFFZqxC3p4WjpTEi4riaJm=pADX+py0yQ0=RWTn5cqK3Q@mail.gmail.com>
<[email protected]>
<CACJufxGm3OhbG3=bj4nLxMzq92A84v6Q1dG+hR_ZFrfLg1B99w@mail.gmail.com>
On 2026-03-08 Su 11:48 PM, jian he wrote:
> On Mon, Mar 9, 2026 at 3:44 AM Andrew Dunstan<[email protected]> wrote:
>> Hmm. But should we be scribbling on slot->tts_tupleDescriptor like that?
>> How about something like this?:
>>
>> - * Full table or query without column list. Ensure the slot uses
>> - * cstate->tupDesc so that the datum is stamped with the right type;
>> - * for queries output type is RECORDOID this must be the blessed
>> - * descriptor so that composite_to_json can look it up via
>> - * lookup_rowtype_tupdesc.
>> + * Full table or query without column list. For queries, the slot's
>> + * TupleDesc may carry RECORDOID, which is not registered in the
>> type
>> + * cache and would cause composite_to_json's lookup_rowtype_tupdesc
>> + * call to fail. Build a HeapTuple stamped with the blessed
>> + * descriptor so the type can be looked up correctly.
>> */
>> if (!cstate->rel && slot->tts_tupleDescriptor->tdtypeid ==
>> RECORDOID)
>> - slot->tts_tupleDescriptor = cstate->queryDesc->tupDesc;
>> + {
>> + HeapTuple tup;
>>
>> - rowdata = ExecFetchSlotHeapTupleDatum(slot);
>> + tup = heap_form_tuple(cstate->tupDesc,
>> + slot->tts_values,
>> + slot->tts_isnull);
>> + rowdata = HeapTupleGetDatum(tup);
>> + }
>> + else
>> + {
>> + rowdata = ExecFetchSlotHeapTupleDatum(slot);
>> + }
>>
> This is better. I've tried to get rid of json_projvalues and json_projnulls.
> Just using heap_form_tuple, but it won't work.
>
> I incorporated the v28-0004 COPY column list into v9-0002.
> With this patch set, we added four fields to the struct CopyToStateData.
>
> + StringInfo json_buf; /* reusable buffer for JSON output,
> + * initialized in BeginCopyTo */
> + TupleDesc tupDesc; /* Descriptor for JSON output; for a column
> + * list this is a projected descriptor */
> + Datum *json_projvalues; /* pre-allocated projection values, or
> + * NULL */
> + bool *json_projnulls; /* pre-allocated projection nulls, or NULL */
>
> Using the script in
> https://www.postgresql.org/message-id/CACJufxFFZqxC3p4WjpTEi4riaJm%3DpADX%2Bpy0yQ0%3DRWTn5cqK3Q%40ma...
> I tested it again on macOS and Linux, and there are no regressions for
> COPY TO with the TEXT and CSV formats.
>
OK, I think we're really close now. Here is a tiny fixup patch that
fixes an error message and a comment, and adds a missing test case.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 84254d46a67..c11c2eb48fa 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -972,8 +972,8 @@ ProcessCopyOptions(ParseState *pstate,
/* Check json format */
if (opts_out->format == COPY_FORMAT_JSON && is_from)
ereport(ERROR,
- errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("COPY %s mode cannot be used with %s", "JSON", "COPY FROM"));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COPY %s is not supported for %s", "FORMAT JSON", "COPY FROM"));
if (opts_out->format != COPY_FORMAT_JSON && opts_out->force_array)
ereport(ERROR,
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 85ca7c947f3..de280ad8612 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -1061,8 +1061,8 @@ BeginCopyTo(ParseState *pstate,
cstate->tupDesc = BlessTupleDesc(resultDesc);
/*
- * * Pre-allocate arrays for projecting selected column values
- * into sequential positions matching the custom TupleDesc.
+ * Pre-allocate arrays for projecting selected column values into
+ * sequential positions matching the custom TupleDesc.
*/
cstate->json_projvalues = palloc_array(Datum, natts);
cstate->json_projnulls = palloc_array(bool, natts);
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index bcf45845b61..3e566b96183 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -136,7 +136,7 @@ LINE 1: copy copytest to stdout (format json, on_error ignore);
copy copytest to stdout (format json, reject_limit 1);
ERROR: COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
copy copytest from stdin(format json);
-ERROR: COPY JSON mode cannot be used with COPY FROM
+ERROR: COPY FORMAT JSON is not supported for COPY FROM
-- all of the above should yield error
-- column list with json format
copy copytest (style, test, filler) to stdout (format json);
@@ -167,6 +167,10 @@ copy copytest to stdout (format json, force_array false);
{"style":"Unix","test":"abc\ndef","filler":2}
{"style":"Mac","test":"abc\rdef","filler":3}
{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
+-- empty result set with force_array
+copy (select 1 where false) to stdout (format json, force_array true);
+[
+]
-- column list with diverse data types
create temp table copyjsontest_types (
id int,
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index bc12ac879ef..520934271ec 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -119,6 +119,9 @@ copy copytest to stdout (format json, force_array);
copy copytest(style, test) to stdout (format json, force_array true);
copy copytest to stdout (format json, force_array false);
+-- empty result set with force_array
+copy (select 1 where false) to stdout (format json, force_array true);
+
-- column list with diverse data types
create temp table copyjsontest_types (
id int,
Attachments:
[text/plain] copy-json-fixes.patch.nocfbot (3.0K, 3-copy-json-fixes.patch.nocfbot)
download | inline diff:
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 84254d46a67..c11c2eb48fa 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -972,8 +972,8 @@ ProcessCopyOptions(ParseState *pstate,
/* Check json format */
if (opts_out->format == COPY_FORMAT_JSON && is_from)
ereport(ERROR,
- errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("COPY %s mode cannot be used with %s", "JSON", "COPY FROM"));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COPY %s is not supported for %s", "FORMAT JSON", "COPY FROM"));
if (opts_out->format != COPY_FORMAT_JSON && opts_out->force_array)
ereport(ERROR,
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 85ca7c947f3..de280ad8612 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -1061,8 +1061,8 @@ BeginCopyTo(ParseState *pstate,
cstate->tupDesc = BlessTupleDesc(resultDesc);
/*
- * * Pre-allocate arrays for projecting selected column values
- * into sequential positions matching the custom TupleDesc.
+ * Pre-allocate arrays for projecting selected column values into
+ * sequential positions matching the custom TupleDesc.
*/
cstate->json_projvalues = palloc_array(Datum, natts);
cstate->json_projnulls = palloc_array(bool, natts);
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index bcf45845b61..3e566b96183 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -136,7 +136,7 @@ LINE 1: copy copytest to stdout (format json, on_error ignore);
copy copytest to stdout (format json, reject_limit 1);
ERROR: COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
copy copytest from stdin(format json);
-ERROR: COPY JSON mode cannot be used with COPY FROM
+ERROR: COPY FORMAT JSON is not supported for COPY FROM
-- all of the above should yield error
-- column list with json format
copy copytest (style, test, filler) to stdout (format json);
@@ -167,6 +167,10 @@ copy copytest to stdout (format json, force_array false);
{"style":"Unix","test":"abc\ndef","filler":2}
{"style":"Mac","test":"abc\rdef","filler":3}
{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
+-- empty result set with force_array
+copy (select 1 where false) to stdout (format json, force_array true);
+[
+]
-- column list with diverse data types
create temp table copyjsontest_types (
id int,
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index bc12ac879ef..520934271ec 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -119,6 +119,9 @@ copy copytest to stdout (format json, force_array);
copy copytest(style, test) to stdout (format json, force_array true);
copy copytest to stdout (format json, force_array false);
+-- empty result set with force_array
+copy (select 1 where false) to stdout (format json, force_array true);
+
-- column list with diverse data types
create temp table copyjsontest_types (
id int,
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Emitting JSON to file using COPY TO
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox