Horton

Re: Make COPY format extendable: Extract COPY TO format implementations

Masahiko Sawada <sawada.mshk@gmail.com>

View thread View on postgr.es
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Sutou Kouhei <kou@clear-code.com>, zhjwpku@gmail.com, pgsql-hackers@postgresql.org
Date: 2025-02-05T06:20:51Z
Lists: pgsql-hackers 
On Tue, Feb 4, 2025 at 9:10 PM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Sat, Feb 01, 2025 at 07:12:01PM +0900, Sutou Kouhei wrote:
> > For the propose, copyapi.h should not include
> > copy{to,from}_internal.h. If we do it, copyto.c includes
> > CopyFromState and copyfrom*.c include CopyToState.
> >
> > What do you think about the following change? Note that
> > extensions must include copy{to,from}_internal.h explicitly
> > in addition to copyapi.h.
>
> I was just looking at bit at this series of patch labelled with v31,
> to see what is happening here.
>
> In 0001, we have that:
>
> +       /* format-specific routines */
> +       const CopyToRoutine *routine;
> [...]
> -       CopySendEndOfRow(cstate);
> +       cstate->routine->CopyToOneRow(cstate, slot);
>
> Having a callback where the copy state is processed once per row is
> neat in terms of design for the callbacks and what extensions can do,
> and this is much better than what 2889fd23be5 has attempted (later
> reverted in 1aa8324b81fa) because we don't do indirect function calls
> for each attribute.  Still, I have a question here: what happens for a
> COPY TO that involves one attribute, a short field size like an int2
> and many rows (the more rows the more pronounced the effect, of
> course)?  Could this level of indirection still be the cause of some
> regressions in a case like that?  This is the worst case I can think
> about, on top of my mind, and I am not seeing tests with few
> attributes like this one, where we would try to make this callback as
> hot as possible.  This is a performance-sensitive area.

FYI when Sutou-san last measured the performance[1], it showed a
slight speed up even with fewer columns (5 columns) in both COPY TO
and COPY FROM cases. The callback design has not changed since then.
But it would be a good idea to run the benchmark with a table having a
single small size column.

Regards,

[1] https://www.postgresql.org/message-id/20241114.161948.1677325020727842666.kou%40clear-code.com

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Commits

  1. Refactor Copy{From|To}GetRoutine() to use pass-by-reference argument.

  2. Refactor COPY FROM to use format callback functions.

  3. Refactor COPY TO to use format callback functions.

  4. Another try to fix BF failure introduced in commit ddd5f4f54a.

  5. Revert "Refactor CopyReadAttributes{CSV,Text}() to use a callback in COPY FROM"

  6. Improve COPY TO performance when server and client encodings match

  7. Simplify signature of CopyAttributeOutCSV() in copyto.c

  8. Revert "Refactor CopyAttributeOut{CSV,Text}() to use a callback in COPY TO"

  9. Refactor CopyAttributeOut{CSV,Text}() to use a callback in COPY TO

  10. Refactor CopyReadAttributes{CSV,Text}() to use a callback in COPY FROM

  11. Add progress reporting of skipped tuples during COPY FROM.

  12. pgbench: Add \syncpipeline

  13. meson: Make gzip and tar optional

  14. Export the external file reader used in COPY FROM as APIs.