Thread: NOT Null constraint on foreign table not working

NOT Null constraint on foreign table not working

From
Rushabh Lathia
Date:
Hello,

Please consider the following test:

create database foo;
\c foo

create table foo_test ( a int );

\c postgres

create extension if not exists postgres_fdw;
create server foo_server foreign data wrapper postgres_fdw options ( dbname  'foo' );
create user mapping for current_user server foo_server;

create foreign table foo_test ( a int not null) server foo_server;

-- insert should return error for because NOT NULL constraint on column a
postgres=# insert into foo_test values ( null );
INSERT 0 1

postgres=# select * from foo_test;
 a 
---
  
(1 row)

-- clean up
drop foreign table foo_test;
drop server foo_server cascade;
\c postgres
drop database foo;


Analysis:

As per the PG documentation it says that foreign table do support the
NOT NULL, NULL and DEFAULT.


But when I tried the NOT NULL constraint, its not working for the foreign tables.
Looking at the code into ExecInsert(), for the foreign table missed to call
ExecConstraints(). I am not sure whether it is intentional that  we not calling
ExecConstraints() in case of foreign server or its missed. 
Do share your thought on this.

I quickly fix the issue by adding ExecConstraints() call for foreign table and
now test behaving as expected. PFA patch for the same.

Regards, 
Rushabh Lathia

Attachment

Re: NOT Null constraint on foreign table not working

From
Rushabh Lathia
Date:
Please consider attached patch here as earlier attached wrong patch.

Sorry for the inconvenience. 


On Mon, Jan 20, 2014 at 1:21 PM, Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
Hello,

Please consider the following test:

create database foo;
\c foo

create table foo_test ( a int );

\c postgres

create extension if not exists postgres_fdw;
create server foo_server foreign data wrapper postgres_fdw options ( dbname  'foo' );
create user mapping for current_user server foo_server;

create foreign table foo_test ( a int not null) server foo_server;

-- insert should return error for because NOT NULL constraint on column a
postgres=# insert into foo_test values ( null );
INSERT 0 1

postgres=# select * from foo_test;
 a 
---
  
(1 row)

-- clean up
drop foreign table foo_test;
drop server foo_server cascade;
\c postgres
drop database foo;


Analysis:

As per the PG documentation it says that foreign table do support the
NOT NULL, NULL and DEFAULT.


But when I tried the NOT NULL constraint, its not working for the foreign tables.
Looking at the code into ExecInsert(), for the foreign table missed to call
ExecConstraints(). I am not sure whether it is intentional that  we not calling
ExecConstraints() in case of foreign server or its missed. 
Do share your thought on this.

I quickly fix the issue by adding ExecConstraints() call for foreign table and
now test behaving as expected. PFA patch for the same.

Regards, 
Rushabh Lathia




--
Rushabh Lathia
Attachment

Re: NOT Null constraint on foreign table not working

From
Tom Lane
Date:
Rushabh Lathia <rushabh.lathia@gmail.com> writes:
> As per the PG documentation it says that foreign table do support the
> NOT NULL, NULL and DEFAULT.

There has been a great deal of debate about what constraints on foreign
tables ought to mean.  Right now, at least for postgres_fdw, they're just
taken as documentation of constraints that are supposed to exist on the
far side.  It's not clear what's the point of trying to enforce them
against insertions done locally if the remote table lacks them --- any
table update done on the far side could still violate the constraint.

We might change this in response to a well-reasoned argument, but it won't
happen just because somebody lobs a quick-and-dirty patch over the fence.

If we were going to enforce them locally, I'd opine it should be the FDW's
task to do it, anyway.  It might have more knowledge about the best way
to do it than nodeModifyTable.c can, and if not it could still call
ExecConstraints for itself.
        regards, tom lane



Re: NOT Null constraint on foreign table not working

From
Amit Kapila
Date:
On Mon, Jan 20, 2014 at 8:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rushabh Lathia <rushabh.lathia@gmail.com> writes:
>> As per the PG documentation it says that foreign table do support the
>> NOT NULL, NULL and DEFAULT.
>
> There has been a great deal of debate about what constraints on foreign
> tables ought to mean.  Right now, at least for postgres_fdw, they're just
> taken as documentation of constraints that are supposed to exist on the
> far side.  It's not clear what's the point of trying to enforce them
> against insertions done locally if the remote table lacks them --- any
> table update done on the far side could still violate the constraint.

What is the reason for keeping DEFAULT behaviour different than
constraints. Right now the behaviour for DEFAULT is if it is
defined on foreign table, then it will use that even if original table has
different or no default value?

Create Database foo;
\c foo
create table tbl(c1 int Default 20);

\c postgres
create foreign table tbl(c1 int Default 10) server pgdbfdw; --here
pgdbfdw is server for postgres_fdw
insert into tbl values(Default);
select * from tbl;c1
----10
(1 row)

\c foo
insert into tbl values(Default);
select * from tbl;c1
----1020
(2 rows)


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com



Re: NOT Null constraint on foreign table not working

From
Tom Lane
Date:
Amit Kapila <amit.kapila16@gmail.com> writes:
> On Mon, Jan 20, 2014 at 8:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> There has been a great deal of debate about what constraints on foreign
>> tables ought to mean.

> What is the reason for keeping DEFAULT behaviour different than
> constraints. Right now the behaviour for DEFAULT is if it is
> defined on foreign table, then it will use that even if original table has
> different or no default value?

If you look back to the original thread about the writable-foreign-tables
patch, we expended a lot of sweat on that point too.  The ideal thing IMO
would have been to allow the remote end's default specification to control
what happens, but we found enough difficulties with that that we ended up
punting and allowing the default expression to be evaluated locally.
I'm not terribly satisfied with that result, but that's where we are.

Another thing to keep in mind is that the preferred behavior isn't
necessarily the same for every FDW.  If there isn't a remote SQL server
underlying a foreign table (think file_fdw for instance) then you might
end up with different choices about what defaults and constraints mean.

We basically ran out of time to deal with these issues back in 9.3.
It'd be worth thinking through a holistic proposal about how it ought
to work across a range of FDW types.
        regards, tom lane



Re: NOT Null constraint on foreign table not working

From
Amit Kapila
Date:
On Tue, Jan 21, 2014 at 9:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Amit Kapila <amit.kapila16@gmail.com> writes:
>> On Mon, Jan 20, 2014 at 8:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> There has been a great deal of debate about what constraints on foreign
>>> tables ought to mean.
>
>> What is the reason for keeping DEFAULT behaviour different than
>> constraints. Right now the behaviour for DEFAULT is if it is
>> defined on foreign table, then it will use that even if original table has
>> different or no default value?
>
> If you look back to the original thread about the writable-foreign-tables
> patch, we expended a lot of sweat on that point too.  The ideal thing IMO
> would have been to allow the remote end's default specification to control
> what happens, but we found enough difficulties with that that we ended up
> punting and allowing the default expression to be evaluated locally.
> I'm not terribly satisfied with that result, but that's where we are.

okay, I think we can specify more clearly in documentation of Foreign Table
as right now it is bit difficult to get the right behaviour by reading
documentation. Another thing could be to return Syntax Error like it does
for other constraints like CKECK.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com



Re: NOT Null constraint on foreign table not working

From
Rushabh Lathia
Date:



On Mon, Jan 20, 2014 at 8:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rushabh Lathia <rushabh.lathia@gmail.com> writes:
> As per the PG documentation it says that foreign table do support the
> NOT NULL, NULL and DEFAULT.

There has been a great deal of debate about what constraints on foreign
tables ought to mean.  Right now, at least for postgres_fdw, they're just
taken as documentation of constraints that are supposed to exist on the
far side.  It's not clear what's the point of trying to enforce them
against insertions done locally if the remote table lacks them --- any
table update done on the far side could still violate the constraint.

We might change this in response to a well-reasoned argument, but it won't
happen just because somebody lobs a quick-and-dirty patch over the fence.

If we were going to enforce them locally, I'd opine it should be the FDW's
task to do it, anyway.  It might have more knowledge about the best way
to do it than nodeModifyTable.c can, and if not it could still call
ExecConstraints for itself.

Submitted patch was never intended to get checked in without the proper discussion
and decision about what behaviour should be for foreign table constraint. Sorry if I
passed wrong message but was never intended.

I found constraints on foreign table is very useful for the application when the multiple
user accessing same remote table using fdw and both user want to enforce different
constraint on particular table or different user want to enforce different DEFAULT
expression for the same table column.

I agree with you that if we want to enforce constraint locally then it should be
FDW's task to do it rather then nodeModifyTable.c.

Regards 
Rushabh Lathia

Re: NOT Null constraint on foreign table not working

From
Albe Laurenz
Date:
Rushabh Lathia wrote:
> I found constraints on foreign table is very useful for the application when the multiple
> user accessing same remote table using fdw and both user want to enforce different
> constraint on particular table or different user want to enforce different DEFAULT
> expression for the same table column.
> 
> I agree with you that if we want to enforce constraint locally then it should be
> FDW's task to do it rather then nodeModifyTable.c.

I believe that a column of a foreign table should be NOT NULL only if
it is guaranteed that it cannot contain NULL values.  Doesn't the planner
rely on that?

But PostgreSQL cannot guarantee that, that has to happen on the remote side
(or in the FDW).  I think that it is best that an error for a constraint
violation is thrown by the same entity that guarantees that the constraint
is respected.

So I agree with your last statement.

Yours,
Laurenz Albe

Re: NOT Null constraint on foreign table not working

From
Tom Lane
Date:
Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> I believe that a column of a foreign table should be NOT NULL only if
> it is guaranteed that it cannot contain NULL values.  Doesn't the planner
> rely on that?

The planner does expect that constraints tell the truth.  I don't remember
how significant a false NOT NULL constraint might be, but certainly false
CHECK constraints can give rise to incorrect plans.

> But PostgreSQL cannot guarantee that, that has to happen on the remote side
> (or in the FDW).  I think that it is best that an error for a constraint
> violation is thrown by the same entity that guarantees that the constraint
> is respected.

A point worth making here is that it's already implicit in the contract
that the CREATE FOREIGN TABLE command accurately represents what the
far-side table is.  If you get the column datatypes wrong, for example,
it's possible to have subtle semantic bugs not all that different from
what will happen with an incorrectly-deduced plan.  And we don't make
any attempt to slap your wrist for that.  So I don't see that there's
anything fundamentally wrong with the position that any NOT NULL or CHECK
constraints attached to a foreign table must be accurate reflections of
constraints that exist on the far side, rather than something we should
enforce locally.  (Note that this argument applies to FDWs for remote SQL
servers, but not necessarily for FDWs for non-SQL data sources, where
conceivably the CREATE FOREIGN TABLE command actually is itself the
authoritative truth.  Such an FDW would then be responsible for enforcing
constraints.)

I agree though that we've failed miserably to explain this in the docs.
        regards, tom lane