Elements inside a container: How to perform atomic operations on them?

Question

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes.

1. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null).
2. Each element of the box MUST have the same color of the other.

To check (and also to ensure) the last condition I use a trigger.

/*
If I try to INSERT (OR UPDATE) an item in a box
containing elements of different color, it raises an exception.
*/
BEGIN
   IF  (
         SELECT color FROM tshirt WHERE tshirt.box_id = NEW.box_id
         UNION SELECT color FROM jeans WHERE jeans.box_id = NEW.box_id
         UNION SELECT color FROM shoes WHERE shoes.box_id = NEW.box_id
       ) <> NEW.color THEN

          RAISE EXCEPTION 'Error..';
          RETURN NULL;

    END IF;
END;

Now, let's suppose I want to change the color from 'Blue' to 'Red' for any item inside a Box (let's say box_id = 1):

UPDATE Tshirts SET color = 'Red' WHERE box_id = 1;
UPDATE Jeans SET color = 'Red' WHERE box_id = 1;
UPDATE Shoes SET color = 'Red' WHERE box_id = 1;

This code will fail because of the trigger.

The only way I can change the color of each item in the box is:

1. Remove all items from the box (setting their box_id to null).
2. Change the color of all the elements.
3. Put all the elements inside the box (setting back their box_id).

Is there any way (through modeling or triggers) to avoid this tricky situation? It would be great if I could change the elements' color atomically.

Thank in advance and sorry for my bad english.

Answer 1

I'd suggest adjusting your model and doing away with the trigger altogether. While the example is somewhat mind-bending, your data model contains a duplicated dependency which serves as the root of your update anomaly problem.

T-shirts ( T ), Jeans ( J ) and Shoes ( S ) can be placed inside a Box ( B ) and they can have a Color ( C ).

Each Element ( E ) of the Box ( B ) MUST have the same Color ( C ) of the other.

Okay, so T → B, T → C, B → C. That is, if we know the T-shirt, we know the Box, if we know the T-shirt, we know the Color, and if we know the Box, we know the Color. Since Color is dependent on Box and Box is dependent on T-shirt, while perhaps not particularly intuitive, Color cannot be directly dependent on T-shirt since it is already dependent through a transitive dependency. That is to say, if we know the T-shirt, we cannot know the Color ( the Color is NULL ), since we must first know the Box.

The existing trigger is actually attempting to compensate for a potential update anomaly a 3NF+ data model would mitigate. If the color of a T-shirt is known at creation time, such a T-shirt could ( and likely should ) be immediately added to a box of the same color. In this way, the need to change the color of all items in a box is performed by merely updating the color attribute of the box itself.

Also, unless there's good reasons to split T-shirt, Jeans and Shoes into their own tables, it might be very much preferable to just consolidate those to an Element table with and ElementType lookup attribute, but much like @dezso, I suspect this is not even kind of really about t-shirt, jean and shoe colors as they are arranged in various boxes.

Answer 2

The specific problem of having the trigger consider a group of related changes rather than independant updates could be solved by :

• declaring the trigger to being a constraint trigger deferred at COMMIT time.

   CREATE CONSTRAINT TRIGGER trigger_name
    AFTER INSERT OR UPDATE ON table_name
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE PROCEDURE check_colors();
  

• and enclosing the set of changes inside a transaction (BEGIN/COMMIT pair)

   BEGIN;
   UPDATE Tshirts SET color = 'Red' WHERE box_id = 1;
   UPDATE Jeans SET color = 'Red' WHERE box_id = 1;
   UPDATE Shoes SET color = 'Red' WHERE box_id = 1;
   COMMIT;
  

Still, concurrent transactions should be blocked by the program to work on the same "boxes", otherwise a trigger-based check could let incoherencies pass. This is because the trigger can't see the potential changes of other sessions that would not have yet committed when it runs.