On 10 April 2018 at 20:56, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > On 2018/04/10 13:27, Ashutosh Bapat wrote: >> On Mon, Apr 9, 2018 at 8:56 PM, Robert Haas <robertmh...@gmail.com> wrote: >>> 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 TABLE mchash (a int, b text, c jsonb) >>> PARTITION BY HASH (a test_int4_ops, b test_text_ops); > > Thanks for the idea. I think it makes sense and also agree that alternate > outputs approach is not perfectly reliable and maintainable. > >> +1. > > Attached find a patch that rewrites hash partition pruning tests that > away. It creates two hash operator classes, one for int4 and another for > text type and uses them to create hash partitioned table to be used in the > tests, like done in the existing tests in hash_part.sql. Since that makes > tests (hopefully) reliably return the same result always, I no longer see > the need to keep them in a separate partition_prune_hash.sql. The > reasoning behind having the separate file was to keep the alternative > output file small as David explained in [1]. > [1] > https://www.postgresql.org/message-id/CAKJS1f-SON_hAekqoV4_WQwJBtJ_rvvSe68jRNhuYcXqQ8PoQg%40mail.gmail.com
I had a quick look, but I'm still confused about why a function like hash_uint32_extended() is susceptible to varying results depending on CPU endianness but hash_combine64 is not. Apart from that confusion, looking at the patch: +CREATE OR REPLACE FUNCTION pp_hashint4_noop(int4, int8) RETURNS int8 AS +$$SELECT coalesce($1)::int8$$ LANGUAGE sql IMMUTABLE STRICT; +CREATE OPERATOR CLASS pp_test_int4_ops FOR TYPE int4 USING HASH AS +OPERATOR 1 = , FUNCTION 2 pp_hashint4_noop(int4, int8); +CREATE OR REPLACE FUNCTION pp_hashtext_length(text, int8) RETURNS int8 AS +$$SELECT length(coalesce($1))::int8$$ LANGUAGE sql IMMUTABLE STRICT; Why coalesce here? Maybe I've not thought of something, but coalesce only seems useful to me if there's > 1 argument. Plus the function is strict, so not sure it's really doing even if you added a default. I know this one was there before, but I only just noticed it: +-- pruning should work if non-null values are provided for all the keys +explain (costs off) select * from hp where a is null and b is null; The comment is a bit misleading given the first test below it is testing for nulls. Maybe it can be changed to +-- pruning should work if values or is null clauses are provided for all partition keys. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
