Re: Declarative partitioning - Mailing list pgsql-hackers

Hi Amit,

On 20.04.2016 13:28, Amit Langote wrote:
> On 2016/04/19 23:52, Amit Langote wrote:
>> On Tue, Apr 19, 2016 at 11:26 PM, Alexander Korotkov
>>> Another question is that it might be NOT what users expect from that.  From
>>> the syntax side it very looks like defining something boxes regions for two
>>> keys which could be replacement for subpartitioning.  But it isn't so.
>> Need to check why query with qual b < 100 behaves the way it does.
>> Something's going wrong there with the constraints (partition
>> predicates) that are being generated internally (as mentioned before,
>> still driven by constraint exclusion using the constraints generated
>> on-the-fly).
>>
>> As for the composite range partition bounds in Ildar's example, it's
>> as if the second value in the key never determines the fate of a row
>> going into some partition, therefore no constraints should have been
>> generated for column b of the key.  I'm afraid that's not the case as
>> per the latest patch.  Will fix.
> The strange behavior that Ildar reported should have been fixed with the
> attached updated set of patches (v2):
>
> create table test(a int, b int) partition by range (a, b);
> create table test_1 partition of test for values start (0, 0) end (100, 100);
> create table test_2 partition of test for values start (100, 100) end
> (200, 200);
> create table test_3 partition of test for values start (200, 200) end
> (300, 300);
> CREATE TABLE
> CREATE TABLE
> CREATE TABLE
> CREATE TABLE
>
> insert into test(a, b) values (150, 50);
> INSERT 0 1
>
> select * from test where b < 100;
>    a  | b
> -----+----
>   150 | 50
> (1 row)
>
> explain (costs off) select * from test where b < 100;
>          QUERY PLAN
> ---------------------------
>   Append
>     ->  Seq Scan on test
>           Filter: (b < 100)
>     ->  Seq Scan on test_1
>           Filter: (b < 100)
>     ->  Seq Scan on test_2
>           Filter: (b < 100)
>     ->  Seq Scan on test_3
>           Filter: (b < 100)
> (9 rows)
>
>
> Multi-column range partitioning seems a bit tricky as far as generating
> constraints on individual columns using a partition's lower and upper
> bounds (both composite values) is concerned.  I mentally pictured
> something like the following example scenario:
>
> create table test(a int, b int, c int)
>      partition by range (a, b, c);
> create table test_1 partition of test
>      for values start (0, 0, 0) end (0, 2, 0);
> create table test_2 partition of test
>      for values start (0, 2, 0) end (0, 3, 0);
> create table test_3 partition of test
>      for values start (0, 3, 0) end (0, 4, 0);
> create table test_4 partition of test
>      for values start (0, 4, 0) end (1, 0, 0);
> create table test_5 partition of test
>      for values start (1, 0, 0) end (1, 2, 0);
> create table test_6 partition of test
>      for values start (1, 2, 0) end (1, 3, 0);
> create table test_7 partition of test
>      for values start (1, 3, 0) end (1, 4, 0);
> create table test_8 partition of test
>      for values start (1, 4, 0) end (2, 0, 0);
>
> Useful to think of the above as sequence of ranges [000, 020), [020, 030),
> [030, 040), [040, 100), [100, 120), [120, 130), [130, 140), [140, 200) for
> purposes of finding the partition for a row.
>
> Then constraints generated internally for each partition:
>
> test_1: a = 0 AND b >= 0 AND b <= 2
> test_2: a = 0 AND b >= 2 AND b <= 3
> test_3: a = 0 AND b >= 3 AND b <= 4
> test_4: a >= 0 AND a <= 1
> test_5: a = 1 AND b >= 0 AND b <= 2
> test_6: a = 1 AND b >= 2 AND b <= 3
> test_7: a = 1 AND b >= 3 AND b <= 4
> test_8: a >= 1 AND a <= 2
>
> I will try further to poke holes in my thinking about this.  Please feel
> free to point out if you find any.
>
> Thanks,
> Amit
Thanks for clarification! I tried the updated patch, now it works correctly.

I encountered another problem that concerns expressions as partitioning 
key. Probably there is still some work in progress. But if it will help 
here is my case:

create table inh(a int, b int) partition by range ((a+b));
create table inh_1 partition of inh for values start (0) end (10);
create table inh_2 partition of inh for values start (10) end (20);

Then if we run any SELECT query it crashes postgres:

select * from inh;

Crash occurs in get_check_expr_from_partbound(). It seems that function 
is not yet expecting an expression key and designed to handle only 
simple attributes keys. Backtrace:

#0  strlen () at ../sysdeps/x86_64/strlen.S:106
#1  0x00000000004add8a in hashname (fcinfo=0x7ffdbdb9c760) at hashfunc.c:145
#2  0x000000000099cc08 in DirectFunctionCall1Coll (func=0x4add66 
<hashname>, collation=0, arg1=0) at fmgr.c:1027
#3  0x00000000009724dd in CatalogCacheComputeHashValue (cache=0x26590b0, 
nkeys=2, cur_skey=0x7ffdbdb9cbf0) at catcache.c:207
#4  0x0000000000974979 in SearchCatCache (cache=0x26590b0, v1=32807, 
v2=0, v3=0, v4=0) at catcache.c:1151
#5  0x0000000000988e35 in SearchSysCache (cacheId=6, key1=32807, key2=0, 
key3=0, key4=0) at syscache.c:1006
#6  0x0000000000988fe3 in SearchSysCacheAttName (relid=32807, 
attname=0x0) at syscache.c:1106
#7  0x000000000098a744 in get_attnum (relid=32807, attname=0x0) at 
lsyscache.c:825
#8  0x000000000056afd2 in get_check_expr_from_partbound 
(rel=0x7f868601ca20, parent=0x7f868601b770, bound=0x26e6ac8) at 
partition.c:1427
#9  0x000000000056bc9e in generate_partition_check_expr 
(rel=0x7f868601ca20) at partition.c:1788
#10 0x000000000056bb5f in RelationGetPartitionCheckExpr 
(rel=0x7f868601ca20) at partition.c:1746
#11 0x0000000000782b5f in get_relation_constraints (root=0x268f1b8, 
relationObjectId=32807, rel=0x26e5cd8, include_notnull=1 '\001') at 
plancat.c:1209
#12 0x0000000000782d74 in relation_excluded_by_constraints 
(root=0x268f1b8, rel=0x26e5cd8, rte=0x268ebf0) at plancat.c:1302
#13 0x000000000072a18d in set_append_rel_size (root=0x268f1b8, 
rel=0x26e5690, rti=1, rte=0x268ea80) at allpaths.c:947
...

-- 
Ildar Musin
i.musin@postgrespro.ru