Date: Thu, 10 May 2018 10:37:11 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Robert Haas <robertmhaas@gmail.com>,
	Simon Riggs <simon@2ndquadrant.com>,
	Arseny Sher <a.sher@postgrespro.ru>,
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Indexes on partitioned tables and foreign partitions
Message-ID: <20180510013711.GD26879@paquier.xyz>
References: <87sh71cakz.fsf@ars-thinkpad>
 <CANP8+j+L5W22kuiMMwtjhanqMMos_eRfuE=K6V4Krw_hgpgEcA@mail.gmail.com>
 <CA+TgmoYYEdFeAh8FOwCfB+kgji18WmOoVAEwvYBJtMSQFQ+_Ng@mail.gmail.com>
 <be1e6f22-26ef-cbef-827c-1c8dcb3c0484@lab.ntt.co.jp>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="/unnNtmY43mpUSKx"
Content-Disposition: inline
In-Reply-To: <be1e6f22-26ef-cbef-827c-1c8dcb3c0484@lab.ntt.co.jp>
Precedence: bulk


--/unnNtmY43mpUSKx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Thu, May 10, 2018 at 10:15:05AM +0900, Amit Langote wrote:
> While I agree with this, let me point out that we do allow inherited check
> constraints on foreign tables that are not actually enforced locally.
>=20
> create table p (a int) partition by range (a);
> create table p1 partition of p for values from (minvalue) to (1);
> create table p2base (a int);
> create foreign table p2 partition of p for values from (1) to (maxvalue)
> server loopback options (table_name 'p2base');
>=20
> alter table p add check (a between -1000 and 1000);
>=20
> -- routed to foreign partition, which doesn't enforce check constraints
> insert into p values (1001);
> INSERT 0 1

That's not actually a surprise, right?  Since foreign tables can be part
of inheritance trees in 9.5, CHECK constraints on foreign tables are not
enforced locally, but used as planner hints to guess how a query would
work remotely.  So getting partition children to work the same way is
consistent.

> We have to do the following to prevent that.
>=20
> alter table p2base add check (a between -1000 and 1000);
> insert into p values (1001);
> ERROR:  new row for relation "p2base" violates check constraint
> "p2base_a_check"
> DETAIL:  Failing row contains (1001).
> CONTEXT:  remote SQL command: INSERT INTO public.p2base(a) VALUES ($1)

This bit looks natural to me as well.
--
Michael

--/unnNtmY43mpUSKx
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAlrzokcACgkQnvQgOdby
QH3AhxAAjx+8sQWiELhCJ+wNuJTZQhNMsY+TBTzlFg5Bvf5fpKTuqbabqZUfeYHx
P/fZWT3OWlal1Hrkz+usPEa4/zDayoJPGXkvOK3C2hD5HvLzfMOWr5wIB6QUj8yd
ys6bbCAnyVmVMhDiXJeBpczNPOy6rakiJCmwZR7GoKSwZyuwzD65Bgu8mJ5kIAjl
AG5/sQyxPk2sYLsRGN98XYz7RV4zCxN2u/Eed6bjnIDU5BvbJvErbhKQwQFRclte
sxAZw90x95c6uMlp794V36Nt+Vr3CZKmQkux+rsorMd228ElOlxcKfpba5w+Coo5
hfZyt9yUXkm6Be4+nC+Hz5dJvTjARao6BYVGe8Hnseo2ngiEeJXXgWQWuTN3kqR7
wxZtzIJlJlgj/PUnUYL3rEQFlV46LHbXRJ68PTspEvA0zA/lvhcrndjtFbotLm4m
wvoaoLosw+OUlpPGkc4eHoCtS0139jwvZXUkrYzJtQYZugq5sOUy9PR+sCME1FL1
rSUsIj3ifblxAtIr4yPVxwtIe3gXTlG3Bk7umnlVAE+5AGNWbVyqhqg8mGETaLNK
kPgkwSay2ox0t3QDsLh7EPhbjgcuVJ+Mr+RPN2K423H02j4N2QFSYSMbR6c+jYFC
XJQ8tn7XJlGhD1A/k/bFFCtAR2MLs2GjoDSOBukP8w4YflAZUVY=
=yPBT
-----END PGP SIGNATURE-----

--/unnNtmY43mpUSKx--