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

concurrencydatabase-designlockingpostgresqltrigger

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.

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

Best Answer

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;

Related Solutions

Mysql – Disable trigger for just one table

Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

run SET @TRIGGER_DISABLED = 1;
do your data maintenance
run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN 
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN 
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN 
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        END IF; 
    ELSEIF (NEW.status != 3) THEN   
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN 
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    END IF; 
    END IF; 
END

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`     
    FOR EACH ROW     
    BEGIN     
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);     
        END IF;
    END;

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.

Mysql – how should i do this trigger

I would really advise to not have a separate "totals" table that you have to maintain. There would be many downsides to doing so:

under high volume, you might get transactional locks
it's possible that your totals get "out of sync" with the source table. If/when this happens, you'd have to take an outage or block insertion of new votes while you're trying to get the totals correct.

Anyhow, I believe you can get a high-performance, real-time vote totals with simple code, without storing separately any totals information and therefore without needing a trigger. You also won't have to worry about any INSERT vs. UPDATE logic.

Make sure your vote_question table has an index (nonunique) on the question_id column. This will ensure performance for real-time.

Here's a totals query (all questions, summarized):

SELECT question_id
   , SUM((vote_question_type)='1') question_total_plus
   , SUM((vote_question_type)='-1') question_total_minus
   , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;

If you don't want to have to put that code in the application side, then no problem, just create it as a database view:

CREATE VIEW vote_question_totals
  AS
SELECT question_id
   , SUM((vote_question_type)='1') question_total_plus
   , SUM((vote_question_type)='-1') question_total_minus
   , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;

Then from your application/client side, you can reference the view just as though it were a table (and provide a question_id).

SELECT question_id, question_total_plus, question_total_minus, question_total_mixed
  FROM vote_question_totals
  WHERE question_id = '???' ;

Best Answer

Related Solutions

Mysql – Disable trigger for just one table

Mysql – how should i do this trigger

Related Question