Thread: NOT Null constraint on foreign table not working
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
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 foocreate table foo_test ( a int );\c postgrescreate 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 apostgres=# insert into foo_test values ( null );INSERT 0 1postgres=# select * from foo_test;a---(1 row)-- clean updrop foreign table foo_test;drop server foo_server cascade;\c postgresdrop database foo;Analysis:As per the PG documentation it says that foreign table do support theNOT 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 callExecConstraints(). I am not sure whether it is intentional that we not callingExecConstraints() 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 andnow test behaving as expected. PFA patch for the same.Regards,Rushabh Lathia
Rushabh Lathia
Attachment
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
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
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
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
On Mon, Jan 20, 2014 at 8:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Regards Rushabh Lathia <rushabh.lathia@gmail.com> writes:There has been a great deal of debate about what constraints on foreign
> As per the PG documentation it says that foreign table do support the
> NOT NULL, NULL and DEFAULT.
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.
Rushabh Lathia
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
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