10 beta 4 foreign table partition check constraint broken?

Started by Paul Jonesover 8 years ago3 messagesgeneral

pbj@cmicdo.com

over 8 years ago

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)))
FOR VALUES 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

Michael Paquier

michael@paquier.xyz

over 8 years ago

In reply to: Paul Jones (#1)

Re: 10 beta 4 foreign table partition check constraint broken?

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

pbj@cmicdo.com

over 8 years ago

In reply to: Michael Paquier (#2)

Re: 10 beta 4 foreign table partition check constraint broken?

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