Re: SQL/MED compatible connection manager

Chris Browne wrote:

> Slony-I does some vaguely similar stuff in its handling of "connection paths"; here's the schema:
>
> create table @NAMESPACE(at)(dot)sl_path (
> pa_server int4,
> pa_client int4,
> pa_conninfo text NOT NULL,
> pa_connretry int4,
[snip ...]
> I wouldn't be surprised to find there being some value in using
> something like SQL/MED.
>

Here the pa_conninfo could be replaced with the connection name (actually
SERVER). For the complete connection definition a USER MAPPING (eg. remote
username and password) is also needed. But that can be fetched by the
connection connection lookup function

> One detail I'll point out, that I'm noticing from an application I'm
> working on right now. We might want to have something like a "db
> connection" data type; here's a prototype I put together:
>

> slonyregress1=# create type dbconn as (port integer, dbname text, username text, password text, ssl boolean);
> CREATE TYPE
[snip]
> slonyregress1=# select * from dbconns;
> id | db
> ----+--------------------------------------
> 1 | (5432,slonyregress1,slony,secret!,t)
> (1 row)
>
> I'm not certain that this is forcibly the right representation, but I
> think it is possible that we'd want a finer-grained representation
> than merely a connection string.

Yes -- the server, user mapping and FDW all take generic options. Some of them
might be part of the connect string, others could specify some hints of how the
connection should be handled (driver options etc). DBD-Excel has a particularly
nasty example of those. A fixed type would not be able to cover all of them.
This is where the SQL/MED stuff can help - all of this complexity can be reduced
to a single name. Though it adds the additional step of doing the lookup.

The dbconns example could be written like this:

test=# create table dbconns (id serial primary key, db regserver);
...
test=# insert into dbconns (db) values ('test');
INSERT 0 1
test=# select * from dbconns;
id | db
----+-------------
1 | public.test
(1 row)

And then for the connection details:

test=# select * from pg_get_remote_connection_info('test');
option | value
----------+--------
host | /tmp
port | 6543
dbname | foo
username | bob
password | secret
(5 rows)

This assumes that there is a server "public.test" and a user mapping for
the session user. The option/value pairs are outputted by the "dummy" FDW
that just dumps the generic options for the server and user mapping. A
"smart" FDW could turn this into just a connection string. Still, there
probably should be a set of functions for accessing the raw options/value
pairs as well

regards,
Martin