However, there still seems to be a 'off-by-a-fixed-N' difference between the hash value returned and how PG selects the partition.
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.rowley@xxxxxxxxxxxxxxx> wrote:
On 4 October 2018 at 16:22, Harry B <harrysungod@xxxxxxxxx> 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
