postgresql 如何在postgres中为PARTITION BY HASH计算分区提醒

jgovgodb  于 2023-04-29  发布在  PostgreSQL
关注(0)|答案(1)|浏览(170)

我想手动计算提醒,以便在分区表中使用upsert。我尝试了以下代码:

create table test_partitioning
(
    test bigint not null
) partition by hash (test);

DO $$
    declare
        _remainder integer ;
    begin
        FOR _remainder IN (select generate_series(1, 10)) LOOP
                EXECUTE 'CREATE TABLE  IF NOT EXISTS  test_partitioning_' || _remainder ||
                        ' PARTITION OF test_partitioning FOR VALUES WITH (modulus 10, remainder ' || (_remainder - 1) || ')';
            END LOOP;
    END $$;

do
$$
    declare
        value bigint ;
        calculated_reminder int;
    begin
        FOR value IN (select generate_series(1, 10)) LOOP
                calculated_reminder = (((hashint8extended(value, 8816678312871386365)::numeric + 5305509591434766563) % 10)::int + 10) % 10;

                if not satisfies_hash_partition('test_partitioning'::regclass, 10, calculated_reminder, value) THEN
                    RAISE 'check failed, value=%, reminder=%', value, calculated_reminder;
                end if;
            end LOOP ;

    end
$$;

值2和3都失败了。我的计算出了什么问题?

mftmpeh8

mftmpeh81#

解决方案如下:

CREATE OR REPLACE FUNCTION unsigned2signed(signed bigint)
RETURNS numeric AS $$
SELECT CASE
           WHEN signed >= 0 THEN signed::numeric
           ELSE 18446744073709551616 + signed
           END
$$ LANGUAGE SQL;

calculated_reminder = ((unsigned2signed(hashint8extended(value, 8816678312871386365)) + 5305509591434766563) % 18446744073709551616) % 10;

顺便说一句,原始解决方案也适用于分区数= x^2(2,4,8,16,...)

相关问题