Re: BUG #15042: Parition by list using enums odd behaviour

On 2018/02/01 17:58, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      15042
> Logged by:          Damir Ciganović-Janković
> Email address:      damir.ciganovic.jankovic@gmail.com
> PostgreSQL version: 10.1
> Operating system:   Windows 10
> Description:        
> 
> First time asking question, if you like stackoverflow, check this link
>
https://stackoverflow.com/questions/48502345/postgresql-10-partition-by-list-using-enums?noredirect=1#comment84079025_48502345
> 
> If not, I will explain. I am trying to partition my table using enum as
> partition condition, this is what I did:
> 
> create type positivity as enum (
>     'POSITIVE',
>     'NEGATIVE'
> );
> 
> create table test (id int, polarity positivity) partition by list
> (polarity);
> create table test_1 partition of test for values in ('POSITIVE');
> create table test_2 partition of test for values in ('NEGATIVE');
> 
> ####
> 
> explain select * from test where polarity = 'NEGATIVE';
> (output:)
>  explain select * from test where polarity = 'NEGATIVE';
>                           QUERY PLAN
> --------------------------------------------------------------
>  Append  (cost=0.00..76.50 rows=22 width=8)
>    ->  Seq Scan on test_1  (cost=0.00..38.25 rows=11 width=8)
>          Filter: (polarity = 'NEGATIVE'::positivity)
>    ->  Seq Scan on test_2  (cost=0.00..38.25 rows=11 width=8)
>          Filter: (polarity = 'NEGATIVE'::positivity)
> (5 rows)
> 
> 
> ####
> I expected that it will only scan test_2, adding constraints on both
> partitions will get me desired behaviour:
> 
> alter table test_1 add constraint test_1_check check(polarity='POSITIVE');
> alter table test_2 add constraint test_2_check check(polarity='NEGATIVE');
> 
> explain select * from test where polarity = 'NEGATIVE';
> (output:)
>                           QUERY PLAN
> --------------------------------------------------------------
>  Append  (cost=0.00..38.25 rows=11 width=8)
>    ->  Seq Scan on test_2  (cost=0.00..38.25 rows=11 width=8)
>          Filter: (polarity = 'NEGATIVE'::positivity)
> (3 rows)
> 
> ###
> 
> Is this a bug or is it not implemented yet maybe? Or is it working as
> intended? 

Yeah, it is a bug.  Thanks for reporting it to this mailing list.

I had posted a bug-fix patch in the context of similar thing happening
when list partitioning using an array type column.  See it here:

https://www.postgresql.org/message-id/54745d13-7ed4-54ac-97d8-ea1eec95ae25%40lab.ntt.co.jp

Looks like the underlying issue is same here, so the patch there will fix
this bug too.  If you'd like, please try the patch.

I will try to revive the discussion on that patch, so that someone picks
it up and maybe the bug will be fixed in the next minor version (10.2).

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/54745d13-7ed4-54ac-97d8-ea1eec95ae25%40lab.ntt.co.jp