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 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 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
>
This concept is outdated. You should set your application to correct isolation level to get a consistent state.
