Re: Locking question

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