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:
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?