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');

you can always exec a sql 'begin'.

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

The answer to this I think is yes, but not quite that way. Much
better I think is to use 8.4 variable argument functions, use
parametrized features off libpq always, and use the binary protocol
when possible. This does end up running much faster, and easier to
use...(we've done exactly that for our in house stuff). IIRC you can
parameterize 'execute', so the above should work for prepared queries
as well.

If we get the ability to set specific OIDs for types, I can remove
some of the hacks we have to send text for composites and arrays of
composites.

select * from pqlink_exec(connstr, 'select $1 + $2', 3, 4) as R(v int);
v
---
7
(1 row)

merlin