Re: Query on postgres_fdw extension

Hello everyone.

I got here after encountering the same difficulty, although on a much more
mundane scenario.

I'm used to fdw on a read-only basis. I was just inserting a new record on
a foreign table and got blocked... and after much searching got here.

Not to rant or anything, but I am completely surprised by this limitation.
As far as I can see it is impossible to use fdw to insert records on 99% of
tables, since all have some kind of primary sequential key.

I'm just a user so cannot really understand the intricacies involved in
this process. Tried to find past messages and up to 2013 without
understanding the real problem.

The simplest workaround seems to be to quit using auto-numbering mechanisms
and implement numbering trigger functions, which is really just going back
to the 90s...

Another option would be a local function that would get the remote default
and use it in a local insert trigger. The complexity is just orders of
magnitude higher. We are talking about auto-numbering keys...

I don't know... realistically what do you guys see as a best/simple
approach?

Having 2 tables seems to me the easiest, less complex solution, but it's
hard on maintenance...

And don't take this the wrong way, but is it really that hard to have a
compromise: if there's a serial on the remote, then the user could change
the local definition so to just send the "DEFAULT" keyword to the remote
and let it figure it out? At least the user would have a chance of setting
the preferred behavior without much fuss, on a per-table basis. And still
use the basic functionality of serial/identity columns. PostgreSQL has such
complex stuff that this seems odd to be left out.

Well I hope I didn't cross over as negative or anything. I do love pgsql
and always promote it as the best thing under the sun.

Best regards,
Duarte

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> escreveu no dia quinta, 20/01/2022
à(s) 15:36:

> 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
>
>
>
>
>
>