Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Make ISQL do not count records that in fact are not deleted from mon$attachments due to mon$system_flag=1 (Cache Writer & Garbage Collector) [CORE4958] #5249

Open
firebird-automations opened this issue Oct 11, 2015 · 3 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Sample (SuperServer, single attachment from SYSDBA):

SQL> set list on;
SQL> select mon$attachment_id, mon$user, mon$remote_protocol, mon$remote_process, mon$system_flag from mon$attachments where mon$attachment_id <> current_connection;

MON$ATTACHMENT_ID 132
MON$USER Cache Writer
MON$REMOTE_PROTOCOL <null>
MON$REMOTE_PROCESS <null>
MON$SYSTEM_FLAG 1

MON$ATTACHMENT_ID 133
MON$USER Garbage Collector
MON$REMOTE_PROTOCOL <null>
MON$REMOTE_PROCESS <null>
MON$SYSTEM_FLAG 1

SQL> set count on;
SQL> delete from mon$attachments where mon$attachment_id <> current_connection;

Records affected: 2 -------------------------------------------------- [ 1 ]

SQL> commit;
SQL> select mon$attachment_id, mon$user, mon$remote_protocol, mon$remote_process, mon$system_flag from mon$attachments where mon$attachment_id <> current_connection;

MON$ATTACHMENT_ID 132
MON$USER Cache Writer
MON$REMOTE_PROTOCOL <null>
MON$REMOTE_PROCESS <null>
MON$SYSTEM_FLAG 1

MON$ATTACHMENT_ID 133
MON$USER Garbage Collector
MON$REMOTE_PROTOCOL <null>
MON$REMOTE_PROCESS <null>
MON$SYSTEM_FLAG 1

Records affected: 2

What does ISQL mean in line marked "[1]" ? In fact, NO rows has been deleted from mon$attachments because they have system_flag = 1.
It will be nice if ISQL do not count them and show correct number in 'Records affected'.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

While perhaps it could be improved, I'd rather avoid relying on the "records affected" counters for MON$ tables. DELETE there means only "mark for deletion (termination/cancellation)" and does not delete anything. So maybe more honest would be to not count deletes at all in the statistics.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

IMO, we *should* count only those records that are really affected, i.e. which have system_flag = 0.

Following sample also returns "DELETED_CNT 2", but now context variable 'row_count' is in use:

set list on;
set term ^;
execute block returns(deleted_cnt int) as
begin
delete
from mon$attachments
where mon$attachment_id <> current_connection;

  deleted\_cnt = row\_count;
  suspend;

end
^
set term ;^

But this code (unlike 1st, with 'records affected') can be used in some service routines or DBA scripts.
If there is one 'common' attachment (with system_flag = 0) and statement like 'delete from mon$att' will not count records - this script will always return 0. This will be wrong.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

As I already said, nobody should rely on that counter, it says nothing useful. If the bad script would return something unexpected, so be it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant