Re: Declarative partitioning

Hi Amit,

Perhaps you're already aware but may I also suggest looking at how clauses
are matched to indexes?  For example, consider how
match_clauses_to_index() in src/backend/optimizer/path/indxpath.c works.

Thanks, I'll take a closer look at it.

Moreover, instead of pruning partitions in planner prep phase, might it
not be better to do that when considering paths for the (partitioned) rel?IOW, instead of looking at parse->jointree, we should rather be working
with rel->baserestrictinfo.  Although, that would require some revisions
to how append_rel_list, simple_rel_list, etc. are constructed and
manipulated in a given planner invocation.  Maybe it's time for that...
Again, you may have already considered these things.

Yes, you're right, this is how we did it in pg_pathman extension. But for this patch it requires further consideration and I'll do it in future!

Could you try with the attached updated set of patches?  I changed
partition descriptor relcache code to eliminate excessive copying in
previous versions.

Thanks,
Amit

I tried your new patch and got following results, which are quite close to the ones using pointer to PartitionDesc structure (TPS):

# of partitions | single row | single partition
----------------+------------+------------------
            100 |       3014 |             1024
           1000 |       2964 |             1001
           2000 |       2874 |             1000

However I've encountered a problem which is that postgres crashes occasionally while creating partitions. Here is function that reproduces this behaviour:

CREATE OR REPLACE FUNCTION fail()
 RETURNS void
 LANGUAGE plpgsql
AS $$
BEGIN
DROP TABLE IF EXISTS abc CASCADE;
CREATE TABLE abc (id SERIAL NOT NULL, a INT, dt TIMESTAMP) PARTITION BY RANGE (a);
CREATE INDEX ON abc (a);
CREATE TABLE abc_0 PARTITION OF abc FOR VALUES START (0) END (1000);
CREATE TABLE abc_1 PARTITION OF abc FOR VALUES START (1000) END (2000);
END
$$;

SELECT fail();

It happens not every time but quite often. It doesn't happen if I execute this commands one by one in psql. Backtrace:

#0  range_overlaps_existing_partition (key=0x7f1097504410, range_spec=0x1d0f400, pdesc=0x1d32200, with=0x7ffe437ead00) at partition.c:747
#1  0x000000000054c2a5 in StorePartitionBound (relid=245775, parentId=245770, bound=0x1d0f400) at partition.c:578
#2  0x000000000061bfc4 in DefineRelation (stmt=0x1d0dfe0, relkind=114 'r', ownerId=10, typaddress=0x0) at tablecmds.c:739
#3  0x00000000007f4473 in ProcessUtilitySlow (parsetree=0x1d1a150, queryString=0x1d1d940 "CREATE TABLE abc_0 PARTITION OF abc FOR VALUES START (0) END (1000)", context=PROCESS_UTILITY_QUERY, params=0x0, dest=0xdb5ca0 <spi_printtupDR>, completionTag=0x7ffe437eb500 "")
    at utility.c:983
#4  0x00000000007f425e in standard_ProcessUtility (parsetree=0x1d1a150, queryString=0x1d1d940 "CREATE TABLE abc_0 PARTITION OF abc FOR VALUES START (0) END (1000)", context=PROCESS_UTILITY_QUERY, params=0x0, dest=0xdb5ca0 <spi_printtupDR>,
    completionTag=0x7ffe437eb500 "") at utility.c:907
#5  0x00000000007f3354 in ProcessUtility (parsetree=0x1d1a150, queryString=0x1d1d940 "CREATE TABLE abc_0 PARTITION OF abc FOR VALUES START (0) END (1000)", context=PROCESS_UTILITY_QUERY, params=0x0, dest=0xdb5ca0 <spi_printtupDR>, completionTag=0x7ffe437eb500 "")
    at utility.c:336
#6  0x000000000069f8b2 in _SPI_execute_plan (plan=0x1d19cf0, paramLI=0x0, snapshot=0x0, crosscheck_snapshot=0x0, read_only=0 '\000', fire_triggers=1 '\001', tcount=0) at spi.c:2200
#7  0x000000000069c735 in SPI_execute_plan_with_paramlist (plan=0x1d19cf0, params=0x0, read_only=0 '\000', tcount=0) at spi.c:450
#8  0x00007f108cc6266f in exec_stmt_execsql (estate=0x7ffe437eb8e0, stmt=0x1d05318) at pl_exec.c:3517
#9  0x00007f108cc5e5fc in exec_stmt (estate=0x7ffe437eb8e0, stmt=0x1d05318) at pl_exec.c:1503
#10 0x00007f108cc5e318 in exec_stmts (estate=0x7ffe437eb8e0, stmts=0x1d04c98) at pl_exec.c:1398
#11 0x00007f108cc5e1af in exec_stmt_block (estate=0x7ffe437eb8e0, block=0x1d055e0) at pl_exec.c:1336
#12 0x00007f108cc5c35d in plpgsql_exec_function (func=0x1cc2a90, fcinfo=0x1cf7f50, simple_eval_estate=0x0) at pl_exec.c:434
...

Thanks

-- 
Ildar Musin
i.musin@postgrespro.ru