Assuming my logic above is correct, there are two other ways I thought to do it, but both seem considerably more redundant:
(1) I could just get rid of the "quantity" attribute and just insert a record for each product, then do a view that aggregates the products of the same prod_id and cart_id with count().
(2) Every time I add a product I could add a record with a quantity of 0 for each cart in existance, and every time I add a cart I could add a record with a quantity of 0 for each product.
Is there some better solution that I'm missing? It seems like a simple problem, but right now I'm doing the full table lock to be on the safe side. Maybe there's some solution involving check constraints?
Full table lock works but blocks normal selects.
If you can manage to use a uniqueness enforcement then that works too (but you'll have to deal with the errors).
Alternatively you can use a table lock mode that doesn't lock plain selects but locks select for updates and similar stuff (you may still wish to have uniqueness enforcement just in case).
e.g.
pseudosub putrow (tablename,whereclause,namevaluepairs)
LOCK TABLE tablename IN SHARE ROW EXCLUSIVE MODE
select ... from tablename where whereclause for update
if found
update tablename ....
else
insert into tablename
endifI'm not aware of a standard SQL command to do this, which seems like a common enough requirement. And the bright sparks made the syntax for updates different from inserts.
Oh well, maybe it's just me.
Link.
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
