dblink bulk operations

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

Does this seem worthwhile and doable, or am I smoking crack?

cheers

andrew

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

On Thu, Aug 06, 2009 at 11:11:58AM -0400, Andrew Dunstan 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');
>
>
> Does this seem worthwhile and doable, or am I smoking crack?

For what it's worth, DBI-Link provides a lot of this.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:
>
> For what it's worth, DBI-Link provides a lot of this.
>
>
>

Indeed, but that assumes that perl+DBI+DBD::Pg is available, which is by
no means always the case. If we're going to have a dblink module ISTM it
should be capable of reasonable bulk operations.

cheers

andrew

On Thu, Aug 06, 2009 at 12:28:15PM -0400, Andrew Dunstan wrote:
> David Fetter wrote:
>>
>> For what it's worth, DBI-Link provides a lot of this.
>
> Indeed, but that assumes that perl+DBI+DBD::Pg is available, which
> is by no means always the case. If we're going to have a dblink
> module ISTM it should be capable of reasonable bulk operations.

I didn't mean to suggest that you should use DBI-Link, just that it's
a requirement that's come up in very similar contexts to that of
dblink.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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