0

On Postgres 11 have this simple table and partition definition

ap=# \d+ hp
                              Partitioned table "public.hp"
 Column |  Type  | Collation | Nullable | Default | Storage | Stats target | Description 
--------+--------+-----------+----------+---------+---------+--------------+-------------
 foo    | bigint |           |          |         | plain   |              | 
Partition key: HASH (foo)
Partitions: hp_0 FOR VALUES WITH (modulus 10, remainder 0),
            hp_1 FOR VALUES WITH (modulus 10, remainder 1),
            hp_2 FOR VALUES WITH (modulus 10, remainder 2),
            hp_3 FOR VALUES WITH (modulus 10, remainder 3),
            hp_4 FOR VALUES WITH (modulus 10, remainder 4),
            hp_5 FOR VALUES WITH (modulus 10, remainder 5),
            hp_6 FOR VALUES WITH (modulus 10, remainder 6),
            hp_7 FOR VALUES WITH (modulus 10, remainder 7),
            hp_8 FOR VALUES WITH (modulus 10, remainder 8),
            hp_9 FOR VALUES WITH (modulus 10, remainder 9)

Inserting into this table a value of 14 and checking on the partition it lands in

insert into hp values(14);
SELECT tableoid::regclass,* FROM hp;
 tableoid | foo 
----------+-----
 hp_0     |  14
(1 row)

Not able to explain how 14 lands in partition hp_0. From what I gather the hash function for bigint is hashint8

ap=# select hashint8(14);
  hashint8   
-------------
 -1018458207
(1 row)

-1018458207 mod 10 would be -7

-1018458207 converted to unsigned int and mod 10 ==> 3276509089 mod 10 = 9

So unable to see how it lands in hp_0 which is for the remainder 0.

Anjani
  • 1
  • 2
  • https://stackoverflow.com/questions/61409008/test-hash-function-for-postgres-table-partitioning –  Jul 29 '21 at 17:54
  • Why do you care in which partition it winds up? –  Jul 29 '21 at 17:55
  • The correct answer is given at https://stackoverflow.com/questions/67181854/how-to-know-which-partition-will-be-used-in-postgres-hash-partitioning. Adapting that to your datatype would yield `SELECT (hashint4extended(14, 8816678312871386365)::numeric + 5305509591434766563) % 10;` which gives 0. – jjanes Jul 29 '21 at 18:34
  • Thanks @jjanes What if the value is negative? For example ```SELECT (hashint4extended(2107, 8816678312871386365)::numeric + 5305509591434766563) % 10000;``` – Anjani Jul 30 '21 at 04:00
  • @Anjani, maybe I over-estimated the level of correctness of that answer. – jjanes Jul 30 '21 at 16:09

0 Answers0