Search code examples
sqlpostgresqlprimary-keypostgres-fdw

Postgres BIGSERIAL does not share sequence when inserts are made with multiple remote fdw sources

I am trying to insert into a table in a Postgres database from two other Postgres databases using Foreign Data Wrappers. The objective is to have an autogenerate primary key, independent of the source, as there will be more than two in.

I first defined the tables like so:

Target database:

create table dummy (
  dummy_pk bigserial primary key
  -- other fields
);

Sources databases:

create foreign table dummy (
  dummy_pk bigserial
  -- other fields
) server ... ;

This solution worked fine as long as I inserted from only one source, when I tried to insert from the other one, without specifying dummy_pk, I got this message:

Duplicate key (dummy_pk)=(1)

Because postgres tries to insert an id of 1, I believe the sequence used for each source foreign table is different. I changed the source tables a bit in an attempt to let the target table's sequence do the job for the id:

create foreign table dummy (
  dummy_pk bigint
  -- other fields
) server ... ;

This time I got a diffrent error:

NULL value violates NOT NULL constaint on column « dummy_pk »

Therefore I believe the source server sends a query to the target where the dummy_pk is null, and the target does not replace it with the default value.

So, is there a way I can force the use of the target's sequence in a query executed on the source? Maybe I have to share that sequence, can I create a foreign sequence? I cannot remove the column on the foreign tables as I need a read access to them.

Thanks!

Solution

  • Remove dummy_pk from foreign tables so that destination table does not get NULL nor value and so fall backs to DEFAULT or NULL if no DEFAULT specified. If you attempt to pass DEFAULT to foreign table it will try to use DEFAULT value of foreign table instead.

    create foreign table dummy (
  /*dummy_pk bigserial,*/
  column1 text,
  column2 int2,
  -- other fields
) server ... ;

    Another way would be to grab sequence values from destination server using dblink, but I think this is better (if you can afford to have this column removed from foreign tables).