On Thu, Mar 29, 2018 at 7:46 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote:
> Rushabh Lathia wrote: > > > CREATE TABLE foo (a INT, b INT, c VARCHAR) PARTITION BY LIST(a); > > CREATE TABLE foo_p1 PARTITION OF foo FOR VALUES IN (1,2); > > CREATE TABLE foo_p2 PARTITION OF foo FOR VALUES IN (3,4); > > INSERT INTO foo select i,i,i from generate_series(1,4)i; > > > > CREATE TABLE foo_d (like foo); > > INSERT INTO foo_d select i,i,i from generate_series(1,9)i; > > > > ALTER TABLE foo ATTACH PARTITION foo_d DEFAULT; > > > > Above ATTACH PARTITION should fail with "partition constraint is > violated" > > error, but instead it's working on a master branch. > > Hmm, offhand I don't quite see why this error fails to be thrown. > > ATTACH PARTITION should throw an error, because partition table "foo" already have two partition with key values (1, 2,3 4). And table "foo_d" which we are attaching here has : postgres@76099=#select * from foo_d; a | b | c ---+---+--- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 4 | 4 | 4 5 | 5 | 5 6 | 6 | 6 7 | 7 | 7 8 | 8 | 8 9 | 9 | 9 (9 rows) After ATTACH PARTITION, when we see the partition constraint for the newly attached partition: postgres@76099=#\d+ foo_d Table "public.foo_d" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | | | plain | | b | integer | | | | plain | | c | character varying | | | | extended | | Partition of: foo DEFAULT Partition constraint: (NOT ((a IS NOT NULL) AND (a = ANY (ARRAY[1, 2, 3, 4])))) So, it says that this partition (table) should not include values (1,2, 3, 4). But it sill has those values. postgres@76099=#select tableoid::regclass, * from foo; tableoid | a | b | c ----------+---+---+--- foo_p1 | 1 | 1 | 1 foo_p1 | 2 | 2 | 2 foo_p2 | 3 | 3 | 3 foo_p2 | 4 | 4 | 4 foo_d | 1 | 1 | 1 foo_d | 2 | 2 | 2 foo_d | 3 | 3 | 3 foo_d | 4 | 4 | 4 foo_d | 5 | 5 | 5 foo_d | 6 | 6 | 6 foo_d | 7 | 7 | 7 foo_d | 8 | 8 | 8 foo_d | 9 | 9 | 9 (13 rows) So basically ATTACH PARTITION should throw an error when partition constraint is violated. -- Rushabh Lathia www.EnterpriseDB.com