Add dblink function to check if a named connection exists

I have locked down access to all dblink_* functions, so that only
certain privileged users have access to them, and instead provide a set
of SRF functions defined as security definer functions, where I connect
to the remote server, fetch some data, disconnect from remote server,
and return the data.
One obvious disadvantage of this approach, is that I need to connect and
disconnect in every function. A possible solution to this, would be
having a function f.ex dblink_exists('connection_name') that returns
true/false depending on whether the connection already exists. This
way, I could just check if a named connection exists, and establish a
connection if not, and wait until the end of the session to disconnect
all established connections.

I've attached a patch with a suggested implementation of such a function.

--
Tommy Gildseth

Tommy Gildseth <tommy(dot)gildseth(at)usit(dot)uio(dot)no> writes:
> One obvious disadvantage of this approach, is that I need to connect and
> disconnect in every function. A possible solution to this, would be
> having a function f.ex dblink_exists('connection_name') that returns
> true/false depending on whether the connection already exists.

Can't you do this already?

SELECT 'myconn' = ANY (dblink_get_connections());

A dedicated function might be a tad faster, but it probably isn't going
to matter compared to the overhead of sending a remote query.

regards, tom lane

Tom Lane wrote:
> Tommy Gildseth <tommy(dot)gildseth(at)usit(dot)uio(dot)no> writes:
>> One obvious disadvantage of this approach, is that I need to connect and
>> disconnect in every function. A possible solution to this, would be
>> having a function f.ex dblink_exists('connection_name') that returns
>> true/false depending on whether the connection already exists.
>
> Can't you do this already?
>
> SELECT 'myconn' = ANY (dblink_get_connections());
>
> A dedicated function might be a tad faster, but it probably isn't going
> to matter compared to the overhead of sending a remote query.

I agree. The above is about as simple as
SELECT dblink_exists('dtest1');
and probably not measurably slower. If you still think a dedicated
function is needed, please send the output of some performance testing
to justify it.

If you really want the notational simplicity, you could use an SQL
function to wrap it:

CREATE OR REPLACE FUNCTION dblink_exists(text)
RETURNS bool AS $$
SELECT $1 = ANY (dblink_get_connections())
$$ LANGUAGE sql;

contrib_regression=# SELECT dblink_exists('dtest1');
dblink_exists
---------------
f
(1 row)

I guess it might be worthwhile adding the SQL function definition to
dblink.sql.in as an enhancement in 8.4.

Joe

Just use plproxy and skip all the hassle of dblink :)

On Mon, Jun 2, 2008 at 3:14 AM, Joe Conway <mail(at)joeconway(dot)com> wrote:

> Tom Lane wrote:
>
>> Tommy Gildseth <tommy(dot)gildseth(at)usit(dot)uio(dot)no> writes:
>>
>>> One obvious disadvantage of this approach, is that I need to connect and
>>> disconnect in every function. A possible solution to this, would be having a
>>> function f.ex dblink_exists('connection_name') that returns true/false
>>> depending on whether the connection already exists.
>>>
>>
>> Can't you do this already?
>>
>> SELECT 'myconn' = ANY (dblink_get_connections());
>>
>> A dedicated function might be a tad faster, but it probably isn't going
>> to matter compared to the overhead of sending a remote query.
>>
>
> I agree. The above is about as simple as
> SELECT dblink_exists('dtest1');
> and probably not measurably slower. If you still think a dedicated function
> is needed, please send the output of some performance testing to justify it.
>
> If you really want the notational simplicity, you could use an SQL function
> to wrap it:
>
> CREATE OR REPLACE FUNCTION dblink_exists(text)
> RETURNS bool AS $$
> SELECT $1 = ANY (dblink_get_connections())
> $$ LANGUAGE sql;
>
> contrib_regression=# SELECT dblink_exists('dtest1');
> dblink_exists
> ---------------
> f
> (1 row)
>
> I guess it might be worthwhile adding the SQL function definition to
> dblink.sql.in as an enhancement in 8.4.
>
> Joe
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Joe Conway wrote:

>
> If you really want the notational simplicity, you could use an SQL
> function to wrap it:
>
> CREATE OR REPLACE FUNCTION dblink_exists(text)
> RETURNS bool AS $$
> SELECT $1 = ANY (dblink_get_connections())
> $$ LANGUAGE sql;

Thanks, that seems like a reasonable way to solve this.

--
Tommy Gildseth

Joe Conway wrote:
> Tom Lane wrote:
>> Tommy Gildseth <tommy(dot)gildseth(at)usit(dot)uio(dot)no> writes:
>>> One obvious disadvantage of this approach, is that I need to connect
>>> and disconnect in every function. A possible solution to this, would
>>> be having a function f.ex dblink_exists('connection_name') that
>>> returns true/false depending on whether the connection already exists.
>>
>> Can't you do this already?
>>
>> SELECT 'myconn' = ANY (dblink_get_connections());
>>
>> A dedicated function might be a tad faster, but it probably isn't going
>> to matter compared to the overhead of sending a remote query.
>
> I agree. The above is about as simple as
> SELECT dblink_exists('dtest1');
> and probably not measurably slower. If you still think a dedicated
> function is needed, please send the output of some performance testing
> to justify it.
>
> If you really want the notational simplicity, you could use an SQL
> function to wrap it:
>
> CREATE OR REPLACE FUNCTION dblink_exists(text)
> RETURNS bool AS $$
> SELECT $1 = ANY (dblink_get_connections())
> $$ LANGUAGE sql;

dblink_get_connections() returns null if there are no connections
though, so the above will fail if you haven't already established a
connection, unless you also check for null, and not just false.

I guess you could rewrite the above function to something like:

CREATE OR REPLACE FUNCTION dblink_exists(text)
RETURNS bool AS $$
SELECT COALESCE($1 = ANY (dblink_get_connections()), false)
$$ LANGUAGE sql;

--
Tommy Gildseth