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