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

[Peter Eisentraut]

On 11/29/17 19:59, Michael Paquier wrote:
> On Wed, Nov 29, 2017 at 1:53 PM, Michael Paquier
>  wrote:
>> On Wed, Nov 29, 2017 at 8:12 AM, Tom Lane  wrote:
>>> 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.)
>>
>> So this gives a good reason to do nothing or return an error at
>> postgres_fdw level for OVERRIDING?
> 
> Moving the patch to next CF as the discussion has not settled yet.

I think I'll close this patch.  I was operating under the assumption
that there is a bug of omission in PG10 here.  But it seems this
combination of features just isn't meant to work together at this time.

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

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

[Michael Paquier]

On Wed, Nov 29, 2017 at 1:53 PM, Michael Paquier
 wrote:
> On Wed, Nov 29, 2017 at 8:12 AM, Tom Lane  wrote:
>> 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.)
>
> So this gives a good reason to do nothing or return an error at
> postgres_fdw level for OVERRIDING?

Moving the patch to next CF as the discussion has not settled yet.
-- 
Michael

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

[Michael Paquier]

On Wed, Nov 29, 2017 at 8:12 AM, Tom Lane  wrote:
> 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.)

So this gives a good reason to do nothing or return an error at
postgres_fdw level for OVERRIDING?
-- 
Michael

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

[Tom Lane]

Peter Eisentraut  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

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

[Peter Eisentraut]

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