Re: Need to find the no. of connections for a database

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 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.

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, stateFROM pg_stat_activityWHERE datname = 'databasename'And   state = 'active'
I expect this query to add the number, when there is another connection from one computer.  I couldn't get it when I tried this query from PGAdmin and connected this database from another machine.  It's just a connection and no query was executed from that machine.
I think setting isolation level will not work out. Switching to single user mode, if available, will be better.  
Any ideas ?
Happiness AlwaysBKR 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

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

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

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

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.

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 EntrySales EntrySales CancellationReport        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 AlwaysBKR 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.

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?