Re: Commit 4dba331cb3 broke ATTACH PARTITION behaviour.

On Thu, Mar 29, 2018 at 7:46 PM, Alvaro Herrera <alvherre@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