Re: Postgres 11, partitioning with a custom hash function

Thank you David! These helped me create an operator class.
However, there still seems to be a 'off-by-a-fixed-N' difference between
the hash value returned and how PG selects the partition.

http://dpaste.com/381E6CF

Am I overlooking some endianness difference!!??

For this setup, values are always off by 1 - whatever I calculate, pg takes
the "next" partition

For a similar setup of 32 partitions, I get the offset (between expected
and selected) as 3 http://dpaste.com/382NDBG

On Wed, Oct 3, 2018 at 8:42 PM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On 4 October 2018 at 16:22, Harry B <harrysungod(at)gmail(dot)com> wrote:
> > I am still having trouble reconciling what happens under the HASH
> > partitioning!. If I have text column forming the basis of PARTITIONED BY
> > HASH, the HASH value used in the partitioning setup does not seem to
> match
> > to `hashtext()` of that value
>
> It won't match. The hash partition hash is seeded with a special const
> (HASH_PARTITION_SEED) see [1].
>
> You could likely roll your own hash ops. See [2] for an example. This
> can then be used to create a hash partitioned table like [3].
>
> [1]
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/partitioning/partbounds.c#l2056
> [2]
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/insert.sql#l241
> [3]
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/hash_part.sql#l10
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

--
Harry