我有下表:
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);
我想将其分区transaction_date
如下:
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);
这种分区transaction_id
并不是真正唯一的,而是每个分区唯一的,因为唯一键必须具有分区键,即transaction_date
。
使用此表的应用程序不知道分区,并使用 23505 SQL 状态执行更新而不是插入。
我每天插入约 30 万行,当前表大小约为 3000 万行。
以下触发器也是如此:
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();
一个坏主意?它对性能有何影响?
尝试在两个并发数据库会话中运行以下三个语句:
第一节:
第二节:
第一节:
只要两个时间戳属于不同的分区,两个事务都会成功,并且会违反您所需的唯一性条件。
所以不,即使您使用延迟约束触发器,您的解决方案也不好,这会缩小竞争条件的窗口。这仅适用于
SERIALIZABLE
隔离级别(这将导致序列化错误,需要您重试事务)或重度锁定,这对并发性不利。通常,前进的道路是放松你的独特性限制。否则,就性能而言,您将不得不付出显着的代价。