On Tue, Apr 10, 2018 at 5:32 PM, David Rowley <david.row...@2ndquadrant.com> wrote: > 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 think Amit Langote wanted to write coalesce($1, $2), $2 being the seed for hash function. See how hash operator class functions are defined in sql/insert.sql. May be we should just use the same functions or even the same tables. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
