Обсуждение: Re: Need to find the no. of connections for a database
Hello,
Before taking a few reports, we need to ensure that only one connection is made to the database and all other computers need to close the connection to that database. This is to prevent any additional entry(ies) that could be made during the course of the report taking. This single-user mode is to be there till the report is taken and few entries are passed.
How to do it? Is it possible to switch to single-user mode from application and back to multi-user mode once the work is completed? Or any other solution available?
Happiness Always
BKR Sivaprakash
On 2020-02-27 07:41:36 +0000, sivapostgres@yahoo.com wrote:
> Before taking a few reports, we need to ensure that only one connection is made
> to the database and all other computers need to close the connection to that
> database. This is to prevent any additional entry(ies) that could be made
> during the course of the report taking.
Do you have control over those reports or are they generated by a
third-party tool?
If the former, the best way is probably to just run them all in a single
REPEATABLE READ transaction. Then they will all reflect the state of the
database at the start of the transaction, regardless of what other
clients are doing in the meantime.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Вложения
> > Before taking a few reports, we need to ensure that only one connection is made to the database and all other computersneed to close the connection to that database. This is to prevent any additional entry(ies) that could be madeduring the course of the report taking. This single-user mode is to be there till the report is taken and few entriesare passed. > > How to do it? Is it possible to switch to single-user mode from application and back to multi-user mode once the workis completed? Or any other solution available? > > Happiness Always > BKR Sivaprakash > This concept is outdated. You should set your application to correct isolation level to get a consistent state.
Well,
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.
If they enter any data, those data also need to be taken into account in this report. The process is somehow lengthy that starts from arriving some cumulative value(s) and based on this value, some lengthy process is done. Any data entered in between will affect this report as well the process we do.
For this same situation, while using SQL Server, we used to count the number of users of that database and if it's greater than one, we don't start the process. By checking the same no. of users in vantage point, we could achieve the required result.
I tried the same way in Postgres, but I could not get the correct connection list, by using this query.
SELECT pid, datname, usename, application_name, client_hostname, client_port, backend_start, query_start, query, state
FROM pg_stat_activity
WHERE datname = 'databasename'
And state = 'active'
I think setting isolation level will not work out. Switching to single user mode, if available, will be better.
Any ideas ?
Happiness Always
BKR Sivaprakash
On Thursday, 27 February, 2020, 04:34:46 pm IST, Ravi Krishna <srkrishna@gmx.com> wrote:
>
> Before taking a few reports, we need to ensure that only one connection is made to the database and all other computers need to close the connection to that database. This is to prevent any additional entry(ies) that could be made during the course of the report taking. This single-user mode is to be there till the report is taken and few entries are passed.
>
> How to do it? Is it possible to switch to single-user mode from application and back to multi-user mode once the work is completed? Or any other solution available?
>
> Happiness Always
> BKR Sivaprakash
> Before taking a few reports, we need to ensure that only one connection is made to the database and all other computers need to close the connection to that database. This is to prevent any additional entry(ies) that could be made during the course of the report taking. This single-user mode is to be there till the report is taken and few entries are passed.
>
> How to do it? Is it possible to switch to single-user mode from application and back to multi-user mode once the work is completed? Or any other solution available?
>
> Happiness Always
> BKR Sivaprakash
>
This concept is outdated. You should set your application to correct isolation level to get a consistent state.
"If they enter any data, those data also need to be taken into account in this report. " Pls read on PG's MVCC architecture. In SQLServer, unless you enabled its bad implementation of Snapshot isolation, you can't achieve the same. So it makes sense there. In PG it is easy to ensure that your report gets a point in time consistent view of the data. Trying to mimic one database in another is not a smart way.
W dniu 2020-02-27 o 12:35, sivapostgres@yahoo.com 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
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.
On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl <ipluta@wp.pl> wrote:
W dniu 2020-02-27 o 12:35, sivapostgres@yahoo.com 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
> 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
W dniu 2020-02-27 o 14:37, sivapostgres@yahoo.com 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@wp.pl> wrote: > > > W dniu 2020-02-27 o 12:35, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com> 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().
On Thursday, 27 February, 2020, 07:39:13 pm IST, Ireneusz Pluta/wp.pl <ipluta@wp.pl> wrote:
W dniu 2020-02-27 o 14:37, sivapostgres@yahoo.com 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@wp.pl> wrote:
>
>
> W dniu 2020-02-27 o 12:35, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com> 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
>
>
>
>
> 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@wp.pl> wrote:
>
>
> W dniu 2020-02-27 o 12:35, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com> 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
>
>
>
>
W dniu 2020-02-27 o 15:26, sivapostgres@yahoo.com 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@wp.pl> wrote: > > > W dniu 2020-02-27 o 14:37, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com> 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@wp.pl > <mailto:ipluta@wp.pl>> wrote: > > > > > > W dniu 2020-02-27 o 12:35, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com> > <mailto:sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com>> 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 > > > > > > > > > >
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@wp.pl> wrote:
W dniu 2020-02-27 o 15:26, sivapostgres@yahoo.com 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@wp.pl> wrote:
>
>
> W dniu 2020-02-27 o 14:37, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com> 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@wp.pl
> <mailto:ipluta@wp.pl>> wrote:
> >
> >
> > W dniu 2020-02-27 o 12:35, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com>
> <mailto:sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com>> 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@wp.pl> wrote:
>
>
> W dniu 2020-02-27 o 14:37, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com> 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@wp.pl
> <mailto:ipluta@wp.pl>> wrote:
> >
> >
> > W dniu 2020-02-27 o 12:35, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com>
> <mailto:sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com>> 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
> >
> >
> >
> >
>
>
Hello, On Fri, 2020-02-28 at 01:10 +0000, sivapostgres@yahoo.com wrote: > 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. > If the menu is built from a table in your database, then when "Processing report" starts you could set a flag (boolean) against those items so that if anybody tried to log-in or access those items, you could simply display a message along the lines of "Processing report is running. Please try again later". When "Processing report" finishes, it just clears that flag. HTH, Rob
On 2/27/20 10:38 PM, rob stone wrote: > > If the menu is built from a table in your database, then when > "Processing report" starts you could set a flag (boolean) against those > items so that if anybody tried to log-in or access those items, you > could simply display a message along the lines of "Processing report is > running. Please try again later". > > When "Processing report" finishes, it just clears that flag. > > HTH, > Rob > Conversely, the OP could be asking for a way to turn "process report" to active state when no one is using the other features: each of them could set a lock/counter I guess and when all counters are zero enable process button. The notion of waiting for "all clear" from the app or the db before running reports does seem odd though. Do certain actions from the app leave the database in an inconsistent state and break the report were it run at the same time?
Since the no. of entry screens to be locked might increase with enhancement(s), the approach could be
1. On completion of all entries by all, say for a day.
2. When opening the report, check whether only one user has logged in. Close the report window, if there are more than one users.
3. Flag somewhere in the db, that the processing has started.
4. Check at the opening event of the 'tobe locked screens', whether this flag set in point 3. If set, quit the screen.
5. Create a report from the entered data.
6. Create / Modify required entries from the values arrived in the report. [ long process ]
7. Once completed, commit all data. Reset the flag set in point 3.
8. Ensure that no data entered for the processed period. Data entered will be for another date. Data cannot [should] not entered for the processed date.
If this works out, we need to find a way to count the no. of users logged in the database.
On Friday, 28 February, 2020, 11:35:48 am IST, Rob Sargent <robjsargent@gmail.com> wrote:
On 2/27/20 10:38 PM, rob stone wrote:
>
> If the menu is built from a table in your database, then when
> "Processing report" starts you could set a flag (boolean) against those
> items so that if anybody tried to log-in or access those items, you
> could simply display a message along the lines of "Processing report is
> running. Please try again later".
>
> When "Processing report" finishes, it just clears that flag.
>
> HTH,
> Rob
>
Conversely, the OP could be asking for a way to turn "process report" to
active state when no one is using the other features: each of them could
set a lock/counter I guess and when all counters are zero enable process
button. The notion of waiting for "all clear" from the app or the db
before running reports does seem odd though. Do certain actions from
the app leave the database in an inconsistent state and break the report
were it run at the same time?