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;