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
intobar values ($1,$2,$3)'); select dblink_exec_prepared('dbh','sth',row(a,b,c)) from bar; -- can we do this?
selectdblink_commit('dbh'); select dblink_disconnect('dbh');
Does this seem worthwhile and doable, or am I smoking crack?
cheers
andrew