Horton

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

Sutou Kouhei <kou@clear-code.com>

View thread View on postgr.es
From: Sutou Kouhei <kou@clear-code.com>
To: david.g.johnston@gmail.com
Cc: sawada.mshk@gmail.com, tgl@sss.pgh.pa.us, zhjwpku@gmail.com, michael@paquier.xyz, pgsql-hackers@postgresql.org
Date: 2025-05-09T09:41:37Z
Lists: pgsql-hackers 
Hi,

In <CAKFQuwaRDXANaL+QcT6LZRAem4rwkSwv9v+viv_mcR+Rex3quA@mail.gmail.com>
  "Re: Make COPY format extendable: Extract COPY TO format implementations" on Sat, 3 May 2025 22:27:36 -0700,
  "David G. Johnston" <david.g.johnston@gmail.com> wrote:

> In any case, I’m doubtful either of us can make a convincing enough
> argument to sway the other fully.  Both options are plausible, IMO.  Others
> need to chime in.

I may misunderstand but here is the current summary, right?

Proposed approaches to register custom COPY formats:
a. Create a function that has the same name of custom COPY
   format
b. Call a register function from _PG_init()

FYI: I proposed c. approach that uses a. but it always
requires schema name for format name in other e-mail.


Users can register the same format name:
a. Yes
   * Users can distinct the same format name by schema name
   * If format name doesn't have schema name, the used
     format depends on search_path
     * Pros:
       * Using schema for it is consistent with other
         PostgreSQL mechanisms
       * Custom format never conflict with built-in
         format. For example, an extension register "xml" and
         PostgreSQL adds "xml" later, they are never
         conflicted because PostgreSQL's "xml" is registered
         to pg_catalog.
     * Cons: Different format may be used with the same
       input. For example, "jsonlines" may choose
       "jsonlines" implemented by extension X or implemented
       by extension Y when search_path is different.
b. No
   * Users can use "${schema}.${name}" for format name
     that mimics PostgreSQL's builtin schema (but it's just
     a string)


Built-in formats (text/csv/binary) should be able to
overwritten by extensions:
a. (The current patch is no but David's answer is) Yes
   * Pros: Users can use drop-in replacement faster
     implementation without changing input
   * Cons: Users may overwrite them accidentally.
     It may break pg_dump result.
     (This is called as "backward incompatibility.")
b. No


Are there any missing or wrong items? If we can summarize
the current discussion here correctly, others will be able
to chime in this discussion. (At least I can do it.)


Thanks,
-- 
kou

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.