Foreign table inserts don't use the remote sequence

Question

I have a set of applications accessing two different PostgreSQL 9.6 DBs on the same server. Due to some application limitations, one application accesses a handful of tables via FDW in one DB to the other.

Something like this:
DB1.fdw_table_a -> DB2.table_a

fdw_table_a is only used for inserts of log data. This table has an id column, which is a bigint sequence. The sequence exists in DB1 (on the foreign table) and in DB2 (the "real" table). This works as it should and all is well.

Now there's a need to have another application (again with limited access capabilities) perform inserts into the "real" table, DB2.table_a. In testing, I can see some inconsistencies in the id column, but no obvious issues have appeared.

I can see in the customer-facing environments that the DB1 FDW sequence is used as expected, but when inserts start directly on the DB2 'real' table, that sequence will start at 1 (as it has never been used).

Are there other things we should be considering in this environment? Are there some issues that could arise from overlap in these two sequences inserting into the table?

Answer 1

The sequence only gets used if you omit the id column in the INSERT statement. But postgres_fdw will never omit a column, as you can see from the execution plan.

One way to solve the problem is to use a foreign table that does not contain the id column. Then any insert into that foreign table will use the sequence to populate that column.

Answer 2

The following plan from 2014 is still valid today.

=# CREATE SEQUENCE seq;
CREATE SEQUENCE
=# CREATE VIEW seq_view AS SELECT nextval('seq') as a;
CREATE VIEW

=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
=# CREATE SERVER postgres_server
-# FOREIGN DATA WRAPPER postgres_fdw
-# OPTIONS (host 'localhost', port '5433', dbname 'postgres');
CREATE SERVER
=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS (password '');
CREATE USER MAPPING
=# CREATE FOREIGN TABLE foreign_seq_table (a bigint)
-# SERVER postgres_server OPTIONS (table_name 'seq_view');
CREATE FOREIGN TABLE

=# CREATE FUNCTION foreign_seq_nextval() RETURNS bigint AS
-# 'SELECT a FROM foreign_seq_table;' LANGUAGE SQL;
CREATE FUNCTION

=# CREATE TABLE tab (a int DEFAULT foreign_seq_nextval());
CREATE TABLE
=# INSERT INTO tab VALUES (DEFAULT), (DEFAULT), (DEFAULT);
INSERT 0 3
=# SELECT * FROM tab;
 a
----
 9
10
11
(3 rows)

https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/