Обсуждение: [GENERAL] 10 beta 4 foreign table partition check constraint broken?
Is this a bug in Postgres 10b4? Looks like neither partition ranges
nor check constraints are honored in 10b4 when inserting into
partitions that are foreign tables.
Here is a nearly shovel-ready example. Just replace with your
servers/passwords.
-- --------------------------
-- Server 1
-- --------------------------
CREATE DATABASE cluster;
\c cluster
CREATE EXTENSION postgres_fdw;
CREATE SERVER server2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host 'server2', dbname 'cluster');
CREATE USER MAPPING FOR postgres SERVER server2 OPTIONS(user 'postgres', password 'pgpassword');
CREATE TABLE foo ( id INT NOT NULL, name TEXT
) PARTITION BY RANGE (id);
CREATE FOREIGN TABLE foo_10000 PARTITION OF foo FOR VALUES FROM (0) TO (10000)
SERVER server2 OPTIONS (table_name 'foo_10000');
-- --------------------------
-- Server 2
-- --------------------------
CREATE DATABASE cluster;
\c cluster
CREATE TABLE foo_10000 ( id INT NOT NULL, name TEXT
);
-- --------------------------
-- Server 1
-- --------------------------
INSERT INTO foo_10000 VALUES(0,'funky bug'), (100, 'wiggle frank'),
(15000, 'boegger snot');
SELECT * FROM foo;
DROP FOREIGN TABLE foo_10000;
CREATE FOREIGN TABLE foo_10000 PARTITION OF foo (id CONSTRAINT f1 CHECK ((id >= 0) AND (id < 10000)))
FORVALUES FROM (0) TO (10000)
SERVER server2 OPTIONS (table_name 'foo_10000');
INSERT INTO foo_10000 VALUES(0,'funky bug'), (100, 'wiggle frank'),
(15000, 'boegger snot');
SELECT * FROM foo;
.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Sep 15, 2017 at 10:43 PM, Paul Jones <pbj@cmicdo.com> wrote: > Is this a bug in Postgres 10b4? Looks like neither partition ranges > nor check constraints are honored in 10b4 when inserting into > partitions that are foreign tables. Here is what you are looking for in the documentation: https://www.postgresql.org/docs/10/static/sql-createforeigntable.html Constraints on foreign tables (such as CHECK or NOT NULL clauses) are not enforced by the core 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. > Here is a nearly shovel-ready example. Just replace with your > servers/passwords. > > -- -------------------------- > -- Server 2 > -- -------------------------- > > CREATE DATABASE cluster; > \c cluster > > CREATE TABLE foo_10000 ( > id INT NOT NULL, > name TEXT > ); So here I think that you should add a CHECK constraint to this table, and that the behavior of your example works as expected. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Thu, Sep 21, 2017 at 02:59:21PM +0900, Michael Paquier wrote: /tmp/mutt-mayon-1000-26043-945be079d938129298 > On Fri, Sep 15, 2017 at 10:43 PM, Paul Jones <pbj@cmicdo.com> wrote: > > Is this a bug in Postgres 10b4? Looks like neither partition ranges > > nor check constraints are honored in 10b4 when inserting into > > partitions that are foreign tables. > > Here is what you are looking for in the documentation: > https://www.postgresql.org/docs/10/static/sql-createforeigntable.html > Constraints on foreign tables (such as CHECK or NOT NULL clauses) are > not enforced by the core 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. Thank you for the pointer... it is clear that I just didn't read far enough. > > > Here is a nearly shovel-ready example. Just replace with your > > servers/passwords. > > > > -- -------------------------- > > -- Server 2 > > -- -------------------------- > > > > CREATE DATABASE cluster; > > \c cluster > > > > CREATE TABLE foo_10000 ( > > id INT NOT NULL, > > name TEXT > > ); > > So here I think that you should add a CHECK constraint to this table, > and that the behavior of your example works as expected. I will try this, thanks! > -- > Michael . -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general