Re: RFD: access to remore databases: altername suggestion

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(at)idea(dot)net(dot)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(at)remote_database remote
> WHERE local.emp_id = remote.id;
>
> SELECT give_a_raise_proc(at)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>