Postgres Pro
Downloads
Home   >   mailing lists

Re: Postgres 11, partitioning with a custom hash function - Mailing list pgsql-general

From Harry B
Subject Re: Postgres 11, partitioning with a custom hash function
Date
Msg-id CAMG7=yUEfbac1iGaomyVLf4=qfz2v2iXA4kY=TT4nN+GsCkyaw@mail.gmail.com
Whole thread Raw
In response to Re: Postgres 11, partitioning with a custom hash function  (David Rowley <david.rowley@2ndquadrant.com>)
Responses Re: Postgres 11, partitioning with a custom hash function
List pgsql-general
Tree view

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.


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@2ndquadrant.com> wrote:
On 4 October 2018 at 16:22, Harry B <harrysungod@gmail.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

pgsql-general by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: We are facing "PANIC: could not read from control file:Successerror while starting the database.
Next
From: David Rowley
Date:
Subject: Re: Postgres 11, partitioning with a custom hash function