Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes:
> I've been playing with a few test cases and I'm a bit confused by how
> some of this is supposed to work. AFAICT, in the SQL standard, foreign
> tables can't have column defaults, but in PostgreSQL it's allowed. This
> creates some semantic differences, I think. For example, if I do this
> in the postgres_fdw.sql test file:
> create table loc2 (f1 int generated always as identity, f2 text);
> create foreign table rem2 (f1 int, f2 text)
> server loopback options(table_name 'loc2');
> insert into rem2(f2) values('hi remote');
Note that this example has nothing to do with any non-standard
extensions: rem2 hasn't got a default.
> then we get the error
> ERROR: cannot insert into column "f1"
> DETAIL: Column "f1" is an identity column defined as GENERATED ALWAYS.
> probably because it resolves f1 on the local server and then sends an
> explicit NULL to insert on the remote.
> I think, however, that it would be more appropriate to send DEFAULT and
> let the remote side sort it out. That way, this command would work
> transparently independent of how the default is defined on the remote
> side. AFAICT, it is not possible to do that.
> Is this defined or explained anywhere?
IIRC, this issue was debated at great length back when we first put
in foreign tables, because early drafts of postgres_fdw did what you
propose here, and we ran into very nasty problems. We eventually decided
that allowing remotely-determined column defaults was a can of worms we
didn't want to open. I do not think that GENERATED columns really change
anything about that. They certainly don't do anything to resolve the
problems we were contending with back then. (Which I don't recall the
details of; you'll need to trawl the archives. Should be somewhere early
in 2013, though, since we implemented that change in commit 50c19fc76.)
regards, tom lane
