Thread: SQL/MED dummy vs postgresql wrapper

I have been thinking that we are setting up the foreign-data wrapper dummies 
wrongly.

Eventually, the postgresql_fdw library should contain an implementation that 
actually connects to a PostgreSQL database and does useful things (dblink 
replacement, basically).  Right now, we are proposing to use it as connection 
information storage.  But I think that might get us in trouble later.  
Loading a fully implemented postgresql_fdw might do significant work, which 
you don't really want when you are just querying the connection parameters.  
(This is not completely theoretical: Firing up libpq might do zeroconf 
queries or in the far future even connection pooling.)  We have conflicting 
use cases there: We are loading up a library that we don't intend to use.

I think the proper approach is to separate these concerns: Have one FDW 
implementation that (eventually) does real PostgreSQL connectivity, and one 
that just does parameter storage.  We could name the latter postgresql_dummy, 
but I also have another idea: We could just use the dummy wrapper and set an 
option for the foreign data wrapper that tells what options are valid.  That 
is, you would say

CREATE FOREIGN DATA WRAPPER postgresql_dummy LIBRARY 'dummy_fdw' LANGUAGE C   OPTIONS (valid_options
'{host,port,dbname,user,password...}');

CREATE SERVER server1 FOREIGN DATA WRAPPER postgresql_dummy OPTIONS 
(host 'localhost');

CREATE USER MAPPING FOR current_user SERVER server1 OPTIONS 
(password 'seKret');

That way, you would have more flexibility, less code, and less potential 
conflicts in the future.

Comments?

Peter Eisentraut <peter_e@gmx.net> wrote:

> We could just use the dummy wrapper and set an 
> option for the foreign data wrapper that tells what options are valid.  That 
> is, you would say
> 
> CREATE FOREIGN DATA WRAPPER postgresql_dummy LIBRARY 'dummy_fdw' LANGUAGE C
>     OPTIONS (valid_options '{host,port,dbname,user,password...}');

Looks reasonable, but is 'dummy_fdw' a proper name for it?
I think 'template_fdw' or something might be better.

If we will complete 'postgres_fdw' as a replacement of dblink, the fdw
will not need the flexibility because it should accept only valid
parameters for PostgreSQL. Then, 'dummy_fdw' might be kept only for
user-defined FDWs. Since users see the library name, we'd better to
choose more suitable name for it.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Peter Eisentraut wrote:

> Eventually, the postgresql_fdw library should contain an implementation that 
> actually connects to a PostgreSQL database and does useful things (dblink 
> replacement, basically).  Right now, we are proposing to use it as connection 
> information storage.  But I think that might get us in trouble later.  
> Loading a fully implemented postgresql_fdw might do significant work, which 
> you don't really want when you are just querying the connection parameters.  

Actually the connection lookup doesn't even require loading the FDW
library. It does so at the moment because GetForeignDataWrapper() loads
the library automatically.

> I think the proper approach is to separate these concerns: Have one FDW 
> implementation that (eventually) does real PostgreSQL connectivity, and one 
> that just does parameter storage.  We could name the latter postgresql_dummy, 
> but I also have another idea: We could just use the dummy wrapper and set an 
> option for the foreign data wrapper that tells what options are valid.  That 
> is, you would say
> 
> CREATE FOREIGN DATA WRAPPER postgresql_dummy LIBRARY 'dummy_fdw' LANGUAGE C
>     OPTIONS (valid_options '{host,port,dbname,user,password...}');
> 

How about extending the syntax by adding validator function(s) instead (similar
to CREATE LANGUAGE)? For instance for postgresql wrapper we might want to check
that a password is provided for a user mapping. The default validator for postgres
wrapper would be supplied, but nothing prevents the user from replacing it with
custom validator. Additionally it is possible to run-the same validator by
connection lookup, so that the connection can be sanity checked.

Something like:

CREATE FOREIGN DATA WRAPPER postgresql LIBRARY 'dummy_fdw' LANGUAGE CVALIDATOR postgresql_fdw_validator;

It is also possible to guess the validator function name from the FDW name.

Additionally, if we are taking this route, it no longer makes sense to provide
the empty shared libraries. We could drop the shared libraries altogether and
loosen the syntax to:

CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
or just:
CREATE FOREIGN DATA WRAPPER postgresql;

regards,
Martin

Martin Pihlak wrote:
> How about extending the syntax by adding validator function(s) instead (similar
> to CREATE LANGUAGE)? For instance for postgresql wrapper we might want to check
> that a password is provided for a user mapping. The default validator for postgres
> wrapper would be supplied, but nothing prevents the user from replacing it with
> custom validator. Additionally it is possible to run-the same validator by
> connection lookup, so that the connection can be sanity checked.

> Additionally, if we are taking this route, it no longer makes sense to provide
> the empty shared libraries. We could drop the shared libraries altogether and
> loosen the syntax to:
> 
> CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
> or just:
> CREATE FOREIGN DATA WRAPPER postgresql;

I have implemented this now.