Re: Multi-Column List Partitioning - Mailing list pgsql-hackers

> While reviewing one of the 'Table partitioning' related patches,

> I found that Postgres does not support multiple column based LIST

> partitioning. Based on this understanding, I have started working on

> this feature. I also feel that 'Multi-Column List Partitioning' can

> be benefited to the Postgres users in future.

+1 for the feature. I also think this can help users deal with some

useful cases.

> CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES IN (1, 2), (1, 5), (2,

> 2),(2, 10);

IMHO, listing every single tuple like this might be a bit cumbersome for

the user. What about something like this:

...FOR VALUES IN (1, 2, 3, 4), (11, 22, 33, 44), where the first set

is the list for values of column A and second list is for column B. We

can treat these lists as A X B possible values or simply (a1, b1), (a2,

b2) internally. However I see other proprietary databases already have

syntax something similar that you are proposing here. So, I leave it

open for the thoughts from experts. Also, though what I propose might be

easy from a user perspective, but might not be that easy for

implementation, given that for a larger number of columns in partition list

e.g. A X B X C X D lists become unmanageable.

I did not review the patch in detail, but a quick look at it leaves me

with following comments:

1.

> + * list. Then this function will continue the serach and return the index of

Typo:

s/serach/search

2.

A compiler warning:

partprune.c: In function ‘get_matching_list_bounds’:

partprune.c:2731:20: error: passing argument 5 of ‘partition_list_bsearch’ makes pointer from integer without a cast [-Werror=int-conversion]

 2731 |           nvalues, value, &is_equal);

      |                    ^~~~~

      |                    |

      |                    Datum {aka long unsigned int}

In file included from partprune.c:53:

../../../src/include/partitioning/partbounds.h:120:32: note: expected ‘Datum *’ {aka ‘long unsigned int *’} but argument is of type ‘Datum’ {aka ‘long unsigned int’}

  120 |            int nvalues, Datum *value, bool *is_equal);

      |                         ~~~~~~~^~~~~

3.

And, a server crash with following case:

postgres=# CREATE TABLE t1 (a int) PARTITION BY LIST (a);

CREATE TABLE

postgres=# CREATE TABLE t1p1 PARTITION OF t1 FOR VALUES IN (1, 2, 3);

CREATE TABLE

postgres=# \d+ t1p1

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Failed.

!?>

Stacktrace:

(gdb) bt

#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50

#1  0x00007f5d273c5859 in __GI_abort () at abort.c:79

#2  0x000055779d2eb69d in ExceptionalCondition (conditionName=0x55779d4978d8 "ptr == NULL || nodeTag(ptr) == type", errorType=0x55779d4978c3 "FailedAssertion", 

    fileName=0x55779d4978a0 "../../../src/include/nodes/nodes.h", lineNumber=603) at assert.c:69

#3  0x000055779d03a684 in castNodeImpl (type=T_Const, ptr=0x55779e457b18) at ../../../src/include/nodes/nodes.h:603

#4  0x000055779d04368a in get_qual_for_list (parent=0x7f5d1df829b8, spec=0x55779e457950) at partbounds.c:4155

#5  0x000055779d03ac60 in get_qual_from_partbound (rel=0x7f5d1df82570, parent=0x7f5d1df829b8, spec=0x55779e457950) at partbounds.c:272

#6  0x000055779d2cf630 in generate_partition_qual (rel=0x7f5d1df82570) at partcache.c:379

#7  0x000055779d2cf468 in get_partition_qual_relid (relid=32771) at partcache.c:308

#8  0x000055779d2592bf in pg_get_partition_constraintdef (fcinfo=0x55779e44ee50) at ruleutils.c:2019

#9  0x000055779cec7221 in ExecInterpExpr (state=0x55779e44dfb0, econtext=0x55779e407fe8, isnull=0x7ffddf9b109f) at execExprInterp.c:744

#10 0x000055779cec954f in ExecInterpExprStillValid (state=0x55779e44dfb0, econtext=0x55779e407fe8, isNull=0x7ffddf9b109f) at execExprInterp.c:1819

#11 0x000055779cf1d58a in ExecEvalExprSwitchContext (state=0x55779e44dfb0, econtext=0x55779e407fe8, isNull=0x7ffddf9b109f)

    at ../../../src/include/executor/executor.h:338

#12 0x000055779cf1d602 in ExecProject (projInfo=0x55779e44dfa8) at ../../../src/include/executor/executor.h:372

#13 0x000055779cf1db2f in ExecNestLoop (pstate=0x55779e407ed0) at nodeNestloop.c:241

#14 0x000055779cedf136 in ExecProcNodeFirst (node=0x55779e407ed0) at execProcnode.c:462

#15 0x000055779ced3053 in ExecProcNode (node=0x55779e407ed0) at ../../../src/include/executor/executor.h:257

#16 0x000055779ced5a87 in ExecutePlan (estate=0x55779e407c80, planstate=0x55779e407ed0, use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true, numberTuples=0, 

    direction=ForwardScanDirection, dest=0x55779e425a88, execute_once=true) at execMain.c:1551

#17 0x000055779ced372d in standard_ExecutorRun (queryDesc=0x55779e453520, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:361

#18 0x000055779ced353c in ExecutorRun (queryDesc=0x55779e453520, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:305

#19 0x000055779d13d287 in PortalRunSelect (portal=0x55779e398800, forward=true, count=0, dest=0x55779e425a88) at pquery.c:912

#20 0x000055779d13cec0 in PortalRun (portal=0x55779e398800, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x55779e425a88, altdest=0x55779e425a88, 

    qc=0x7ffddf9b14f0) at pquery.c:756

#21 0x000055779d1361ce in exec_simple_query (

    query_string=0x55779e3367a0 "SELECT inhparent::pg_catalog.regclass,\n  pg_catalog.pg_get_expr(c.relpartbound, c.oid),\n  inhdetachpending,\n  pg_catalog.pg_get_partition_constraintdef(c.oid)\nFROM pg_catalog.pg_class c JOIN pg_catalo"...) at postgres.c:1214

#22 0x000055779d13ad8b in PostgresMain (argc=1, argv=0x7ffddf9b1710, dbname=0x55779e3626f8 "postgres", username=0x55779e3626d8 "hadoop") at postgres.c:4476

#23 0x000055779d0674d3 in BackendRun (port=0x55779e358380) at postmaster.c:4488

#24 0x000055779d066d8c in BackendStartup (port=0x55779e358380) at postmaster.c:4210

#25 0x000055779d062f9b in ServerLoop () at postmaster.c:1742

#26 0x000055779d062734 in PostmasterMain (argc=3, argv=0x55779e3308b0) at postmaster.c:1414

#27 0x000055779cf5805f in main (argc=3, argv=0x55779e3308b0) at main.c:209

Regards,

Jeevan Ladhe

On Thu, May 6, 2021 at 7:33 PM Nitin Jadhav <nitinjadhavpostgres@gmail.com> wrote:

Hi,

While reviewing one of the 'Table partitioning' related patches, I found that Postgres does not support multiple column based LIST partitioning. Based on this understanding, I have started working on this feature. I also feel that 'Multi-Column List Partitioning' can be benefited to the Postgres users in future.  

I am attaching the WIP patch for this feature here. It supports 'Multi-Column List Partitioning', however some tasks are still pending. I would like to know your thoughts about this, So that I can continue the work with improvising the current patch. 

Following things are handled in the patch.

1. Syntax

CREATE TABLE table_name (attrs) PARTITION BY LIST(list_of_columns);

Earlier there was no provision to mention multiple columns as part of the 'list_of_columns' clause. Now we can mention the list of columns separated by comma.

CREATE TABLE table_name_p1 PARTITION OF table_name FOR VALUES IN list_of_values.

Whereas list_of_columns can be

a. (value [,...])

b. (value [,...]) [,...]

I would like to list a few examples here for better understanding.

Ex-1:

CREATE TABLE t1(a int) PARTITION BY LIST(a);

CREATE TABLE t1_1 PARTITION OF t1 FOR VALUES IN (1, 2, 10, 5, 7);

Ex-2: 

CREATE TABLE t2(a int, b int) PARTITION BY LIST(a,b);

CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES IN (1, 2), (1, 5), (2, 2),(2, 10);

Please share if any changes are required in the above syntax.

2. Modified transformation logic to support above syntax.

3. Modified the data structures to store the information caused by above syntax. Also modified the searching logic to route the tuple to the appropriate partition.

4. Done a few basic testing and verified CREATE TABLE, INSERT INTO and SELECT are working fine.

Following items are pending and I am working on it.

1. Handling of 'NULL' values.

2. Support multi column case in partition pruning.

3. Add test cases to the regression test suite.

Please share your thoughts.

Thanks & Regards,

Nitin Jadhav