Re: Query on postgres_fdw extension

From: Duarte Carreira <dncarreira(at)gmail(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Swathi P <swathi(dot)bluepearl(at)gmail(dot)com>, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Query on postgres_fdw extension
Date: 2022-01-20 15:59:07
Message-ID: CAHE-9zD2Pzwig01bF4RXKbN1rY6Kbb5manK9o9oBqxivsE-JCg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Vijaykumar Jain 2022-01-20 17:38:59 Re: Query on postgres_fdw extension
Previous Message PG Bug reporting form 2022-01-20 14:35:57 BUG #17375: RECOVERY TARGET TIME RESTORE IS FAILING TO START SERVER

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-01-20 16:45:49 Re: Multiple SELECT statements Using One WITH statement
Previous Message Johannes Graën 2022-01-20 15:28:29 Re: Multiple SELECT statements Using One WITH statement