Re: Query on postgres_fdw extension

On Fri, 2021-05-14 at 10:53 -0400, Tom Lane wrote:
> Swathi P <swathi(dot)bluepearl(at)gmail(dot)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.

One possibility might be to define a trigger on the remote table
that fetches the next sequence value if you try to insert NULL.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com