Re: Locking question

From: hubert depesz lubaczewski
Sent: Wednesday, October 26, 2016 10:46 AM
To: Frank Millman
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Locking question

On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote:
> Hi all
> >
> > I am designing an inventory application, and I want to ensure that the stock level of any item cannot go negative.
> >
[...]
>
> What I would do, is to add trigger on inv_alloc, than when you
> insert/update/delete row there, it updates appropriate row in inv_rec by
> correct number.
>
> Then, I'd add check on inv_rec to make sure qty is never < 0.
>

Thanks, depesz

I can see how that would work, but I have two comments.
1. I am writing my application to support 3 databases – PostgreSQL, sqlite3, and MS SQL Server. Because they are all so different when it comes to triggers and procedures, I am trying to avoid using them, and do as much within the application as possible.
2. I think you are suggesting maintaining a ‘balance’ column on inv_rec. This raises the question of whether or when you should create and maintain a column if the same information could be derived from other sources. I realise that this is a judgement call, and sometimes I struggle to get the balance right. Is this a situation where people would agree that it is warranted?
I would still appreciate some feedback as to whether my proposed solution would work.
Thanks
Frank