RE: [firebird-support] system table behaviour

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On Thu, 4 Feb 2016 15:55:29 +1100, "'Andrew Zenz'
and...@aimsoftware.com.au
[firebird-support]"  wrote:
> '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 don't need to close the connection or restart your application. You
only need to commit the current transaction (and start a new one if that
isn't done automatically for you).

Mark

ODP: RE: [firebird-support] system table behaviour

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

hi,
as Helen sayyou must commit previous transaction not close connection
regards,Karol Bieniaszewski

 Oryginalna wiadomość 
Od: "'Andrew Zenz' and...@aimsoftware.com.au [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















 

Re: [firebird-support] system table behaviour

[Helen Borrie hele...@iinet.net.au [firebird-support]]

RE: [firebird-support] system table behaviour

['Andrew Zenz' and...@aimsoftware.com.au [firebird-support]]

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