Cross-Database Integrations with `postgres_fdw` and the Solution for "Auto-Increment" Fields

PostgreSQL, by default, does not allow cross-database queries, even if the databases are on the same server. However, there are solutions for this "limitation" (it's more of an isolation context rather than a limitation) when we need to interact between two or more databases, whether on the same server or not. These solutions are provided by two extensions.

The first extension is dblink, which is older but still very functional and useful. It allows remote queries exclusively on PostgreSQL databases, using functions that embed the standard PostgreSQL client library (libpq). More details about its functionality and available functions can be found in the official PostgreSQL documentation here.

Example of usage (taken from the official documentation):

SELECT * FROM dblink('dbname=mydb options=-csearch_path=',
                     'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';        

With dblink, it is possible to execute any statement supported by libpq, including queries, inserts, updates, calling procedures, creating objects (DDL – Data Definition Language), etc. However, the main limitation of dblink is its verbosity. Although it is possible and advisable to register a "Foreign Server" for it, exposing the remote schema requires the creation of specific views or read-only "Foreign Tables."

The second option is the postgres_fdw extension, which follows the FDW (Foreign Data Wrapper) standard. There are several inter-database FDW options, such as firebird_fdw, tds_fdw (for MS SQLServer/Sybase), oracle_fdw, bigquery_fdw, mysql_fdw, and others. Each comes with its own characteristics and limitations, as all these extensions are community contributions.

Our focus, however, is on postgres_fdw. With this extension, after following some basic steps well-documented on the official PostgreSQL website, it is possible to expose a remote schema in your local database with the default read and write option (which can be changed to read-only).

Problem with Auto-Increment Fields

When using postgres_fdw, a problem can arise when remote tables have auto-increment fields. This happens because the local version of the table does not know the remote sequencer, and remote sequencers are not mapped in the local schema. As a result, the value passed in the insertion will be "null" instead of "default," causing a violation error.

Proposed Solutions

1. Create a View on the Remote Server: Use nextval (a function that increments the sequencer and returns the incremented value) of the sequencer and create a local function to read the view and deliver the default value of the column.
2. Trigger BEFORE INSERT on the Remote Server: Set up a trigger to fetch the increment by nextval when the value is null.

Both solutions work but require changes to the server-side database, which may not be feasible in all situations.

My Proposed Solution

My proposal is to use both extensions (dblink and postgres_fdw). First, we map the remote schema in the traditional way with postgres_fdw. Then, we create a generic function using dblink to trigger the nextval on the server.

CREATE OR REPLACE FUNCTION public.fdw_nextval(seq text)
RETURNS integer LANGUAGE 'sql' AS $BODY$
SELECT sequencia FROM dblink('artigo1', 'SELECT nextval(' || quote_literal($1) || ') AS sequencia') x (sequencia integer);
$BODY$;        

Here, 'artigo1' is the name of the Foreign Server configured in postgres_fdw. The dblink can share the connection information and privileges of the FDW.

After this, we alter the column that invokes the sequencer in the foreign table, setting the default value to call this local function with the name of the remote sequencer.

Example of Table Metadata:

CREATE FOREIGN TABLE IF NOT EXISTS artigo1.id1(
    id integer OPTIONS (column_name 'id') NOT NULL,
    idesc text OPTIONS (column_name 'idesc') COLLATE pg_catalog."default"
)
    SERVER artigo1
    OPTIONS (schema_name 'public', table_name 'id1');        

Change to:

CREATE FOREIGN TABLE IF NOT EXISTS artigo1.id1(
    id integer OPTIONS (column_name 'id') NOT NULL DEFAULT fdw_nextval('id1_id_seq'),
    idesc text OPTIONS (column_name 'idesc') COLLATE pg_catalog."default"
)
    SERVER artigo1
    OPTIONS (schema_name 'public', table_name 'id1');        

Running the DDL:

ALTER FOREIGN TABLE artigo1.id1 ALTER COLUMN id SET DEFAULT fdw_nextval('id1_id_seq');        

Solution for Tables with GENERATED ALWAYS AS IDENTITY

For tables with GENERATED ALWAYS AS IDENTITY, the solution is a bit more labor-intensive. We create two remote mappings: one with all fields and another omitting the auto-generated fields.

Example:

CREATE FOREIGN TABLE IF NOT EXISTS artigo1.id3_read(
    id integer OPTIONS (column_name 'id'),
    idesc text OPTIONS (column_name 'idesc') COLLATE pg_catalog."default"
)
    SERVER artigo1
    OPTIONS (schema_name 'public', table_name 'id3');

CREATE FOREIGN TABLE IF NOT EXISTS artigo1.id3_write(
    idesc text OPTIONS (column_name 'idesc') COLLATE pg_catalog."default"
)
    SERVER artigo1
    OPTIONS (schema_name 'public', table_name 'id3');        

Create a local view with the remote name to orchestrate this, and create a RULE for this view.

CREATE OR REPLACE VIEW artigo1.id3 AS
SELECT id, idesc FROM artigo1.id3_read;

CREATE OR REPLACE RULE ins_id3 AS
    ON INSERT TO artigo1.id3
    DO INSTEAD (
        INSERT INTO artigo1.id3_write (idesc)
        VALUES (new.idesc)
    );        

Now everything is set. This table will also call the correct sequencer. What we did here is configure PostgreSQL to omit the identity column values and pass the remaining values to the foreign table.

Conclusion

With these solutions, it is possible to handle cross-database integrations in PostgreSQL, even with the limitations of auto-increment fields. I hope this has been helpful!

If you need more details or have any questions, I'm here to help!