Thread

Re: postgres_fdw: Use COPY to speed up batch inserts

Matheus Alcantara <matheusssilv97@gmail.com> — 2026-05-27T23:22:47Z

On Wed Apr 1, 2026 at 12:50 PM -03, Matheus Alcantara wrote:
>> -- on local server
>> copy t(a, d) from stdin;
>> Enter data to be copied followed by a newline.
>> End with a backslash and a period on a line by itself, or an EOF signal.
>>>> 1    hello\nworld
>>>> \.
>> ERROR:  invalid input syntax for type integer: "world"
>> CONTEXT:  COPY t, line 2, column a: "world"
>> remote SQL command: COPY public.t(a, d) FROM STDIN (FORMAT TEXT)
>>
>
> I think that we need something like CopyAttributeOutText() here. 
>
> To fix this I've added appendStringInfoText() which is a similar version
> of CopyAttributeOutText() that works with a StringInfo. I did not find
> any function that I could reuse here, if such function exists please let
> me know.
>
> I'm wondering if we should have this similar function or try to combine
> both to avoid duplicated logic, although it looks complicated to me at
> first look to combine these both usages.
>

I've spent some time hacking into this and actually I don't think that
it's too complicated. Please see the attached patchset.

0001: Extract CopyAttributeOutText escape logic into a reusable function
that can be used by postgres_fdw and by COPY TO routine.

0002: Is the main feature implementation using the TEXT format reusing
the refactor from 0001 avoiding all the duplicated code introduced on
this previous patch version. I've also removed the
resultRelInfo->ri_WithCheckOptions == NIL check that I've introduced
again by mistake since it was already commented at [1] that it should be
removed.

[1] https://www.postgresql.org/message-id/72ec1708-0c02-4ae9-b4f5-ee2bac5fd2f3%40gmail.com

--
Matheus Alcantara
EDB: https://www.enterprisedb.com