On Wed, 2016-10-26 at 12:18 +0200, Frank Millman wrote:
>
> From: hubert depesz lubaczewski
> Sent: Wednesday, October 26, 2016 10:46 AM
> To: Frank Millman
> Cc: pgsql-general@postgresql.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.
> > >
> [...]
>
> 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
>
Hello Frank,
Stock on-hand quantity = goods inwards +
goods returned from customers -
goods invoiced -
goods returned to suppliers due to defects.
Available stock quantity = stock on-hand -
goods ordered.
The hassle you have in a multi-user environment is when User "A" takes
an order from Customer "A" for Product "A" and at the same time User
"B" takes an order from Customer "B" also for Product "A".
Both users will quote the same value for quantity available (or
quantity on-hand if you don't keep track of orders).
Most companies rank their customers according to internal policy. For
example, customers who pay their bills late could be given a lower rank
than those that pay on time. Some customers won't accept partial
deliveries. Etc.
So, if you just take orders you can run a batch process to convert
orders into invoices and read your orders according to customer ranking
criteria.
By running a batch process, with a suitable "locking" mechanism to
avoid the process being run twice at the same time, the maintenance of
stock on-hand quantities is run in a single instance, you won't over
ship and it won't become negative.
I think this method will work on all three databases cited.
HTH,
Rob