Then clearly I've misunderstood what advisory lock could do. We used to put locks in SQL server to avoid deadlock situations. I thought advisory lock is a similar one. [ New to Postgres ]
The report is arrived from around 10 tables out of 300 tables that are in the database. Once we start this process, we need to ensure that no other user could enter any data in those 10 tables, at least for the processing period. I thought the table lock [ those 10 tables ] will ensure no entry.
We have a menu like this in our application
Purchase Entry
Sales Entry
Sales Cancellation
Report
Processing report
When we enter the Processing report and click process, we need to ensure that no one could enter data from Purchase Entry, Sales Entry, Sales Cancellation, etc.
Couldn't understand how advisory lock could achieve this?
Happiness Always
BKR Sivaprakash
On Thursday, 27 February, 2020, 10:04:12 pm IST, Ireneusz Pluta/wp.pl <ipluta@xxxxx> wrote:
W dniu 2020-02-27 o 15:26, sivapostgres@xxxxxxxxx pisze:
> Need to lock around 10 tables. Let me try with pg_advisory_lock().
I am not sure why you mention table locks at this point. Just in case: with advisory locks you lock
an "application flow", not database objects.
>
>
> On Thursday, 27 February, 2020, 07:39:13 pm IST, Ireneusz Pluta/wp.pl <ipluta@xxxxx> wrote:
>
>
> W dniu 2020-02-27 o 14:37, sivapostgres@xxxxxxxxx <mailto:sivapostgres@xxxxxxxxx> pisze:
> > Hello,
> >
> > I'm saying isolation will not work out to my requirement. The steps.
> >
> > 1. On completion of all entries by all, say for a day.
> > 2. Lock, so that no one enters any other data.
> > 3. Create a report from the entered data.
> > 4. Create / Modify required entries from the values arrived in the report. [ long process ]
> > 5. Once completed, commit all data.
> > 6. Unlock, so that other users can enter data again. Data entered will be for another date.
> > Data cannot [should] not entered for the processed date.
> >
> so wouldn't pg_advisory_lock() in step 2, and pg_advisory_unlock() in step 6, make the process work
> in the way you expect?
>
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
>
> >
> >
> > On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl <ipluta@xxxxx
> <mailto:ipluta@xxxxx>> wrote:
> >
> >
> > W dniu 2020-02-27 o 12:35, sivapostgres@xxxxxxxxx <mailto:sivapostgres@xxxxxxxxx>
> <mailto:sivapostgres@xxxxxxxxx <mailto:sivapostgres@xxxxxxxxx>> pisze:
>
> >
> > > I need to prevent other users from entering any transaction till I finish taking reports from my
> > > application. All users will be using the same application, from which this report is supposed to
> > > be printed.
> >
> > >
> > maybe advisory lock is what you need?
> >
> > https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
> >
> >
> >
> >
>
>
> Need to lock around 10 tables. Let me try with pg_advisory_lock().
I am not sure why you mention table locks at this point. Just in case: with advisory locks you lock
an "application flow", not database objects.
>
>
> On Thursday, 27 February, 2020, 07:39:13 pm IST, Ireneusz Pluta/wp.pl <ipluta@xxxxx> wrote:
>
>
> W dniu 2020-02-27 o 14:37, sivapostgres@xxxxxxxxx <mailto:sivapostgres@xxxxxxxxx> pisze:
> > Hello,
> >
> > I'm saying isolation will not work out to my requirement. The steps.
> >
> > 1. On completion of all entries by all, say for a day.
> > 2. Lock, so that no one enters any other data.
> > 3. Create a report from the entered data.
> > 4. Create / Modify required entries from the values arrived in the report. [ long process ]
> > 5. Once completed, commit all data.
> > 6. Unlock, so that other users can enter data again. Data entered will be for another date.
> > Data cannot [should] not entered for the processed date.
> >
> so wouldn't pg_advisory_lock() in step 2, and pg_advisory_unlock() in step 6, make the process work
> in the way you expect?
>
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
>
> >
> >
> > On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl <ipluta@xxxxx
> <mailto:ipluta@xxxxx>> wrote:
> >
> >
> > W dniu 2020-02-27 o 12:35, sivapostgres@xxxxxxxxx <mailto:sivapostgres@xxxxxxxxx>
> <mailto:sivapostgres@xxxxxxxxx <mailto:sivapostgres@xxxxxxxxx>> pisze:
>
> >
> > > I need to prevent other users from entering any transaction till I finish taking reports from my
> > > application. All users will be using the same application, from which this report is supposed to
> > > be printed.
> >
> > >
> > maybe advisory lock is what you need?
> >
> > https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
> >
> >
> >
> >
>
>
