Commented by: Sean Leyne (seanleyne)

That would have a potential impact on gbak (or any equivalent process that a developer might need to create). It opens standard DB connection/transactions to then extract the data from the database. This suggestion would have the effect of killing such a process.

It seems that this issue is trying to solve application issues at the database level, that is not correct.

Further, if this were an issue for a developer, then they are free to build an application/utility which uses the MON$ tables to perform the same operation.

Commented by: @pavel-zotov

I think that the engine can detect such 'system' connects like from gbak or nbackup (being called directly or via fbsvcmgr). Of course such transactions have to be preserved from being forcely commit / rollback.

> It seems that this issue is trying to solve application issues at the database level, that is not correct.

Why it is not correct ?
We already have some settings in firebird.conf that tries to solve some application issues (e.g. TempCacheLimit or ConnectionTimeout). I think that firebird.conf content shoudl NOT be abstract from concrete reality (environtment) of production database.

> if this were an issue for a developer, then they are free to build an application/utility which uses the MON$ tables

Yes, we made such 'utility' (cron + bash script), exactly in THIS manner few years ago.
But as for me this looks as temporary solution, because it is doing by external tools (cron + bash) and, furtermore, any new DBA whould be carefully 'dive' into this script (and many analogous) to understand what it does and how can it be updated.

Commented by: Sean Leyne (seanleyne)

First, while I agree that the problem of long running TX can cause *possible* performance issues.

The proposed solution, however, would use a very heavy "hammer" to solve a problem which would need much finer control.

WRT the existing settings/options, I disagree that these are in place to solve application issues -- they are about supporting the database engine.

Commented by: @pavel-zotov

DBA and application developers often do their job separately (both in space and time).
Application developers (who made end-user binary with poor TX management) can disappear and DBA will stay alone with countless problems which he can`t fix. Most unpleasant of these problems - performance issues in the middle of users' working time.

I suggest simple solution that can facilitate dayly usage of heavy-loaded database - even without need to know something about MON$-tables, cron (`at` on Windows) or bash / cmd script languages :-)

Commented by: Sean Leyne (seanleyne)

But your 'simple' solution will only have narrow use and not solve the real breath of issue -- it assumes that the unique variations of connection/transaction purposes can be discovered from knowledge which is available to the engine.

Further, I don't see how that will be possible without:
- adding additional properties to the connections/transactions to assist the evaluation process
- adding complex script/REGEX support to allow for the exceptionalities to be handled.

Commented by: @pavel-zotov

> it assumes that the unique variations of connection/transaction purposes can be discovered from knowledge

I think that only few tools from Firebird package should never be interrupted in such 'automated' mode: {gbak, nbackup, gfix, gstat}.

These names even can be hardcoded inside some 'checking' function. If current user = SYSDBA and process_name belongs to this list - do NOT interrupt it. Otherwise - check for timeout expired.

>Further, I don't see how that will be possible without:
> - adding additional properties to the connections/transactions to assist the evaluation process
> - adding complex script/REGEX support to allow for the exceptionalities to be handled.

I think there is no any need neither adding properties to connections/transactions nor any complex regexp.

PS. Eventually, this feature in should be DISABLED by default. It's up to DBA to use it. If there are some another (3rd party) programs that should not be interrupt - welcome to manually solution via cron + MON$-tables + bash / cmd script.

Commented by: @dyemanov

Looks like a duplicate for CORE985 (Transaction Timeout part).

Commented by: Sean Leyne (seanleyne)

I don't see it as a duplicate, certainly related though, because this case describes more 'inteligence' then that case describes.

Commented by: PizzaProgram Ltd. (szakilaci)

I agree on this is just a small part of CORE985 (Transaction Timeout part).
It would be much easier if ALL commands / transactions could be simply "timeouted". Like:
{code}
UPDATE WITH TIMEOUT 5000 invoices SET closed_time = CURRENT_TIMESTAMP WHERE ID = 123;
{code} ... or something similar.
Thanks ;)