Postgres 9.4 Foreign Data wrapper "FDW" is unable to send serial data type between insertion from different ends

Question

I have a simple 2 CentOS servers configurations using both postgres-9.4 to simulate the FDW scenario in Postgres-9.4.

I used fdw to link a simple table with a another database on another server, reading worked perfectly from both ends,the issue was with the serial primary key, it was not in sync; in other words, If I inserted from the original table, after I inserted from the foreign table, it doesn't sync the count. and vise versa.

Answer 1

I had the same problem, and tried it like Negma suggested in his blog. This solution only works in case you insert only one row. In case you insert more rows in the same transaction, select max(id) will always return the same id and you will get not unique ids.

I have solved this by changing the type of the id from serial/integer to uuid. Then you can do the same as Negma suggested but with gen_random_uuid() from the pgcrypto EXTENSION.

So at the foreign server I did:

ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT gen_random_uuid();

And the same for the foreign table.