Re: Declarative partitioning

Hi Amit,

On 20.05.2016 11:37, Amit Langote wrote:
> 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(dot)musin(at)postgrespro(dot)ru