From: Tom Lane <tgl@sss.pgh.pa.us>
To: Heikki Linnakangas <hlinnaka@iki.fi>
cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: psql \copy from sends a lot of packets
In-reply-to: <40b2cec0-d0fb-3191-2ae1-9a3fe16a7e48@iki.fi>
References: <40b2cec0-d0fb-3191-2ae1-9a3fe16a7e48@iki.fi>
Comments: In-reply-to Heikki Linnakangas <hlinnaka@iki.fi>
	message dated "Sun, 07 Feb 2021 00:13:38 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <334618.1612650213.1@sss.pgh.pa.us>
Date: Sat, 06 Feb 2021 17:23:33 -0500
Message-ID: <334619.1612650213@sss.pgh.pa.us>
Precedence: bulk

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> I just noticed that if you load a file using psql:
> it sends every line as a separate FE/BE protocol CopyData packet.
> ...
> I'll add this to the next commitfest. There's similar inefficiency in 
> the server side in COPY TO, but I'll leave that for another patch.

The FE/BE protocol documentation is pretty explicit about this:

    Copy-in mode (data transfer to the server) is initiated when the
    backend executes a COPY FROM STDIN SQL statement. The backend sends a
    CopyInResponse message to the frontend. The frontend should then send
    zero or more CopyData messages, forming a stream of input data. (The
    message boundaries are not required to have anything to do with row
    boundaries, although that is often a reasonable choice.)
    ...
    Copy-out mode (data transfer from the server) is initiated when the
    backend executes a COPY TO STDOUT SQL statement. The backend sends a
    CopyOutResponse message to the frontend, followed by zero or more
    CopyData messages (always one per row), followed by CopyDone.

So while changing psql isn't so much a problem, changing the server
is a wire protocol break.  Maybe we should do it anyway, but I'm
not sure.

			regards, tom lane