Subject ODP: RE: [firebird-support] system table behaviour
Author liviuslivius
Post date 2016-02-04T07:00:11Z
hi,

as Helen say
you must commit previous transaction not close connection

regards,
Karol Bieniaszewski


-------- Oryginalna wiadomość --------
Od: "'Andrew Zenz' andrew@... [firebird-support]" <firebird-support@yahoogroups.com>
Data: 04.02.2016 05:55 (GMT+01:00)
Do: firebird-support@yahoogroups.com
Temat: RE: [firebird-support] system table behaviour

 

Thanks Helen.

 

‘Tis starting to make some sense now.

 

Unfortunately our application establishes a connection to the FB server and remains active until shut down.  I can’t terminate it and create a new one.  A ‘feature’ of the RAD we are using.

 

You have given me an idea though, thanks.

 

Andrew Zenz

 

 

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Thursday, 4 February 2016 3:31 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] system table behaviour

 

 

Hello 'Andrew,

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