> 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 <nitinjadhavpostg...@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 > > > > > > >