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. > > 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'); > > alter table p add check (a between -1000 and 1000); > > -- 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. > > 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
signature.asc
Description: PGP signature