Message-ID: <64c24dab-cf4b-4c33-8667-540136588e9e@joeconway.com>
Date: Wed, 6 Dec 2023 18:45:52 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Emitting JSON to file using COPY TO
Content-Language: en-US
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Daniel Verite <daniel@manitou-mail.org>,
 Andrew Dunstan <andrew@dunslane.net>, Davin Shearer <davin@apache.org>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <b3a73eca-ec3b-4b64-b6c2-82418bb16701@manitou-mail.org>
 <b7d8c88b-d175-44ca-a323-ec2f5c3cd37d@joeconway.com>
 <8620df11-96e4-4ca3-8f3c-33a479260961@joeconway.com>
 <CAKFQuwYS4HwypV3iKqx+GdsMU+-k-VTWt4bu6vc5nYqcAMgkcA@mail.gmail.com>
From: Joe Conway <mail@joeconway.com>
Autocrypt: addr=mail@joeconway.com; keydata=
 xsFNBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l
 VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS
 sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14
 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE
 qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw
 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ
 ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo
 DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA
 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm
 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABzSRKb3NlcGggRSBD
 b253YXkgPG1haWxAam9lY29ud2F5LmNvbT7CwXoEEwEIACQCGwMCHgECF4AFCwkIBwMFFQoJ
 CAsFFgIDAQAFAlWTVvUCGQEACgkQMyt+aLaZQ0oPCQ/9HyRewMyvAIJRmoXoLAr8AoFLId6R
 qBJnNX0Lll0RLZui65aQ0+exwX7aH7TxWR16B2gWX3OmLfGT8XITOoG+zt9zsEpLvNkHchkF
 T/jyAcbuRj5WX9hamZgMbjXAJeCdlhW+fRA9Upb0w4dgBjqK5OgsqMikASL7t2vogHl9H08j
 vSoQLW+8wTnSBXBeBTBwB7xLIin5WVivzFHUCrnD2UsjeBIW3fmGdpTAjSxRzG+UPYVwXQ8F
 FLt7DpEytvLWapmZWMRdj0WZ/Q3SOO/Ed0yFqbzuwKaWcFrQBNeS2Sig+FefBNS98f9Hx7ku
 H3DW34qX/zSSdDh0jLs7X3PkIgF6BZR2TxaCwHPP9ERDiDaUInC9U7We1iZE1DjW8rLMEVJB
 hY0ClrrF67pnUKTbcU+uajpPn+2Jl74T0Set/XxpHZ4cezcJuqg31R8vHZgd5cf1WKP0D0pc
 qiuS02BBFkNCs1jQ+raTWcDuE6F1mUO2nvjUBN9r4y5DUbCNSqLKeAe/aA6JaSDkBpoXKdNS
 +c4rbzbktWkfUW8EhVlCGzNpy4ezEoVsqV2Ex7fNoxsE2vnSylLT9hycAmYf8ryMvniRZqnD
 T4JgLenIcQlkhB896T7wApOXfD8OJj1/XFxAfPi6vdlsr81uoxuB4euLp8IyduwLORRUogO9
 zmAXG5jOwU0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMva
 v/KENd3oAtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFV
 dWnlw7dWVTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5Vu
 gWNlPZcV5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv
 5ZhPRpO79nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc
 2OrWo7YMgMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQ
 bauDQw6VQ38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT
 0S5yOgyX0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4e
 NrxU0u2xAy5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA
 81uMj49j0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAHCwV8EGAEI
 AAkFAkpXMicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4
 Fia379JmHwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBG
 HwLEOTRcfYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZ
 E0C1xIE43nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6Xlnd
 FrDlY22tRmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl
 0ZJP3sHJ2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcH
 YebVJZRbUUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs
 5egE3szPcIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1
 c9JbPJ1Pn8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+
 1iJNEnp9iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q=
In-Reply-To: 
 <CAKFQuwYS4HwypV3iKqx+GdsMU+-k-VTWt4bu6vc5nYqcAMgkcA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/64c24dab-cf4b-4c33-8667-540136588e9e%40joeconway.com>
Precedence: bulk

On 12/6/23 18:28, David G. Johnston wrote:
> On Wed, Dec 6, 2023 at 4:09 PM Joe Conway <mail@joeconway.com 
> <mailto:mail@joeconway.com>> wrote:
> 
>     On 12/6/23 14:47, Joe Conway wrote:
>      > On 12/6/23 13:59, Daniel Verite wrote:
>      >>      Andrew Dunstan wrote:
>      >>
>      >>> IMNSHO, we should produce either a single JSON
>      >>> document (the ARRAY case) or a series of JSON documents, one
>     per row
>      >>> (the LINES case).
>      >>
>      >> "COPY Operations" in the doc says:
>      >>
>      >> " The backend sends a CopyOutResponse message to the frontend,
>     followed
>      >>     by zero or more CopyData messages (always one per row),
>     followed by
>      >>     CopyDone".
>      >>
>      >> In the ARRAY case, the first messages with the copyjsontest
>      >> regression test look like this (tshark output):
>      >>
>      >> PostgreSQL
>      >>      Type: CopyOut response
>      >>      Length: 13
>      >>      Format: Text (0)
>      >>      Columns: 3
>      >>      Format: Text (0)
> 
>      > Anything receiving this and looking for a json array should know
>     how to
>      > assemble the data correctly despite the extra CopyData messages.
> 
>     Hmm, maybe the real problem here is that Columns do not equal "3" for
>     the json mode case -- that should really say "1" I think, because the
>     row is not represented as 3 columns but rather 1 json object.
> 
>     Does that sound correct?
> 
>     Assuming yes, there is still maybe an issue that there are two more
>     "rows" that actual output rows (the "[" and the "]"), but maybe those
>     are less likely to cause some hazard?
> 
> 
> What is the limitation, if any, of introducing new type codes for 
> these.  n = 2..N for the different variants?  Or even -1 for "raw 
> text"?  And document that columns and structural rows need to be 
> determined out-of-band.  Continuing to use 1 (text) for this non-csv 
> data seems like a hack even if we can technically make it function.  The 
> semantics, especially for the array case, are completely discarded or wrong.

I am not following you here. SendCopyBegin looks like this currently:

8<--------------------------------
SendCopyBegin(CopyToState cstate)
{
	StringInfoData buf;
	int			natts = list_length(cstate->attnumlist);
	int16		format = (cstate->opts.binary ? 1 : 0);
	int			i;

	pq_beginmessage(&buf, PqMsg_CopyOutResponse);
	pq_sendbyte(&buf, format);	/* overall format */
	pq_sendint16(&buf, natts);
	for (i = 0; i < natts; i++)
		pq_sendint16(&buf, format); /* per-column formats */
	pq_endmessage(&buf);
	cstate->copy_dest = COPY_FRONTEND;
}
8<--------------------------------

The "1" is saying are we binary mode or not. JSON mode will never be 
sending in binary in the current implementation at least. And it always 
aggregates all the columns as one json object. So the correct answer is 
(I think):
8<--------------------------------
*************** SendCopyBegin(CopyToState cstate)
*** 146,154 ****

   	pq_beginmessage(&buf, PqMsg_CopyOutResponse);
   	pq_sendbyte(&buf, format);	/* overall format */
! 	pq_sendint16(&buf, natts);
! 	for (i = 0; i < natts; i++)
! 		pq_sendint16(&buf, format); /* per-column formats */
   	pq_endmessage(&buf);
   	cstate->copy_dest = COPY_FRONTEND;
   }
--- 150,169 ----

   	pq_beginmessage(&buf, PqMsg_CopyOutResponse);
   	pq_sendbyte(&buf, format);	/* overall format */
! 	if (!cstate->opts.json_mode)
! 	{
! 		pq_sendint16(&buf, natts);
! 		for (i = 0; i < natts; i++)
! 			pq_sendint16(&buf, format); /* per-column formats */
! 	}
! 	else
! 	{
! 		/*
! 		 * JSON mode is always one non-binary column
! 		 */
! 		pq_sendint16(&buf, 1);
! 		pq_sendint16(&buf, 0);
! 	}
   	pq_endmessage(&buf);
   	cstate->copy_dest = COPY_FRONTEND;
   }
8<--------------------------------

That still leaves the need to fix the documentation:

" The backend sends a CopyOutResponse message to the frontend, followed
    by zero or more CopyData messages (always one per row), followed by
    CopyDone"

probably "always one per row" would be changed to note that json array 
format outputs two extra rows for the start/end bracket.

In fact, as written the patch does this:
8<--------------------------------
COPY (SELECT x.i, 'val' || x.i as v FROM
       generate_series(1, 3) x(i) WHERE false)
TO STDOUT WITH (FORMAT JSON, FORCE_ARRAY);
[
]
8<--------------------------------

Not sure if that is a problem or not.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com