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