Re: [HACKERS] Runtime Partition Pruning

Hi David,

On 03/01/2018 08:29 PM, David Rowley wrote:
>> 0004 fails "make check-world" due to
>>
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 670; 1259 49954 TABLE
>> boolp_f jpedersen
>> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at
>> or near "false"
>> LINE 24: ..." ATTACH PARTITION "public"."boolp_f" FOR VALUES IN (false);
> 
> The tests seem to have stumbled on a pg_dump bug which causes it to
> produce syntax that's not valid (currently)
> 
> I should be able to stop my patch failing the test by dropping that
> table, which I should have been doing anyway.
>

I've added that thread to the Open Items list.

> Thanks for the review and in advance for the future review.
> 
> I'll delay releasing a new patch as there's some discussion over on
> the faster partition pruning thread which affects this too [1]
> 
> [1] https://www.postgresql.org/message-id/CA+Tgmoa4D1c4roj7L8cx8gkkeBWAZD=MTcXKxTwBnsLRHD3rig@mail.gmail.com
> 

Sure, 0003-0005 depends on that thread. 0002 is refactoring so that one 
is ready.

In 0004 should we add a HASH based test case,

-- test.sql --
-- verify pruning in hash partitions
create table hashp (a int primary key, b int) partition by hash (a);
create table hashp_0 partition of hashp for values with (modulus 2, 
remainder 0);
create table hashp_1 partition of hashp for values with (modulus 2, 
remainder 1);
insert into hashp values (0,0), (1,1), (2,2), (3,3);
prepare q1 (int) as select * from hashp where a = $1;
execute q1 (1);
execute q1 (1);
execute q1 (1);
execute q1 (1);
execute q1 (1);
explain (analyze, costs off, summary off, timing off)  execute q1 (1);
explain (analyze, costs off, summary off, timing off)  execute q1 (3);
deallocate q1;
drop table hashp;
-- test.sql --

Also, should 0004 consider the "Parallel Append" case, aka

-- parallel.sql --
CREATE TABLE t1 (
     a integer NOT NULL,
     b integer NOT NULL
) PARTITION BY HASH (b);

CREATE TABLE t1_p00 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 0);
CREATE TABLE t1_p01 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 1);
CREATE TABLE t1_p02 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 2);
CREATE TABLE t1_p03 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 3);
INSERT INTO t1 (SELECT i, i FROM generate_series(1, 1000000) AS i);
PREPARE q1 (int) AS SELECT * FROM t1 WHERE a = $1;
EXECUTE q1 (5432);
EXECUTE q1 (5432);
EXECUTE q1 (5432);
EXECUTE q1 (5432);
EXECUTE q1 (5432);
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)  EXECUTE q1 (5432);
DEALLOCATE q1;
DROP TABLE t1;
-- parallel.sql --

Best regards,
  Jesper