Issue Details (XML | Word | Printable)

Key: CORE-4958
Type: Improvement Improvement
Status: Open Open
Priority: Trivial Trivial
Assignee: Unassigned
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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)

Created: 11/Oct/15 05:52 AM   Updated: 11/Oct/15 06:52 AM
Component/s: ISQL
Affects Version/s: None
Fix Version/s: None

QA Status: No test


 Description  « Hide
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'.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 11/Oct/15 06:13 AM
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.

Pavel Zotov added a comment - 11/Oct/15 06:43 AM
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.

Dmitry Yemanov added a comment - 11/Oct/15 06:52 AM
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.