Re: Locking question

From: Frank Millman
Sent: Wednesday, October 26, 2016 10:42 AM
To: pgsql-general(at)postgresql(dot)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