Re: Commit 4dba331cb3 broke ATTACH PARTITION behaviour.

On Thu, Mar 29, 2018 at 7:46 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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(at)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(at)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(at)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