Re: [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION

Greg Smith wrote:
> Robert Haas wrote:
>> I'm fuzzy on what problem this is attempting to solve... as mentioned
>> in the above guidelines, it's usually good to start with some design
>> discussions before writing/submitting code.
> This has been through some heavy design discussions with a few PG
> hackers you know and some you don't, they just couldn't release the
> result until now. As for what it's good for, if you look at what you
> can do now with dblink, you can easily move rows between nodes using
> dblink_build_sql_insert. This is perfectly fine for small bits of
> work, but the performance isn't nearly good enough to do serious
> replication with it. The upper level patch here allows using COPY as
> the mechanism to move things between them, which is much faster for
> some use cases (which includes most of the really useful ones). It
> dramatically increases the scale of what you can move around using
> dblink as the replication transport.

I recently found myself trying to push data through dblink() and ended
up writing code to make a call to the target to call a function which
called back to the source to select the data and insert it. The speedup
was massive, so I'll be interested to dig into the details here.

> The lower level patch is needed to build that layer, which is an
> immediate proof of its utility. In addition, adding a user-defined
> function as a COPY target opens up all sorts of possibilities for
> things like efficient ETL implementation. And if this approach is
> accepted as a reasonable one, as Dan suggested a next step might even
> be to similarly allow passing COPY FROM through a UDF, which has the
> potential to provide a new efficient implementation path for some of
> the custom input filter requests that pop up here periodically.

I'm also interested in COPY returning rows as text[], which was
discussed recently. Does this help move us towards that?

cheers

andrew