Re: Emitting JSON to file using COPY TO

On 12/6/23 18:38, David G. Johnston wrote:
> On Wed, Dec 6, 2023 at 4:28 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> 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.
> 
> Also, it seems like this answer would be easier to make if we implement 
> COPY FROM now since how is the server supposed to deal with decomposing 
> this data into tables without accurate type information?  I don't see 
> implementing only half of the feature being a good idea.  I've had much 
> more desire for FROM compared to TO personally.

Several people have weighed in on the side of getting COPY TO done by 
itself first. Given how long this discussion has already become for a 
relatively small and simple feature, I am a big fan of not expanding the 
scope now.

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