Ensuring unique value for group of rows in table with trigger and concurrent transactions

Question

I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a learning exercise, because some parts could be contrived.

I've provided a lot of details here, but whoever can answer this may not really need them, so here's the question:

For each customer it is desired to only have ONE credit card set as the default, but there must always be a default. This is ALMOST no problem with an unique index on both acc_num and is_default(with null for the other rows of that account number) -- it just does not ensure that one is true (set to default) for a given user and according to this answer it's not really possible with a simple constraint: Constraint - one boolean row is true, all other rows false.

Assume the user decides to remove (unset, w/e) the default, but does not set another as default. I'm open to all ways this may be achieved (throwing some type of error back to app server), but I'm most interested in doing it in a way that automatically selects another credit card from the list as the default (even if it's the one the user just unset) and, thus, does not even send an error back to the app.

EDIT: Arkhena already provided a workaround, but i'm still wondering how this would be solved with the design I provided. Is locking the table to only sure way to solve this (with the design I provided) in postgres?

Below you'll find a schema and some concurrency issues.

FWIW, here are some other related questions:

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

Restrict two specific column values from existing at the same time

https://stackoverflow.com/questions/28166915/postgresql-constraint-only-one-row-can-have-flag-set -- the bottom answer here by Mickael is probably the closest to my question, but it does not deal with this race-condition component.

Example schema:

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');

This is best viewed with an example.

TRIGGER

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;

So say you want to do something like this:

-- 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;

The previous example wasn't much of a problem. Concurrent transactions for inserts are a problem, though.

A

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

B

--- 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 and B running concurrently will result in both credit cards being set as default.

What are the options here? Is my only option here to setup some type of lock for the table when modifying it?

Answer 1

Maybe I'm totally wrong, but it seems to me that if a customer has one and only one default credit card, I'll add that as a column in the customers tables :

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;

• HelloWorld: Hey Arkhena, I appreciate the response. What you're suggesting is a good idea actually. I'm still wondering if locking the table is the only way to handle exact problem I proposed earlier (just more of a learning experience for me) -- I believe it will be, but i'm hoping someone with more postgres knowledge will be able to weigh in on it. Thanks!
• Arkhena: If you really want to do it the way you tried, I think you'll need the serializable isolation level to ensure you can perform only one transaction at a time.You can find documentation about it here : postgresql.org/docs/current/transaction-iso.html
• HelloWorld: Hey Arkhena, thanks for taking your time. I've been playing around with the various concurrency modes in postgres and I think serializable or using some type of explicit locking is the only way to do it -- I mean it had to be some type of locking if it involved concurrency with the setting, but was just wondering if any others had some good ideas and less ugly implementations. I'll mark yours as accepted since it's a nice solution to the problem and avoids complicated triggers.