From: Frank Millman
Sent: Wednesday, October 26, 2016 10:42 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Locking question
> I am designing an inventory application, and I want to ensure that the stock
> level of any item cannot go negative.
Thanks to all for some really great replies. Much food for thought there.
As mentioned previously, I am trying to avoid using PostgreSQL-specific
techniques, as I need to support sqlite3 and SQL Server as well.
There is an additional complication that I forgot to mention in my original
post.
For costing purposes, I want to run a FIFO system. This means I have to
maintain separate entries for each receipt of stock, and allocate any sales of
stock against the receipts ‘oldest first’.
Assume the following purchases -
2016-06-01 qty 5 Unit price $5.00
2016-06-02 qty 10 Unit price $5.50
2016-06-03 qty 15 Unit price $6.00
Quantity on hand after the third purchase is 30. Whether this should be
maintained as a total somewhere, or derived from totalling the receipts, is a
matter for debate, but I think that it is not relevant for this discussion.
Then assume the following sales -
2016-06-11 qty 8
2016-06-12 qty 12
2016-06-13 qty 16
The first sale will succeed, and will record a ‘cost of sale’ of (5 x $5.00) +
(3 x $5.50).
The second sale will succeed, and will record a ‘cost of sale’ of (7 x $5.50)
+ (5 x $6.00).
The third sale must be rejected, as there is insufficient stock.
This is how I propose to achieve this -
CREATE TABLE inv_rec
(row_id SERIAL PRIMARY KEY,
product_id INT REFERENCES inv_products,
rec_date DATE,
qty INT
unit_price DEC(15, 2));
CREATE TABLE inv_alloc
(row_id SERIAL PRIMARY KEY,
rec_id INT REFERENCES inv_rec,
qty INT);
INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
VALUES (99, ‘2016-06-01’, 5, 5.00);
INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
VALUES (99, ‘2016-06-02’, 10, 5.50);
INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
VALUES (99, ‘2016-06-03’, 15, 6.00);
The sales will be handled at application level. Here is some pseudo code -
qty_to_allocate = sale_qty
cost_of_sale = 0
BEGIN TRANSACTION
SELECT a.row_id, a.unit_price,
a.qty + COALESCE((SELECT SUM(b.qty) FROM inv_alloc b
WHERE b.rec_id = a.row_id), 0) AS balance
FROM inv_rec a
WHERE a.product_id = 99
AND
a.qty + COALESCE((SELECT SUM(b.qty) FROM inv_alloc b
WHERE b.rec_id = a.row_id), 0)
> 0
ORDER BY a.rec_date
FOR UPDATE
for row in rows:
if row.balance >= qty_to_allocate:
INSERT INTO inv_alloc (rec_id, qty)
VALUES (row.row_id, –qty_to_allocate)
cost_of_sale += (qty_to_allocate * unit_price)
qty_to_allocate = 0
else:
INSERT INTO inv_alloc (rec_id, qty)
VALUES (row.row_id, –row.balance)
cost_of_sale += (row.balance * unit_price)
qty_to_allocate –= row.balance
if qty_to_allocate: # i.e. insufficient stock
raise exception and ROLLBACK
else:
COMMIT
My main concern is that this should be robust.
A secondary concern is that it should be reasonably efficient, but that is not
a priority at this stage. If it became a problem, I would look at maintaining a
‘balance’ column on each ‘inv_rec’.
Comments welcome.
Frank
