Re: Query on postgres_fdw extension

On Fri, May 14, 2021 at 8:23 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> [ removing -bugs ]
>
> 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.

Then the serial column cannot be selected via the foreign table.
Maybe, drop the serial column from the foreign table before insertions
and add the serial column before the selects from the foreign table.
This is not elegant though.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com