10 beta 4 foreign table partition check constraint broken?

From: Paul Jones <pbj(at)cmicdo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: 10 beta 4 foreign table partition check constraint broken?
Date: 2017-09-15 13:43:15
Message-ID: 20170915134315.GA31038@cmicdo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Luiz Hugo Ronqui 2017-09-15 14:24:45 RES: Table partition - parent table use
Previous Message Rafal Pietrak 2017-09-15 13:04:36 Re: looking for a globally unique row ID