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: Sutou Kouhei <kou@clear-code.com>
Cc: david.g.johnston@gmail.com, tgl@sss.pgh.pa.us, zhjwpku@gmail.com, michael@paquier.xyz, pgsql-hackers@postgresql.org
Date: 2025-05-03T06:37:46Z
Lists: pgsql-hackers 
On Fri, May 2, 2025 at 11:20 PM Sutou Kouhei <kou@clear-code.com> wrote:
>
> Hi,
>
> In <CAD21AoDnY2fhC7tp7jpn24AuwkeW-0YjFEtZbEfPwg8YcH6bAw@mail.gmail.com>
>   "Re: Make COPY format extendable: Extract COPY TO format implementations" on Fri, 2 May 2025 23:02:25 -0700,
>   Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> > The progress view can know only numbers. We need to extend the
> > progress view infrastructure so that we can pass other data types.
>
> Sorry. Could you tell me what APIs referred here?
> pgstat_progress_*() functions in
> src/include/utils/backend_progress.h?

The progress information is stored in PgBackendStatus defined in
backend_status.h:

    /*
     * Command progress reporting.  Any command which wishes can advertise
     * that it is running by setting st_progress_command,
     * st_progress_command_target, and st_progress_param[].
     * st_progress_command_target should be the OID of the relation which the
     * command targets (we assume there's just one, as this is meant for
     * utility commands), but the meaning of each element in the
     * st_progress_param array is command-specific.
     */
    ProgressCommandType st_progress_command;
    Oid         st_progress_command_target;
    int64       st_progress_param[PGSTAT_NUM_PROGRESS_PARAM];

Then the progress view maps the numbers to the corresponding strings:

CREATE VIEW pg_stat_progress_copy AS
    SELECT
        S.pid AS pid, S.datid AS datid, D.datname AS datname,
        S.relid AS relid,
        CASE S.param5 WHEN 1 THEN 'COPY FROM'
                      WHEN 2 THEN 'COPY TO'
                      END AS command,
        CASE S.param6 WHEN 1 THEN 'FILE'
                      WHEN 2 THEN 'PROGRAM'
                      WHEN 3 THEN 'PIPE'
                      WHEN 4 THEN 'CALLBACK'
                      END AS "type",
        S.param1 AS bytes_processed,
        S.param2 AS bytes_total,
        S.param3 AS tuples_processed,
        S.param4 AS tuples_excluded,
        S.param7 AS tuples_skipped
    FROM pg_stat_get_progress_info('COPY') AS S
        LEFT JOIN pg_database D ON S.datid = D.oid;

So the idea is that the backend process sets the format ID somewhere
in st_progress_param, and then the progress view calls a SQL function,
say pg_stat_get_copy_format_name(), with the format ID that returns
the corresponding format name.

Regards,

-- 
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.