INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints

DROP TABLE quuux;

-- check validation when attaching hash partitions

-- The default hash functions as they exist today aren't portable; they can

-- return different results on different machines. Depending upon how the

-- values are hashed, the row may map to different partitions, which result in

-- regression failure. To avoid this, let's create a non-default hash function

-- that just returns the input value unchanged.

CREATE OR REPLACE FUNCTION dummy_hashint4(a int4, seed int8) RETURNS int8 AS

$$ BEGIN RETURN (a + 1 + seed); END; $$ LANGUAGE 'plpgsql' IMMUTABLE;

CREATE OPERATOR CLASS custom_opclass FOR TYPE int4 USING HASH AS

OPERATOR 1 = , FUNCTION 2 dummy_hashint4(int4, int8);

-- Use hand-rolled hash functions and operator class to get predictable result

-- on different matchines. part_test_int4_ops is defined in insert.sql.

-- check that the new partition won't overlap with an existing partition

CREATE TABLE hash_parted (

a int,

b int

) PARTITION BY HASH (acustom_opclass);

) PARTITION BY HASH (apart_test_int4_ops);

CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE fail_part (LIKE hpart_1);

ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4);

DROP TABLE list_parted, list_parted2, range_parted;

DROP TABLE fail_def_part;

DROP TABLE hash_parted;

DROP OPERATOR CLASS custom_opclass USING HASH;

DROP FUNCTION dummy_hashint4(a int4, seed int8);

-- more tests for certain multi-level partitioning scenarios

create table p (a int, b int) partition by range (a, b);

create table p1 (b int, a int not null) partition by range (b);

--

-- Hash partitioning.

--

CREATE OR REPLACE FUNCTION hashint4_noop(int4, int8) RETURNS int8 AS

$$SELECT coalesce($1,0)::int8$$ LANGUAGE sql IMMUTABLE;

CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING HASH AS

OPERATOR 1 = , FUNCTION 2 hashint4_noop(int4, int8);

CREATE OR REPLACE FUNCTION hashtext_length(text, int8) RETURNS int8 AS

$$SELECT length(coalesce($1,''))::int8$$ LANGUAGE sql IMMUTABLE;

CREATE OPERATOR CLASS test_text_ops FOR TYPE text USING HASH AS

OPERATOR 1 = , FUNCTION 2 hashtext_length(text, int8);

-- Use hand-rolled hash functions and operator classes to get predictable

-- result on different matchines. See the definitions of

-- part_part_test_int4_ops and part_test_text_ops in insert.sql.

CREATE TABLE mchash (a int, b text, c jsonb)

PARTITION BY HASH (atest_int4_ops, btest_text_ops);

PARTITION BY HASH (apart_test_int4_ops, bpart_test_text_ops);

CREATE TABLE mchash1

PARTITION OF mchash FOR VALUES WITH (MODULUS 4, REMAINDER 0);

-- invalid OID, no such table

(1 row)

-- ok, should be true

SELECT satisfies_hash_partition('mchash'::regclass, 4, 0,1, ''::text);

SELECT satisfies_hash_partition('mchash'::regclass, 4, 0,2, ''::text);

satisfies_hash_partition

--------------------------

t

ERROR: column 2 of the partition key has type "text", but supplied value is of type "integer"

-- multiple partitioning columns of the same type

CREATE TABLE mcinthash (a int, b int, c jsonb)

PARTITION BY HASH (atest_int4_ops, btest_int4_ops);

PARTITION BY HASH (apart_test_int4_ops, bpart_test_int4_ops);

-- now variadic should work, should be false

SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,

variadic array[0, 0]);

-- should be true

SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,

variadic array[1, 0]);

variadic array[0, 1]);

satisfies_hash_partition

--------------------------

t

-- cleanup

DROP TABLE mchash;

DROP TABLE mcinthash;

DROP OPERATOR CLASS test_text_ops USING hash;

DROP OPERATOR CLASS test_int4_ops USING hash;

DROP FUNCTION hashint4_noop(int4, int8);

DROP FUNCTION hashtext_length(text, int8);

(9 rows)

-- direct partition inserts should check hash partition bound constraint

-- create custom operator class and hash function, for the same reason

-- explained in alter_table.sql

create or replace function dummy_hashint4(a int4, seed int8) returns int8 as

$$ begin return (a + seed); end; $$ language 'plpgsql' immutable;

create operator class custom_opclass for type int4 using hash as

operator 1 = , function 2 dummy_hashint4(int4, int8);

-- Use hand-rolled hash functions and operator classes to get predictable

-- result on different matchines. The hash function for int4 simply returns

-- the sum of the values passed to it and the one for text returns the length

-- of the non-empty string value passed to it or 0.

create or replace function part_hashint4_noop(value int4, seed int8)

returns int8 as $$

select value + seed;

$$ language sql immutable;

create operator class part_test_int4_ops

for type int4

using hash as

operator 1 =,

function 2 part_hashint4_noop(int4, int8);

create or replace function part_hashtext_length(value text, seed int8)

RETURNS int8 AS $$

select length(coalesce(value, ''))::int8

$$ language sql immutable;

create operator class part_test_text_ops

for type text

using hash as

operator 1 =,

function 2 part_hashtext_length(text, int8);

create table hash_parted (

a int

) partition by hash (acustom_opclass);

) partition by hash (apart_test_int4_ops);

create table hpart0 partition of hash_parted for values with (modulus 4, remainder 0);

create table hpart1 partition of hash_parted for values with (modulus 4, remainder 1);

create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2);

-- cleanup

drop table range_parted, list_parted;

drop table hash_parted;

drop operator class custom_opclass using hash;

drop function dummy_hashint4(a int4, seed int8);

-- test that a default partition added as the first partition accepts any value

-- including null

create table list_parted (a int) partition by list (a);

drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;

--

-- Test Partition pruning for HASH partitioning

--

-- Use hand-rolled hash functions and operator classes to get predictable

-- result on different matchines. See the definitions of

-- part_part_test_int4_ops and part_test_text_ops in insert.sql.

--

create table hp (a int, b text) partition by hash (a part_test_int4_ops, b part_test_text_ops);

create table hp0 partition of hp for values with (modulus 4, remainder 0);

create table hp3 partition of hp for values with (modulus 4, remainder 3);

create table hp1 partition of hp for values with (modulus 4, remainder 1);

create table hp2 partition of hp for values with (modulus 4, remainder 2);

insert into hp values (null, null);

insert into hp values (1, null);

insert into hp values (1, 'xxx');

insert into hp values (null, 'xxx');

insert into hp values (2, 'xxx');

insert into hp values (1, 'abcde');

select tableoid::regclass, * from hp order by 1;

tableoid | a | b

----------+---+-------

hp0 | |

hp0 | 1 | xxx

hp3 | 2 | xxx

hp1 | 1 |

hp2 | | xxx

hp2 | 1 | abcde

(6 rows)

-- partial keys won't prune, nor would non-equality conditions

explain (costs off) select * from hp where a = 1;

QUERY PLAN

-------------------------

Append

-> Seq Scan on hp0

Filter: (a = 1)

-> Seq Scan on hp1

Filter: (a = 1)

-> Seq Scan on hp2

Filter: (a = 1)

-> Seq Scan on hp3

Filter: (a = 1)

(9 rows)

explain (costs off) select * from hp where b = 'xxx';

QUERY PLAN

-----------------------------------

Append

-> Seq Scan on hp0

Filter: (b = 'xxx'::text)

-> Seq Scan on hp1

Filter: (b = 'xxx'::text)

-> Seq Scan on hp2

Filter: (b = 'xxx'::text)

-> Seq Scan on hp3

Filter: (b = 'xxx'::text)

(9 rows)

explain (costs off) select * from hp where a is null;

QUERY PLAN

-----------------------------

Append

-> Seq Scan on hp0

Filter: (a IS NULL)

-> Seq Scan on hp1

Filter: (a IS NULL)

-> Seq Scan on hp2

Filter: (a IS NULL)

-> Seq Scan on hp3

Filter: (a IS NULL)

(9 rows)

explain (costs off) select * from hp where b is null;

QUERY PLAN

-----------------------------

Append

-> Seq Scan on hp0

Filter: (b IS NULL)

-> Seq Scan on hp1

Filter: (b IS NULL)

-> Seq Scan on hp2

Filter: (b IS NULL)

-> Seq Scan on hp3

Filter: (b IS NULL)

(9 rows)

explain (costs off) select * from hp where a < 1 and b = 'xxx';

QUERY PLAN

-------------------------------------------------

Append

-> Seq Scan on hp0

Filter: ((a < 1) AND (b = 'xxx'::text))

-> Seq Scan on hp1

Filter: ((a < 1) AND (b = 'xxx'::text))

-> Seq Scan on hp2

Filter: ((a < 1) AND (b = 'xxx'::text))

-> Seq Scan on hp3

Filter: ((a < 1) AND (b = 'xxx'::text))

(9 rows)

explain (costs off) select * from hp where a <> 1 and b = 'yyy';

QUERY PLAN

--------------------------------------------------

Append

-> Seq Scan on hp0

Filter: ((a <> 1) AND (b = 'yyy'::text))

-> Seq Scan on hp1

Filter: ((a <> 1) AND (b = 'yyy'::text))

-> Seq Scan on hp2

Filter: ((a <> 1) AND (b = 'yyy'::text))

-> Seq Scan on hp3

Filter: ((a <> 1) AND (b = 'yyy'::text))

(9 rows)

explain (costs off) select * from hp where a <> 1 and b <> 'xxx';

QUERY PLAN

---------------------------------------------------

Append

-> Seq Scan on hp0

Filter: ((a <> 1) AND (b <> 'xxx'::text))

-> Seq Scan on hp1

Filter: ((a <> 1) AND (b <> 'xxx'::text))

-> Seq Scan on hp2

Filter: ((a <> 1) AND (b <> 'xxx'::text))

-> Seq Scan on hp3

Filter: ((a <> 1) AND (b <> 'xxx'::text))

(9 rows)

-- pruning should work if either a value or a IS NULL clause is provided for

-- each of the keys

explain (costs off) select * from hp where a is null and b is null;

QUERY PLAN

-----------------------------------------------

Append

-> Seq Scan on hp0

Filter: ((a IS NULL) AND (b IS NULL))

(3 rows)

explain (costs off) select * from hp where a = 1 and b is null;

QUERY PLAN

-------------------------------------------

Append

-> Seq Scan on hp1

Filter: ((b IS NULL) AND (a = 1))

(3 rows)

explain (costs off) select * from hp where a = 1 and b = 'xxx';

QUERY PLAN

-------------------------------------------------

Append

-> Seq Scan on hp0

Filter: ((a = 1) AND (b = 'xxx'::text))

(3 rows)

explain (costs off) select * from hp where a is null and b = 'xxx';

QUERY PLAN

-----------------------------------------------------

Append

-> Seq Scan on hp2

Filter: ((a IS NULL) AND (b = 'xxx'::text))

(3 rows)

explain (costs off) select * from hp where a = 2 and b = 'xxx';

QUERY PLAN

-------------------------------------------------

Append

-> Seq Scan on hp3

Filter: ((a = 2) AND (b = 'xxx'::text))

(3 rows)

explain (costs off) select * from hp where a = 1 and b = 'abcde';

QUERY PLAN

---------------------------------------------------

Append

-> Seq Scan on hp2

Filter: ((a = 1) AND (b = 'abcde'::text))

(3 rows)

explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and b = 'xxx') or (a is null and b is null);

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------

Append

-> Seq Scan on hp0

Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))

-> Seq Scan on hp2

Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))

-> Seq Scan on hp3

Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))

(7 rows)

drop table hp;

--

-- Test runtime partition pruning

--

create table ab (a int not null, b int not null) partition by list (a);