Re: dblink bulk operations

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