What'd be the downside of a uniqueness check within a trigger for a partitioned table?

Question

I had the following table:

CREATE TABLE transactions
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PARIMARY KEY,
    transaction_id VARCHAR(255) DEFAULT NULL NULL,
    transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
    customer_name VARCHAR(256) DEFAULT NULL NULL,
    transaction_date TIMESTAMP DEFAULT NULL NULL,
    CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id)
);

CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);

and I want to partition it with transaction_date as follows:

CREATE TABLE transactions
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence'),
    transaction_id VARCHAR(255) DEFAULT NULL NULL,
    transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
    customer_name VARCHAR(256) DEFAULT NULL NULL,
    transaction_date TIMESTAMP DEFAULT NULL NULL,
    CONSTRAINT transactions_pkey PRIMARY KEY (id, transaction_date),
    CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id, transaction_date)
) PARTITION BY RANGE (transaction_date);

CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);

with this partitioning transaction_id is not truly unique, but unique per partition, as the unique key has to have the partition key, i.e. transaction_date.

The application using this table is not aware of the partitioning, and uses 23505 SQL state to perform update instead of an insert.

I'm inserting ~300K rows per day, and the current table size is ~30 million rows.

So is the following trigger:

CREATE OR REPLACE FUNCTION transactions_validate() RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    IF EXISTS (SELECT id FROM transactions WHERE transaction_id = NEW.transaction_id AND id <> NEW.id) THEN
        RAISE EXCEPTION USING ERRCODE = '23505';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER transactions_validate_trigger
    BEFORE INSERT OR UPDATE
    ON transactions
    FOR EACH ROW
EXECUTE PROCEDURE transactions_validate();

a bad idea? What'd be it's performance implications?

Answer 1

Try to run the following three statements in two concurrent database sessions:

Session 1:

BEGIN;

INSERT INTO transactions (id, transaction_id, transaction_date)
   VALUES (1, 42, '2020-01-01 00:00:00');

Session 2:

BEGIN;

INSERT INTO transactions (id, transaction_id, transaction_date)
   VALUES (1, 42, '2023-01-01 00:00:00');

COMMIT;

Session 1:

COMMIT;

As long as the two timestamps belong in different partitions, both transactions will succeed and will violate your desired uniqueness condition.

So no, your solution is not good, even if you use deferred constraint triggers, which narrow the window for the race condition. This will only work with the SERIALIZABLE isolation level (which will cause serialization errors that require you to retry transactions) or heavy locking, which is bad for concurrency.

Usually, the way forward is to relax your uniqueness constraints. Otherwise, you will have to pay a noticeable price, as far as performance is concerned.