Skip site navigation (1) Skip section navigation (2)

Re: SQL/MED compatible connection manager

From: Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com>
To: Chris Browne <cbbrowne(at)acm(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: SQL/MED compatible connection manager
Date: 2008-10-28 22:15:38
Message-ID: 49078F0A.1000900@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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



In response to

pgsql-hackers by date

Next:From: Joshua DrakeDate: 2008-10-28 22:18:03
Subject: Re: PostgreSQL + Replicator developer meeting 10/28
Previous:From: Simon RiggsDate: 2008-10-28 21:56:55
Subject: Re: Proposal of PITR performance improvement for 8.4.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group