so - I can create a dblink connection - eg
select * from dblink( 'dbname=whatever host=the_host user=the_user password=my_password', 'select x, y, z from blah')
works fine. I can even make what appears to be a persistent connection
select * from dblink_connect( 'dev', 'dbname=whatever host=the_host user=the_user password=my_password');
select * from dblink( 'dev', 'select x, y, z from blah' );
works fine. For a while. And then after a while - if I try to use dev again - it starts telling me "no open connection". But if I try to run the connect command again, it tells me a connection with that name already exists.
So how do I establish a named connection that I, and others, can just use directly forever afterwards without having to do any sort of connect/disconnect?
You can give dblink() the name of a foreign server, rather than the name of a connection.
create server dev foreign data wrapper dblink_fdw options (host 'thehost', dbname 'whatever');
create user mapping for public server dev options (user 'the_user', password 'my_password');
Then run your dblink query just as you currently are, using 'dev' as the name.
Note that this will increase the number of connections done, it is just that the system manages them so that you don't need to. So it is good for convenience, but not for performance.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments