You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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;
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'.
The text was updated successfully, but these errors were encountered:
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.
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.
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'.
The text was updated successfully, but these errors were encountered: