RFD: access to remore databases: altername suggestion

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)?