[ removing -bugs ]
Swathi P <swathi.bluepearl@gmail.com> writes:
> Hence we decided to have the coordinator nodes as stateless and hence
> declared the column with no serial/sequence. Let me know if this makes
> sense.
Attaching serial-sequence defaults on both sides would certainly not
work very well, because the sequences wouldn't stay in sync.
Unfortunately, postgres_fdw just doesn't have a good way right now
to make use of dynamically-generated defaults at the remote server.
If you leave out a column in your INSERT, it's going to compute
and send the locally-defined default (which is just null in this
case), so the remote's default expression is never used.
I remember that we spent a great deal of effort in postgres_fdw's
early days, trying to find a way that we could use the remote's
defaults in cases like this. But everything we tried ended up
causing horrible semantic inconsistencies, so we ended up with
the always-use-the-local-default approach. There was some feeling
that maybe this could be revisited later, but no one's done so.
One conceivable workaround is to do your insertions through a
foreign table that doesn't even have the serial column, so that
the INSERT command received by the remote server lacks that
column and the default gets applied. Probably too messy though.
regards, tom lane