PostgreSQL中256位区块链哈希拆分后的冲突检测与更新问题

Alright, let's break down how to implement your required logic for splitting 256-bit blockchain hashes into a 128-bit primary key prefix (hash_id) and the remaining bytes (hash_rest), while handling two conflict scenarios: raising an error if the prefix collides with a different full hash, and updating the timestamp if the full hash matches an existing entry.

First: Define Your Table Structure #

Start by creating the table to store your split hashes. We'll include a timestamp column that defaults to the current time for new entries:

CREATE TABLE hash_store (
    hash_id BYTEA NOT NULL PRIMARY KEY, -- 16-byte (128-bit) prefix
    hash_rest BYTEA NOT NULL, -- Remaining 16-byte (128-bit) of the 256-bit hash
    last_updated TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Solution 1: PL/pgSQL Function for Explicit Control #

For clear, readable logic that handles both conflict cases explicitly, wrap the insert/update logic in a PL/pgSQL function. This gives you full control over error handling and updates:

CREATE OR REPLACE FUNCTION upsert_hash(p_hash_id BYTEA, p_hash_rest BYTEA)
RETURNS VOID AS $$
DECLARE
    existing_rest BYTEA;
BEGIN
    -- Check if the hash_id already exists
    SELECT hash_rest INTO existing_rest FROM hash_store WHERE hash_id = p_hash_id;
    
    IF FOUND THEN
        -- If exists, verify the hash_rest matches
        IF existing_rest != p_hash_rest THEN
            RAISE EXCEPTION 'Hash collision detected: hash_id % has conflicting hash_rest values', p_hash_id;
        ELSE
            -- Update timestamp if the full hash matches
            UPDATE hash_store SET last_updated = NOW() WHERE hash_id = p_hash_id;
        END IF;
    ELSE
        -- Insert new entry if no conflict
        INSERT INTO hash_store (hash_id, hash_rest) VALUES (p_hash_id, p_hash_rest);
    END IF;
END;
$$ LANGUAGE plpgsql;

To use this function:

SELECT upsert_hash('\xyour16byteprefix'::bytea, '\xyourremaining16bytes'::bytea);

Pros: Straightforward logic, easy to modify if you need to add additional checks later.
Cons: Requires calling a function instead of a raw INSERT statement.

Solution 2: Single INSERT Statement with Conflict Handling #

If you prefer a single atomic INSERT statement (which is often better for concurrency), you can use PostgreSQL's ON CONFLICT clause combined with a helper function to raise exceptions when hash_rest mismatches.

First, create a helper function that throws an exception (since you can't directly use RAISE EXCEPTION in the SET clause of ON CONFLICT):

CREATE OR REPLACE FUNCTION raise_hash_conflict(p_hash_id BYTEA) RETURNS BYTEA AS $$
BEGIN
    RAISE EXCEPTION 'Hash collision detected for hash_id: %', p_hash_id;
END;
$$ LANGUAGE plpgsql;

Then use this in your INSERT statement:

INSERT INTO hash_store (hash_id, hash_rest)
VALUES ('\xyour16byteprefix'::bytea, '\xyourremaining16bytes'::bytea)
ON CONFLICT (hash_id) DO UPDATE
SET 
    last_updated = NOW(),
    -- Only keep the existing hash_rest if it matches; otherwise throw an error
    hash_rest = CASE 
        WHEN hash_store.hash_rest = EXCLUDED.hash_rest THEN hash_store.hash_rest
        ELSE raise_hash_conflict(hash_store.hash_id)
    END;

How this works:

• If the hash_id doesn't exist: The INSERT proceeds normally, with last_updated set to the current time.
• If the hash_id exists and hash_rest matches: The last_updated timestamp is refreshed.
• If the hash_id exists but hash_rest differs: The CASE clause calls raise_hash_conflict, which throws an exception and aborts the operation.

Pros: Atomic single statement, better for high-concurrency environments.
Cons: Relies on a helper function, which adds a small layer of indirection.

Key Notes for Your Use Case #

• Uniqueness Detection: Both solutions will reliably throw an error if a 128-bit prefix collides with a different full hash, letting you catch those rare cases (though statistically unlikely for cryptographic hashes like SHA-256).
• Storage Efficiency: By using hash_id as the primary key, other tables can reference this table with a 16-byte foreign key, saving significant storage compared to storing the full 32-byte hash everywhere.

内容的提问来源于stack exchange，提问作者dpc.pw