Re: RFD: access to remore databases: altername suggestion - Mailing list pgsql-hackers

You are attacking here two things: 

a) schemas, which should be done in 7.3, thus multiple databases on same
host would be unnecessary.

b) connections to remote host' databases, which is partially implemented
already (in a ugly way, but...) see contrib/dblink

What you described is a syntactic sugar to implement b) which isn't a bad
idea, but just consider, it is already done. sorta. 

On Wed, 3 Oct 2001 manieq@idea.net.pl wrote:

> Hi!
> 
> 0. I think access to other databases is really important. There was
> a discussion about that. Using a dot operator to specify a
> database (schema) seems to be very standard and elegant.
> But there is another way to implement it. Here is my
> suggestion.
> 
> 1. First, some syntax:
> 
> CREATE [ SHARED ] [ TRUSTED ] CONNECTION conn_name
>   USING 'conn_string'
>   [ CONNECT ON { LOGIN | USE } ]
>   [ DISCONNECT ON { LOGOUT | COMMIT } ];
> 
> Description
>   Creates a connection definition (Oracle: database link) to
>   a remote database.
> 
> SHARED
>   Means only one instance of connection exists and is accessible
>   to all qualified users.
> 
> TRUSTED
>   Only superusers can use this connection (like TRUSTED modifier
>   in CREATE LANGUAGE).
> 
> conn_name
>   Just an identifier.
> 
> 'conn_string'
>   Connect string in standard form accepted by libpq
>   'PQconnectdb' function.
> 
> CONNECT ON { LOGIN | USE }
>   Defines whether connection should be established when
>   user logs in, or when references remote object for the
>   first time (default).
> 
> DISCONNECT ON { LOGOUT | COMMIT }
>   Defines whether connection should be closed when
>   user logs out (default), or when transaction is ended (COMMIT,
>   ROLLBACK, but also exiting).
> 
> 2. Additional commands
> 
> ALTER CONNECTION conn_name
>   USING 'conn_string'
>   [ CONNECT ON { LOGIN | USE } ]
>   [ DISCONNECT ON { LOGOUT | COMMIT } ];
> 
> Description
>   Changes behaviour of a defined connection (same parameters
>   as for CREATE CONNECTION).
> 
> 
> DROP CONNECTION conn_name;
> 
> Description
>   Hmm... drop the connection definition?
> 
> 
> Also a new privilege CONNECT should be added, so
> GRANT CONNECT ON remote_database TO SCOTT;
> can be processed.
> 
> 
> 3. How to use this?
> 
> SELECT local.id, remote.name
>   FROM orders local, emp@remote_database remote
>   WHERE local.emp_id = remote.id;
> 
> SELECT give_a_raise_proc@rempte_database(1000);
> 
> 
> 4. Some notes (in random order)
> 
> If a 'conn_string' does not contain a user/password information,
> connection is performed using current user identity. But, for SHARED
> connection always use a 'nobody' account (remeber to create
> 'nobody' user on remote database). For security reasons
> 'conn_string' must be stored in encrypted form.
> 
> When CONNECT ON LOGIN is used, connection is etablished
> only if user has CONNECTprivilege granted on this. For TRUSTED
> connection also superuser rights must be checked.
> 
> If first remote object is accessed within a transaction, a remote
> transaction should be started. When trancaction ends, remote
> transaction should also be ended same way (commit or rollback).
> 
> SHARED connection should be established when first user logs in
> or uses remote object (depends on CONNECT ON clause) and
> terminated when last user ends transaction or disconnects
> (depens on DISCONNECT ON clause). Of course no remote
> transaction can be performed for SHARED connection.
> 
> Of course it would require lot of work, but can be parted. The
> minimum  IMHO can be a SHARED connection with
> CONNECT ON USE and DISCONNECT ON LOGOUT behaviour.
> 
> 5. Conclusion
> 
> I know it is much easier to 'invent' a new functionality than
> to implement it. I also realize this proposal is not complete
> nor coherent. Still want to listen/read your opinions about it.
> 
> Regards,
> 
> Mariusz Czulada
> 
> P.S.: Is it planned to add 'auto_transaction' parameter on server
> or database levels, so events like login, commit or rolback
> automaticly start a new transaction without 'BEGIN WORK'
> (like Oracle does)?
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 
>