Question for replacement

[Tomasz Dubiel]

Hello.

Our software makes inserts to the one table which holds informations about users currently logged into our ERP: id_user and connection_id.

When logging in, there is a insert into this table with given values.

There is also a trigger after insert which deletes data from this table, to keep the data fresh:

delete from table
  where connection_id not in (select MON$ATTACHMENT_ID
                                                    from MON$ATTACHMENTS);

As I already know, every call to monitoring tables can cause problems with performance when having many users logged in.

Do you have any idea what a replacement could we use to get rid off monitoring tables and keep the current logic?

Best regards.

[Lucas Schatz]

Why not a "on disconnection" trigger to delete only the current record?

And "frequently" run this your delete?

I'm not sure in what circumstances the "on disconnect" doesn't work, maybe on DB shutdown (no real idea)

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/6ec10860-c9ac-4693-9217-f170713ba477n%40googlegroups.com.

[Tomasz Dubiel]

If this trigger would ensure the consistency of data, we could try this.

[Tomasz Dubiel]

Thanks, looks like a quite simple solution.

[Tomasz Dubiel]

Well, you're right.

https://github.com/FirebirdSQL/firebird/issues/7042

We must be prepared for deleting manually records when having database shutdown or deleting from mon$attachments

[Tomasz Dubiel]

In our situation hanged sessions can occur very frequently and then we delete them from MON$ATTACHMENTS. When having current solution, we have always fresh data (as quick as only next user logs into the system).

Trigger on disconnect would be ideal for normal disconnecting, but there should be plan "B" for not clean disconnects.

I can think of a task which regularly would do what our current trigger does, but that would require additional activity outside of our software. It would be nice to handle it everything in the application.

[Dimitry Sibiryakov]

Tomasz Dubiel wrote 07.05.2023 19:44:
> Do you have any idea what a replacement could we use to get rid off monitoring
> tables and keep the current logic?

I see no point to keep the current logic. What this table is used for?

--
WBR, SD.

[Tomasz Dubiel]

To view the current users logged into the system and the names of these users' "computers" which the license for particular ERP module was given.

[Dimitry Sibiryakov]

Tomasz Dubiel wrote 07.05.2023 21:44:
> To view the current users logged into the system and the names of these users'
> "computers" which the license for particular ERP module was given.

IIRC, your ERP is a web-application (or any other kind of multi-tier
architecture), right? So there is no relation between database connection and
user connection.
In this case a table with insert of CURRENT_ATTACHMENT and other info in ON
CONNECT trigger and delete in ON DISCONNECT trigger is more than enough. The
only case when records in such table can "stuck" is a Firebird server crash and
cleanup of the table can be a part of recovery process.

--
WBR, SD.

[Mark Rotteveel]

On 07-05-2023 21:44, Tomasz Dubiel wrote:
> To view the current users logged into the system and the names of these
> users' "computers" which the license for particular ERP module was given.

Wouldn't it be sufficient to cleanup the records as part of viewing this
information? (i.e. delete before selecting the information.)

Mark
--
Mark Rotteveel

[Tomasz Dubiel]

Not exactly. We also have the license for maximum allowed logged users so the table must be quite fresh.

[Mark Rotteveel]

On 08-05-2023 09:54, Tomasz Dubiel wrote:
> Not exactly. We also have the license for maximum allowed logged users
> so the table must be quite fresh.

How often is that license check performed? Would performing the cleanup
as part of the license check, or maybe joining against `MON$ATTACHMENTS`
to only retrieve actually attached users be sufficient (and performant)?

Mark
--
Mark Rotteveel

[Tomasz Dubiel]

It's probably done with every user logon to the system so both options would still cause slowness (or select from our table join mon$attachment on attachment_id would not cause it?)

[Mark Rotteveel]

On 08-05-2023 10:21, Tomasz Dubiel wrote:
> It's probably done with every user logon to the system so both options
> would still cause slowness (or select from our table join mon$attachment
> on attachment_id would not cause it?)

That would still cause the same slowness, as it will result in the
creation of a monitoring snapshot.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

The main problem that it will be useless because cannot detect lost
connections which is (AFAIU) the target of this query.

--
WBR, SD.

[Bogdan Mugerli]

when we've started our company, many years ago, the main preocupation was how to protect our applications from illegal use.

After 20 years i finely see how stupid that was.

Lp. Bogdan

---

From: 'Dimitry Sibiryakov' via firebird-support <firebird...@googlegroups.com>
Sent: Monday, May 8, 2023 11:50:47 AM
To: firebird...@googlegroups.com <firebird...@googlegroups.com>
Subject: Re: [firebird-support] Question for replacement

 

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/90094af0-e11e-9283-6e9e-ac2b2d1222cd%40ibphoenix.com.