使用觸發器和並發事務確保表中行組的唯一值

我已經看到了這個問題的類似變體，但沒有任何東西可以解決這個特定的實現，我想知道是否可以使用 sql/plpgsql。將此視為更多的學習練習，因為某些部分可能是人為的。

我在這裡提供了很多細節，但誰能回答這個問題可能並不真正需要它們，所以這裡有一個問題：

對於每個客戶，只希望將一張信用卡設置為預設值，但必須始終存在預設值。這幾乎沒有問題，acc_num並且is_default（null對於該帳號的其他行）都有唯一索引 - 它只是不能確保給定使用者的唯一索引是真的（設置為預設值），並且根據這個答案它不是一個簡單的約束真的可能：約束 - 一個布爾行為真，所有其他行為假。

假設使用者決定刪除（未設置，w/e）預設值，但沒有將另一個設置為預設值。我對所有可能實現這一目標的方式持開放態度（將某種類型的錯誤返回給應用伺服器），但我最感興趣的方式是自動從列表中選擇另一張信用卡作為預設值（即使它是使用者剛剛取消設置的那個），因此甚至不會將錯誤發送回應用程序。

編輯：Arkhena 已經提供了一個解決方法，但我仍然想知道如何通過我提供的設計來解決這個問題。在 postgres 中鎖定表格是否是解決這個問題的唯一方法（使用我提供的設計）？

您將在下面找到架構和一些並發問題。

FWIW，這裡有一些其他相關的問題：

https://stackoverflow.com/questions/34495479/add-constraint-to-make-column-unique-per-group-of-rows/34495621#34495621

限制兩個特定列值同時存在

https://stackoverflow.com/questions/28166915/postgresql-constraint-only-one-row-can-have-flag-set - Mickael 的底部答案可能最接近我的問題，但它沒有處理使用這個競爭條件組件。

範例架構：

CREATE TABLE customers (acc_num text PRIMARY KEY, name text);

CREATE TABLE customers_credit_cards 
(
 id serial primary key, 
 acc_num text references customers (acc_num) not null,
 is_default boolean
);
-- will use this below
INSERT INTO customers (acc_num, name) values ('aaa', 'whatever');

最好用一個例子來看待這一點。

扳機

CREATE OR REPLACE FUNCTION trg_fn_cust_cc_unique_default() RETURNS TRIGGER AS $$
DECLARE
 cust_cc_row customers_credit_cards%rowtype;
 a_default_exists boolean := false;
BEGIN
 -- This only helps for UPDATES, but does not help with inserts as will be shown below.
 PERFORM 1 FROM customers_credit_cards WHERE acc_num = new.acc_num FOR UPDATE;

 IF tg_op = 'INSERT'
 THEN
   -- it's possible that another transaction also sets one to true, but that
   -- doesn't really matter as we only care that ONE is set to default.
   IF new.is_default THEN
     -- set all others to false since this one is set to default
     UPDATE customers_credit_cards SET is_default = FALSE WHERE acc_num = new.acc_num;
   ELSE
     -- need to check to see if any others are true, if so, carry on, otherwise
     -- set this one to true (if anything is inserted or updated after this,
     -- this will be overridden and set to false, so no prob)
     FOR cust_cc_row IN SELECT * FROM customers_credit_cards WHERE acc_num = new.acc_num LOOP
       IF cust_cc_row.is_default THEN
         a_default_exists := TRUE;
       END IF;
     END LOOP;
     IF NOT a_default_exists THEN
       new.is_default = TRUE;
     END IF;
   END IF;
   RETURN new;
 ELSEIF tg_op = 'UPDATE'
 THEN
   -- it's possible that another transaction also sets one to true, but that
   -- doesn't really matter as we only care that ONE is set to default.
   IF new.is_default THEN
     UPDATE customers_credit_cards SET is_default = FALSE WHERE acc_num = old.acc_num;
   -- could check if this one was explicity set to false here and try to select another for that user, but that's beyond the question
   ELSE
     -- it is possible here that another transaction or insert sets one to default, but we cannot know that, so must set something to true.
     WITH cte AS (
       SELECT id
       FROM customers_credit_cards
       WHERE acc_num = old.acc_num
       LIMIT 1
     )
     UPDATE customers_credit_cards
     SET is_default = TRUE
     FROM cte
     WHERE customers_credit_cards.id = cte.id;
   END IF;
   RETURN new;
 ELSEIF tg_op = 'DELETE'
 THEN
 -- handle delete operation as well, but it won't be much different than others and should have enough information for question by now
 ELSE
   RAISE EXCEPTION 'Should not have been called for anything but INSERT, UPDATE, or DELETE';
 END IF;
END;
$$ LANGUAGE plpgsql;

所以說你想做這樣的事情：

-- single transaction insert
/* 
 In this case, you don't know that the next insert will set the is_default value
 to true and the when checking with a trigger you would see no other is_default
 value for that customer ('aaa') as true and therefore need to set the first 
 inserted value to true. This case can pretty easily be handled with a trigger.
 On insert, you simply check for other rows of customer 'aaa' in the credit card 
 table and since there are none you set it to true. Then, when the next one is 
 inserted it can override the previous insert (which was even set to true by 
 the trigger).
*/ 
BEGIN;
INSERT INTO customers_credit_cards (acc_num, is_default) values ('aaa', false);
INSERT INTO customers_credit_cards (acc_num, is_default) values ('aaa', true);
COMMIT;

前面的例子不是什麼大問題。但是，插入的並發事務是一個問題。

一種

--- is executed concurrently with B (below)
BEGIN;
INSERT INTO customers_credit_cards (acc_num, is_default) values ('aaa', true);
COMMIT;

乙

--- executed concurrently with A (above)
BEGIN;
/* 
 Even if is_default is set to false here, the trigger would set it to true, 
 because the trigger will not see the value inserted by transaction A.
*/
INSERT INTO customers_credit_cards (acc_num, is_default) values ('aaa', true);
COMMIT;

A和B同時執行將導致兩張信用卡都被設置為預設值。

這裡有哪些選擇？我唯一的選擇是在修改表時為表設置某種類型的鎖嗎？

也許我完全錯了，但在我看來，如果客戶只有一張預設信用卡，我會將其添加為客戶表中的一列：

CREATE TABLE customers
(
 acc_num text PRIMARY KEY,
 name text,
 default_credit_card integer NOT NULL
);

CREATE TABLE customers_credit_cards 
(
 id serial primary key, 
 acc_num text references customers (acc_num) not null
);

ALTER TABLE customers
 ADD CONSTRAINT ref_credit_card FOREIGN KEY (default_credit_card)
 REFERENCES customers_credit_cards(id);

WITH credit_card AS
(
 INSERT INTO customers_credit_cards (acc_num)
 VALUES ('aaa')
 RETURNING id
)
INSERT INTO customers 
SELECT 'aaa',
 'whatever',
 id
FROM credit_card;

引用自：https://dba.stackexchange.com/questions/227244