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.albe@xxxxxxxxxxx> 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.bluepearl@xxxxxxxxx> 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
