POSTGRESQL foreign key like constraint

Question

I have tableA called Order

It has a PK id several columns and a bool column named active and a column tableid. Two orders cant be active at the same time for a tableid that is ensured with a two column unique contraint (&& active=true) that works like an index and finding active orders is pretty fast.
The problem is that there is another table orderitems. I want active to be true unless all orderitems for the order-id are marked as paid=true..
Using serialization transactions this can be achieved i think in payment code by setting an update query if all items are paid. I think that this wont work always.Because if they run concurrently they might both see that there are unpaid items(due to old snapshot) but when commit they would pay all items,but not update active column.(different) .
Adding new items and payment transaction tries to set active=false wont be a problem with serial transactions because one of them would fail..
I think triggers are a solution but i dont know what to do exactly.. Thank you for reading

Right idea....trigger functions would be able to handle this. Is orderitems updated to be set as paid = true? Or is it an insert? use table funks in any case : Postgres documentation... postgresql.org/docs/8.4/static/sql-createtrigger.html — Twelfth
– Twelfth, Commented Dec 12, 2011 at 18:11
Spell check changed funcs to funks. ha. use table funks = use trigger funcs. — Twelfth
– Twelfth, Commented Dec 12, 2011 at 18:26
The question is: can this be done without triggers? To the OP: could you please add the relevant table definitions, including keys and constraints? That could save some people some typing... — wildplasser
– wildplasser, Commented Dec 13, 2011 at 0:52
@wildplasser In 9.1 using its new true SERIALIZABLE mode I think it might be possible if you're meticulous about always doing a SELECT ... FOR UPDATE on the Order when INSERTing a new order item. You have to do the SELECT ... FOR UPDATE because there's still no predicate locking; it can't know that the newly inserted order item breaks serializability when another transaction is looking at other orderitems with the same order ID. For that reason, I'd recommend doing it in a trigger. — Craig Ringer
– Craig Ringer, Commented Dec 13, 2011 at 0:59
In 9.1 they say that there is predicate locking. Testing with updates worked but i havent tested with inserts — GorillaApe
– GorillaApe, Commented Dec 13, 2011 at 1:02

Craig Ringer · Accepted Answer · 2011-12-13 00:55:45Z

What you'll want to do is add an AFTER UPDATE OR INSERT OR DELETE FOR EACH ROW trigger on orderitems that determines whether Order.active should be changed. You'll have to do a SELECT ... FOR UPDATE on the Order row that owns those orderitems otherwise you'll risk concurrent runs of the trigger racing against each other and doing out-of-order updates.

Presuming orderitems has a field order_id that is your foreign key reference to Order.id, try something like the (untested, general example only) code following:

CREATE OR REPLACE FUNCTION orderitems_order_active_trigger() RETURNS trigger AS $$
DECLARE
  _old_active BOOLEAN;
  _new_active BOOLEAN;
  _order_id INTEGER;
BEGIN
  IF tg_op = 'INSERT' THEN
    _order_id = NEW.order_id;
  ELIF tg_op = 'UPDATE' THEN
    _order_id = NEW.order_id;
  ELIF tg_op = 'DELETE' THEN
    _order_id = OLD.order_id;
  ELSE
    RAISE EXCEPTION 'Unexpected trigger operation %',tg_op;
  END IF;
  -- Lock against concurrent trigger runs and other changes in the parent record while
  -- obtaining the current value of `active`
  SELECT INTO _old_active Order.active FROM Order WHERE Order.id = _order_id FOR UPDATE;
  -- Now figure out whether the order should be active. We'll say that if there are
  -- more than zero unpaid order items found we'll treat it as active.
  _new_active = EXISTS(SELECT 1 FROM orderitems WHERE orderitems.order_id = _order_id AND orderitems.paid='f');
  -- If the active state has flipped, update the order.
  IF _old_active IS DISTINCT FROM _new_active THEN
    UPDATE Order SET active = _new_active WHERE Order.id = _order_id;
  END IF;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER orderitems_ensure_active_correct AFTER INSERT OR UPDATE OR DELETE 
ON orderitems FOR EACH ROW EXECUTE PROCEDURE orderitems_order_active_trigger();

Collectives™ on Stack Overflow

POSTGRESQL foreign key like constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related