| Subject | Re: [firebird-support] system table behaviour |
|---|---|
| Author | Helen Borrie |
| Post date | 2016-02-04T04:30:55Z |
Thursday, February 4, 2016, 2:19:07 PM, you wrote:
Hi. I am writing a small application for our support staff which includes the ability to see who is connected to a database using the query: SELECT M.MON$ATTACHMENT_ID, M.MON$USER, M.MON$REMOTE_ADDRESS, M.MON$TIMESTAMP, M.MON$REMOTE_PROCESS FROM MON$ATTACHMENTS M When the application first runs, it gets the correct number of records. If a user connects to the database and, without closing the application, I re-run the query, it doesn’t update the result. That is, the list of connected users does not show the new connection. Similarly if someone disconnects, the list is not updated. Closing the application and running it again updates the result. |
You don't have to close the application to refresh the MON$ results,
simply commit the transaction and run the query in a new transaction.
| Using Flamerobin, the results are as expected. I tried using a stored procedure to give me the list (hoping that moving the query closer to the database might yield more timely results) but that didn’t work either. I have also tried opening and closing the view and file. Other user tables seem to work as you would expect. Do the system tables cache results or do they behave differently to user tables? |
The MON4 tables are in-memory structures. Queries on them run in a
SNAPSHOT transaction. That is, the statement provides a snapshot of
the status of those items at the time the transaction started and will
not be "refreshed" by re-running the same query inside the same
transaction.
The persistent system tables (RDB$...) behave like any regular table.
Helen