Re: Declarative partitioning

Hi Amit,<br /><br /><div class="moz-cite-prefix">On 20.05.2016 11:37, Amit Langote wrote:<br /></div><blockquote
cite="mid:573ECCC3.7080104@lab.ntt.co.jp"type="cite"><pre wrap="">
 
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.
</pre></blockquote> Thanks, I'll take a closer look at it.<br /><blockquote cite="mid:573ECCC3.7080104@lab.ntt.co.jp"
type="cite"><prewrap="">
 
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.

</pre></blockquote> Yes, you're right, this is how we did it in pg_pathman extension. But for this patch it requires
furtherconsideration and I'll do it in future!<br /><blockquote cite="mid:573ECCC3.7080104@lab.ntt.co.jp"
type="cite"><prewrap="">
 
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
</pre></blockquote> I tried your new patch and got following results, which are quite close to the ones using pointer
toPartitionDesc structure (TPS):<br /><br /><font face="Courier New, Courier, monospace"># of partitions | single row |
singlepartition<br /> ----------------+------------+------------------<br />             100 |       3014 |            
1024<br/>            1000 |       2964 |             1001<br />            2000 |       2874 |            
1000</font><br/><br /> However I've encountered a problem which is that postgres crashes occasionally while creating
partitions.Here is function that reproduces this behaviour:<br /><br /> CREATE OR REPLACE FUNCTION fail()<br />
 RETURNSvoid<br />  LANGUAGE plpgsql<br /> AS $$<br /> BEGIN<br /> DROP TABLE IF EXISTS abc CASCADE;<br /> CREATE TABLE
abc(id SERIAL NOT NULL, a INT, dt TIMESTAMP) PARTITION BY RANGE (a);<br /> CREATE INDEX ON abc (a);<br /> CREATE TABLE
abc_0PARTITION OF abc FOR VALUES START (0) END (1000);<br /> CREATE TABLE abc_1 PARTITION OF abc FOR VALUES START
(1000)END (2000);<br /> END<br /> $$;<br /><br /> SELECT fail();<br /><br /> It happens not every time but quite often.
Itdoesn't happen if I execute this commands one by one in psql. Backtrace:<br /><br /> #0 
range_overlaps_existing_partition(key=0x7f1097504410, range_spec=0x1d0f400, pdesc=0x1d32200, with=0x7ffe437ead00) at
partition.c:747<br/> #1  0x000000000054c2a5 in StorePartitionBound (relid=245775, parentId=245770, bound=0x1d0f400) at
partition.c:578<br/> #2  0x000000000061bfc4 in DefineRelation (stmt=0x1d0dfe0, relkind=114 'r', ownerId=10,
typaddress=0x0)at tablecmds.c:739<br /> #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 "")<br />
   at utility.c:983<br /> #4  0x00000000007f425e in standard_ProcessUtility (parsetree=0x1d1a150, queryString=0x1d1d940
"CREATETABLE abc_0 PARTITION OF abc FOR VALUES START (0) END (1000)", context=PROCESS_UTILITY_QUERY, params=0x0,
dest=0xdb5ca0<spi_printtupDR>, <br />     completionTag=0x7ffe437eb500 "") at utility.c:907<br /> #5 
0x00000000007f3354in ProcessUtility (parsetree=0x1d1a150, queryString=0x1d1d940 "CREATE TABLE abc_0 PARTITION OF abc
FORVALUES START (0) END (1000)", context=PROCESS_UTILITY_QUERY, params=0x0, dest=0xdb5ca0 <spi_printtupDR>,
completionTag=0x7ffe437eb500"")<br />     at utility.c:336<br /> #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<br /> #7  0x000000000069c735 in SPI_execute_plan_with_paramlist (plan=0x1d19cf0, params=0x0,
read_only=0'\000', tcount=0) at spi.c:450<br /> #8  0x00007f108cc6266f in exec_stmt_execsql (estate=0x7ffe437eb8e0,
stmt=0x1d05318)at pl_exec.c:3517<br /> #9  0x00007f108cc5e5fc in exec_stmt (estate=0x7ffe437eb8e0, stmt=0x1d05318) at
pl_exec.c:1503<br/> #10 0x00007f108cc5e318 in exec_stmts (estate=0x7ffe437eb8e0, stmts=0x1d04c98) at pl_exec.c:1398<br
/>#11 0x00007f108cc5e1af in exec_stmt_block (estate=0x7ffe437eb8e0, block=0x1d055e0) at pl_exec.c:1336<br /> #12
0x00007f108cc5c35din plpgsql_exec_function (func=0x1cc2a90, fcinfo=0x1cf7f50, simple_eval_estate=0x0) at
pl_exec.c:434<br/> ...<br /><br /> Thanks<br /><pre class="moz-signature" cols="72">-- 
 
Ildar Musin
<a class="moz-txt-link-abbreviated" href="mailto:i.musin@postgrespro.ru">i.musin@postgrespro.ru</a></pre>