Calculate stock value with PostgreSQL

Question

I'm working on inventory system web app. It is based on latest Ruby on Rails and PostgreSQL. I need to track products stock values base on purchases and sales quantities. You can check out my simplified db schema (also shown below).

Table products has a stock field. This field must be calculated from products' total purchases and sales quantity.

SUM(purchases.quantity) - SUM(sales.quantity)

I'm looking for simple, performant and most important race conditions safe solution. My current (but not final) idea is to use db triggers here. But I have no mind how to implement them the right way.

CREATE TABLE products (
 id INT GENERATED ALWAYS AS IDENTITY,
 name varchar,
 stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
 PRIMARY KEY (id)
);
CREATE TABLE purchases (
 id INT GENERATED ALWAYS AS IDENTITY,
 quantity INT NOT NULL DEFAULT 0,
 product_id INT NOT NULL,
 CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id)
);
CREATE TABLE sales (
 id INT GENERATED ALWAYS AS IDENTITY,
 quantity INT NOT NULL DEFAULT 0,
 product_id INT NOT NULL,
 CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id)
);
INSERT INTO products (name)
 VALUES('Super Power');
INSERT INTO purchases (product_id, quantity)
 VALUES(1, 2), (1, 3), (1, 2);
INSERT INTO sales (product_id, quantity)
 VALUES(1, 1), (1, 2), (1, 2);
SELECT
 *
FROM
 products

• Vérace: I'm having difficulty in understanding what exactly your question is. Could you show a before (table data) and after (either query result and/or table data) under your envisaged scenario?

Answer 1

I'm not really sure if you have much of an issue, as long as you update the products.stock field every time you either INSERT INTO the purchases or sales tables. No need for a Trigger in this scenario. Also you should ensure your update on the products.stock happens in the same Transaction as your INSERT INTO the purchases and sales tables.

Alternatively you could also design your schema to not store the stock field on the products table. Rather create a View that joins products, purchases, and sales together, grouped on the product and aggregates the difference in purchases and sales per product as its stock, that way it's instantly always up to date whenever the purchases or sales change.

• Vérace: I tried using the VIEW approach which appears logical, but the problem with it is that there is no now way of ensuring that stock levels don't drop below 0 - see the end of this fiddle - you can't put a CHECK constraint on a VIEW! It would be nice if you could!
• J.D.: @Vérace Yea that would be nice but certainly not a necessity. If OP needs to ensure the stock doesn't drop below 0 that would be very specifically dependent on his application's workflow and we'd need more context. (As depending on what his application is doing, it may already not even be a possibility.)
• Vérace: Unless there's something very wierd going on, having a product stock quantity below 0 is not normally desirable and would be a bug in any system I've ever worked with - apart perhaps from a "stock" of money in a bank where one can have an overdraft - but that's quite particular to a given industry - the word "product" implies (to me) some physical object - having said that, banks and other financial services companies do sell "products" that are abstract/intangible... hmm.... maybe you're correct, some OP input might be required?
• J.D.: @Vérace Yea, there's also a ton of other reasons depending on his application's workflow why it may just not even be possible to go below 0. That being said, I think it's good to account for minimal error checks, like you're thinking. I just personally like to keep my answers minimally sufficient and concise without any assumptions, as sometimes with some questions by some posters, it can turn into a tangent lol.
• Vérace: If I had a euro for every time I've answered a question, OP comes back with caveat_1 - fix caveat_1, then caveat_2 appears, fix that... and so on..., I'd be a wealthy man! :-)

Answer 2

In order to answer this, I did the following (see fiddle here):

DROP TABLE IF EXISTS product;
CREATE TABLE product 
(
 product_id INT GENERATED ALWAYS AS IDENTITY,
 name varchar,
 stock INT NOT NULL DEFAULT 0,
 PRIMARY KEY (product_id),
 CONSTRAINT product_stock_gt_0_ck CHECK (stock >= 0) -- can't have negative stock levels
);

I tend to use the singular for table names - a table is a set of things - just my preference - it also makes things easier to remember.

DROP TABLE IF EXISTS purchase;
CREATE TABLE purchase 
(
 purchase_id INT GENERATED ALWAYS AS IDENTITY,
 product_id INT NOT NULL,
 quantity INT NOT NULL,
 CONSTRAINT purchase_product_fk FOREIGN KEY (product_id) REFERENCES product (product_id),
 CONSTRAINT purchase_gt_0-ck CHECK (quantity > 0) -- can't have a 0 or negative purchase
);

I changed the name of the FK to table_field_fk - more descriptive IMHO. Also, I added the CHECK constraint so that the purchase quantity would always be greater than or equal to 1 - one doesn't purchase 0 items and a negative quantity for a purchase makes no sense.

You could just have one table, say txn (short for transaction, which is an SQL keyword (see below) so I didn't use that. I could have used keywords by enclosing them in double quotes - but this is non-portable and messy (makes for difficulty in reading the SQL and produces potentially confusing error messages).

and

DROP TABLE IF EXISTS sale;
CREATE TABLE 
sale 
(
 sale_id INT GENERATED ALWAYS AS IDENTITY,
 product_id INT NOT NULL,
 quantity INT NOT NULL DEFAULT 0,
 CONSTRAINT sale_product_fk FOREIGN KEY (product_id) REFERENCES product (product_id)
);

I inverted the order of product_id and quantity in your schema - more logical from my POV. Note also that I put the _fk at the end - this means that related objects sort together when using \d to describe database objects.

I didn't do the INSERTs that were in your schema - instead I did the following:

INSERT INTO product (name, stock) 
VALUES 
('product1', 100), ('product2', 200), ('product3', 300);

You now have a choice - update using "pure" DML or via TRIGGERs.

To perform UPDATEs in a "race-safe" way using a query, I did the following:

START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE; -- READ COMMITTED (default)
INSERT INTO purchase (quantity, product_id) VALUES (1000, 1); -- also suitable
INSERT INTO purchase (quantity, product_id) VALUES (2000, 2);
INSERT INTO purchase (quantity, product_id) VALUES (3000, 3);
UPDATE product SET stock = stock + 1000 WHERE product_id = 1;
UPDATE product SET stock = stock + 2000 WHERE product_id = 2;
UPDATE product SET stock = stock + 3000 WHERE product_id = 3;
COMMIT;

Note that I used the SERIALIZABLE transaction isolation level - this isn't strictly necessary - the READ COMMITTED level should be more than adequate as you are not reading and rereading values from other tables which may change between your first and second read. I would urge you to check out [PostgreSQL's documentation][1] and the internet generally (here for example) on the issue of transaction isolation. It's a tricky topic and not having a good grasp of it can lead to subtle, tricky-to-find bugs.

After this transaction, I now have:

SELECT * FROM product;

Result:

product_id name stock
 1 product1 1100
 2 product2 2200
 3 product3 3300

We can see (taking product_1) that we have our original 100 plus the 1000 of the purchase.

and

SELECT * FROM purchase

Result:

purchase_id product_id quantity
 1 1 1000
 2 2 2000
 3 3 3000

So, this is relatively simple. However, the only fly in the ointment here is that you have to remember to update two tables instead of one and you'll have to remember this at every point in the user interface in your application where the user makes a new purchase. I also included an example of the same thing using the sale table (not shown - see fiddle).

The way of obviating this brain overhead memory requirement^(*) so that when you make a purchase, the product table stock field is also updated is to use TRIGGERs as follows:

_{(*) an interesting book on this topic!}

First, PostgreSQL requires a procedure/function to be called by a TRIGGER as follows (you might this and this helpful):

CREATE OR REPLACE FUNCTION purchase_after_ins()
 RETURNS TRIGGER AS
$BODY$
BEGIN
 UPDATE product
 SET stock = stock + new.quantity
 WHERE product_id = new.product_id;
 
 RETURN NEW;
 
END;
$BODY$
LANGUAGE PLPGSQL;

and then the calling TRIGGER itself:

CREATE TRIGGER purchase_after_ins_tr
 AFTER INSERT
 ON purchase
 FOR EACH ROW
 EXECUTE PROCEDURE purchase_after_ins();

Run some SQL using an INSERT into the purchase table:

START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE; -- could have SERIALIZABLE
INSERT INTO purchase (quantity, product_id) VALUES (1111111, 1);
COMMIT;

and then we check our result:

SELECT * FROM product
ORDER BY product;

Result:

product_id name stock
1 product1 1112100
2 product2 1978
3 product3 2967

So, we see that our INSERT into the purchase table has resulted in the same number of items being added to the appropriate stock level of product_1.

I also tried making a "purchase" of -111111110 of product_1 - equivalent to a sale (not shown, see the fiddle) and the database threw an appropriate error:

ERROR: new row for relation "purchase" violates check constraint "purchase_qty_gt_0_ck"
DETAIL: Failing row contains (5, 1, -111111110).

This demonstrates that the TRIGGER doesn't override or ignore your table DDL, i.e. CHECK constraints - this also holds for FKs also (or anything else).

TRIGGERs are an extremely powerful mechanism for turning your database into a fully-fledged application in its own right (minus the GUI) and I cannot emphasise enough how important it is to master them.

I have only included the bare bones of a solution - I leave it up to you to construct appropriate TRIGGERs for an INSERT on the sale table and potential UPDATEs to the purchase table inter alia. Exactly how all these will work will depend on your own business rules and practices in your own industry.