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;
.
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 |