Re: dblink bulk operations

On Thu, Aug 6, 2009 at 11:11 AM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>
> Last night I needed to move a bunch of data from an OLTP database to an
> archive database, and used dblink with a bunch of insert statements. Since I
> was moving about 4m records this was distressingly but not surprisingly
> slow. It set me wondering why we don't build more support for libpq
> operations into dblink, like transactions and prepared queries, and maybe
> COPY too. It would be nice to be able to do something like:
>
>   select dblink_connect('dbh','dbname=foo');
>   select dblink_begin('dbh');
>   select dblink_prepare('dbh','sth','insert into bar values ($1,$2,$3)');
>   select dblink_exec_prepared('dbh','sth',row(a,b,c)) from bar; -- can
>   we do this?
>   select dblink_commit('dbh');
>   select dblink_disconnect('dbh');

thinking about this some more, you can get pretty close with vanilla
dblink with something like (i didn't test):

select dblink_exec('dbh', 'prepare xyz as insert into foo select ($1::foo).*');
select dblink_exec('dbh', 'execute xyz(' || my_foo::text || ')');

This maybe defeats a little bit of what you are trying to achieve
(especially performance), but is much easier to craft for basically
any table as long as the fields match. The above runs into problems
with quoting (composite with bytea in it), but works ok most of the
time.

If you want faster/better, dblink need to be factored to parametrize
queries and, if possible, use binary.

merlin