Foreign tables don't enforce the partition constraint
We don't enforce the constraints defined on foreign tables in ExecInsert()
and ExecUpdate(). (COPY FROM does not support foreign tables at all.)
Since partition constraints are enforced using ExecConstraints() which is
not called for foreign tables, they will not be checked if one inserts
directly into foreign partitions. So:
create table p (a int) partition by list (a);
create table p1t (like p);
create table p2t (like p);
create foreign table p1 partition of p for values in (1)
server loopback options (table_name 'p1t');
create foreign table p2 partition of p for values in (2)
server loopback options (table_name 'p2t');
insert into p1 values (2); -- ungood
insert into p2 values (1); -- ungood
While we have the ability to mark check constraints as being NOT VALID so
that planner can ignore them, partition constraints are assumed to
*always* hold, giving possibly surprising results.
explain (costs off) select * from p where a = 1;
QUERY PLAN
--------------------------
Append
-> Foreign Scan on p1
(2 rows)
select * from p where a = 1;
a
---
(0 rows)
explain (costs off) select * from p where a = 2;
QUERY PLAN
--------------------------
Append
-> Foreign Scan on p2
(2 rows)
select * from p where a = 2;
a
---
(0 rows)
Should we do something about this (treat as an open item)?
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 31, 2017 at 1:36 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
We don't enforce the constraints defined on foreign tables in ExecInsert()
and ExecUpdate(). (COPY FROM does not support foreign tables at all.)
Since partition constraints are enforced using ExecConstraints() which is
not called for foreign tables, they will not be checked if one inserts
directly into foreign partitions. So:create table p (a int) partition by list (a);
create table p1t (like p);
create table p2t (like p);
create foreign table p1 partition of p for values in (1)
server loopback options (table_name 'p1t');
create foreign table p2 partition of p for values in (2)
server loopback options (table_name 'p2t');
insert into p1 values (2); -- ungood
insert into p2 values (1); -- ungoodWhile we have the ability to mark check constraints as being NOT VALID so
that planner can ignore them, partition constraints are assumed to
*always* hold, giving possibly surprising results.explain (costs off) select * from p where a = 1;
QUERY PLAN
--------------------------
Append
-> Foreign Scan on p1
(2 rows)select * from p where a = 1;
a
---
(0 rows)explain (costs off) select * from p where a = 2;
QUERY PLAN
--------------------------
Append
-> Foreign Scan on p2
(2 rows)select * from p where a = 2;
a
---
(0 rows)Should we do something about this (treat as an open item)?
Per https://www.postgresql.org/docs/devel/static/sql-createforeigntable.html,
constraints on the foreign table should represent a constraint that is
being enforced by the remote server. Similarly, a partition constraint
should also be enforced at the foreign server. Probably we should
update documentation of create foreign table to mention this. We have
updated ALTER TABLE ATTACH PARTITION documentation with a note on
foreign tables.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 31, 2017 at 5:11 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
Per https://www.postgresql.org/docs/devel/static/sql-createforeigntable.html,
constraints on the foreign table should represent a constraint that is
being enforced by the remote server.
Right. This is user error. Having the *local* server try to enforce
the constraint would slow down the system without guaranteeing
anything, because somebody could modify the table on the remote server
directly.
Similarly, a partition constraint
should also be enforced at the foreign server. Probably we should
update documentation of create foreign table to mention this.
That is a good idea.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Similarly, a partition constraint
should also be enforced at the foreign server. Probably we should
update documentation of create foreign table to mention this.That is a good idea.
Here's the patch. I am not able to build documents on my laptop because of
recent changes in d63762452434a3a046e8c7d130d5a77c594176e4. So, I was not
able to check whether the patch builds or not. But I am hoping it builds
well.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
Attachments:
cft_doc_change.patchapplication/octet-stream; name=cft_doc_change.patchDownload
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 5d0dcf5..4b9d7dd 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -279,34 +279,33 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<title>Notes</title>
<para>
- Constraints on foreign tables (such as <literal>CHECK</>
- or <literal>NOT NULL</> clauses) are not enforced by the
- core <productname>PostgreSQL</> system, and most foreign data wrappers
- do not attempt to enforce them either; that is, the constraint is
- simply assumed to hold true. There would be little point in such
- enforcement since it would only apply to rows inserted or updated via
- the foreign table, and not to rows modified by other means, such as
- directly on the remote server. Instead, a constraint attached to a
- foreign table should represent a constraint that is being enforced by
- the remote server.
+ Constraints and partition bounds on foreign tables (such as
+ <literal>CHECK</> or <literal>NOT NULL</> clauses) are not enforced by the
+ core <productname>PostgreSQL</> system, and most foreign data wrappers do
+ not attempt to enforce them either; that is, the they is simply assumed to
+ hold true. There would be little point in such enforcement since it would
+ only apply to rows inserted or updated via the foreign table, and not to
+ rows modified by other means, such as directly on the remote server.
+ Instead, a constraint or partition bounds attached to a foreign table
+ should represent a constraint that is being enforced by the remote server.
</para>
<para>
Some special-purpose foreign data wrappers might be the only access
mechanism for the data they access, and in that case it might be
- appropriate for the foreign data wrapper itself to perform constraint
- enforcement. But you should not assume that a wrapper does that
- unless its documentation says so.
+ appropriate for the foreign data wrapper itself to perform constraint or
+ partition bound enforcement. But you should not assume that a wrapper does
+ that unless its documentation says so.
</para>
<para>
- Although <productname>PostgreSQL</> does not attempt to enforce
- constraints on foreign tables, it does assume that they are correct
- for purposes of query optimization. If there are rows visible in the
- foreign table that do not satisfy a declared constraint, queries on
- the table might produce incorrect answers. It is the user's
- responsibility to ensure that the constraint definition matches
- reality.
+ Although <productname>PostgreSQL</> does not attempt to enforce partition
+ bounds or constraints on foreign tables, it does assume that they are
+ correct for purposes of query optimization. If there are rows visible in
+ the foreign table that do not satisfy a declared constraint or partition
+ bounds, queries on the table might produce incorrect answers. It is the
+ user's responsibility to ensure that the constraint definition or partition
+ bound matches reality.
</para>
</refsect1>
Hi Ashutosh,
On 2017/04/03 15:49, Ashutosh Bapat wrote:
Similarly, a partition constraint
should also be enforced at the foreign server. Probably we should
update documentation of create foreign table to mention this.That is a good idea.
Here's the patch.
Thanks for creating the patch.
+ Constraints and partition bounds on foreign tables (such as
We use "partition constraint" instead of "partition bounds" to mean the
implicit constraint of a partition (there are a few instances of that in
the documentation). So, perhaps this could be written as: Constraints
(both the user-defined constraints such as <literal>CHECK</>
or <literal>NOT NULL</> clauses and the partition constraint) are not
enforced by the core <productname>PostgreSQL</> system, ...
And once we've mentioned that a constraint means one of these things, we
need not repeat "partition bounds/constraints" in the subsequent
paragraphs. If you agree, attached is the updated patch.
I am not able to build documents on my laptop because of
recent changes in d63762452434a3a046e8c7d130d5a77c594176e4. So, I was not
able to check whether the patch builds or not. But I am hoping it builds
well.
By the way, docs do build fine despite the error you see.
Thanks,
Amit
Attachments:
cft_doc_change-v2.patchtext/x-diff; name=cft_doc_change-v2.patchDownload
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 5d0dcf567b..57b3156b21 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -279,10 +279,10 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<title>Notes</title>
<para>
- Constraints on foreign tables (such as <literal>CHECK</>
- or <literal>NOT NULL</> clauses) are not enforced by the
- core <productname>PostgreSQL</> system, and most foreign data wrappers
- do not attempt to enforce them either; that is, the constraint is
+ Constraints (both the user-defined constraints such as <literal>CHECK</>
+ or <literal>NOT NULL</> clauses and the partition constraint) are not
+ enforced by the core <productname>PostgreSQL</> system, and most foreign
+ data wrappers do not attempt to enforce them either; that is, the they is
simply assumed to hold true. There would be little point in such
enforcement since it would only apply to rows inserted or updated via
the foreign table, and not to rows modified by other means, such as
On 2017/04/03 16:44, Amit Langote wrote:
Hi Ashutosh,
On 2017/04/03 15:49, Ashutosh Bapat wrote:
Similarly, a partition constraint
should also be enforced at the foreign server. Probably we should
update documentation of create foreign table to mention this.That is a good idea.
Here's the patch.
Thanks for creating the patch.
+ Constraints and partition bounds on foreign tables (such as
We use "partition constraint" instead of "partition bounds" to mean the
implicit constraint of a partition (there are a few instances of that in
the documentation). So, perhaps this could be written as: Constraints
(both the user-defined constraints such as <literal>CHECK</>
or <literal>NOT NULL</> clauses and the partition constraint) are not
enforced by the core <productname>PostgreSQL</> system, ...And once we've mentioned that a constraint means one of these things, we
need not repeat "partition bounds/constraints" in the subsequent
paragraphs. If you agree, attached is the updated patch.
Since it seems that we agree that this documentation tweak is good idea, I
will add this to the open items list to avoid it being missed.
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers