Here is a WIP patch for a foreign data wrapper based dblink.
It integrates dblink module into core and adds a new functionality,
automatic transaction management. The new interface of dblink is
exported by include/foreign/dblink.h. We can easily write a connector
module for another database because we can reuse transaction and
resource management parts in core.
Syntax to create FDW with connector is below:
CREATE FOREIGN DATA WRAPPER postgresql
VALIDATOR postgresql_fdw_validator
CONNECTOR postgresql_fdw_connector
OPTIONS (...);
contrib/dblink2 is a sample of postgres connector. It exports one function:
CREATE FUNCTION postgresql_fdw_connector(options internal)
RETURNS internal -- returns a connection object
Basic dblink functions are moved to postgres core:
Name | Result type | Argument data types
-------------------+--------------+----------------------
dblink | SETOF record | text, text
dblink_close | boolean | integer
dblink_connect | boolean | text
dblink_connect | boolean | text, text
dblink_disconnect | boolean | text
dblink_exec | bigint | text, text
dblink_fetch | SETOF record | integer, integer
dblink_open | integer | text, text
The new dblink can work together closely with local transactions. If a
local transaction is committed or rollbacked, remote transactions take
the same status with the local one. Please set max_prepared_transactions
to 1 or greater if you could test the patch.
I want pretty much the automatic transaction management. It is useful to
write applied modules like materialized-view-over-network. But it should
be able to be turned off if we don't want it. I'll work on those parts next.
-- connect
CREATE SERVER server_postgres FOREIGN DATA WRAPPER postgresql;
SELECT dblink_connect('conn_postgres', 'server_postgres');
-- commit both local and remote transactions.
BEGIN;
SELECT dblink_exec('conn_postgres', 'UPDATE ...');
COMMIT;
-- rollback both local and remote transactions.
BEGIN;
SELECT dblink_exec('conn_postgres', 'UPDATE ...');
ROLLBACK;
-- disconnect
SELECT dblink_disconnect('conn_postgres');
I've not ported all features in present dblink, but I'd like to hear
wheather the goal and the concepts are reasonable. Comments welcome.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center