From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
Cc: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] postgres_fdw: Add support for INSERT OVERRIDING clause |
Date: | 2017-11-28 23:12:11 |
Message-ID: | 14271.1511910731@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Dilger | 2017-11-28 23:16:18 | Re: pgindent run? |
Previous Message | Peter Geoghegan | 2017-11-28 23:00:04 | Re: [HACKERS] Small improvement to compactify_tuples |