Re: [HACKERS] postgres_fdw: Add support for INSERT OVERRIDING clause

On 11/3/17 07:53, Michael Paquier wrote:
> Trying to insert some data using OVERRIDING SYSTEM VALUE on a foreign
> table with a remote relation defined with GENERATED ALWAYS would just
> fail:
> =# insert into id_always_foreign OVERRIDING system VALUE values (8);
> ERROR: 428C9: cannot insert into column "a"
> DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.
> HINT: Use OVERRIDING SYSTEM VALUE to override.
>
> And that's confusing because there is no actual way to avoid this
> error if postgres_fdw is unpatched.
>
> I think that you should add some tests, and make sure that the
> documentation of postgres-fdw.sgml mentions that those two clauses are
> pushed down.

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');

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?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services