Result of several updates of RDB$PROCEDURES (and RDB$TRIGGERS) depends on
presence of ROLLBACK between each of DML statements
-------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4772
URL: http://tracker.firebirdsql.org/browse/CORE-4772
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
On empty database (Firebird version: WI-T3.0.0.31807, tested on SS and SC) do:
TEST-1.
######
---------------- start of script `test1.sql` ---------------
set term ^;
create procedure sp_dummy(a_id int) returns(o_txt varchar(20)) as
begin
o_txt = 'get id='||a_id;
suspend;
end
^
set term ;^
commit;
set count on; set echo on;
update RDB$PROCEDURES t set t.RDB$PROCEDURE_SOURCE = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$RUNTIME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$ENGINE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$ENTRYPOINT = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$PACKAGE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$PRIVATE_FLAG = null where
coalesce(rdb$system_flag,0)=0 rows 1;
rollback;
update RDB$PROCEDURES t set t.RDB$PROCEDURE_SOURCE = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$RUNTIME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$ENGINE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$ENTRYPOINT = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$PACKAGE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
update RDB$PROCEDURES t set t.RDB$PRIVATE_FLAG = null where
coalesce(rdb$system_flag,0)=0 rows 1;
rollback;
---------------- end of script `test1.sql` ---------------
Run: isql.exe localhost/port:<path>\<file> -i test1.sql 1>result1.log 2>&1
Result-1:
#######
update RDB$PROCEDURES t set t.RDB$PROCEDURE_SOURCE = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$RUNTIME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$ENGINE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$ENTRYPOINT = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$PACKAGE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$PRIVATE_FLAG = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
rollback;
update RDB$PROCEDURES t set t.RDB$PROCEDURE_SOURCE = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$RUNTIME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$ENGINE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$ENTRYPOINT = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$PACKAGE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
update RDB$PROCEDURES t set t.RDB$PRIVATE_FLAG = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
rollback;
(So, each DML really can update at least one row - we can see it in affected
records; and NO errors occured).
TEST-2
######
Recreate again new database and apply following:
---------------- start of script `test2.sql` ---------------
set term ^;
create procedure sp_dummy(a_id int) returns(o_txt varchar(20)) as
begin
o_txt = 'get id='||a_id;
suspend;
end
^
set term ;^
commit;
set count on; set echo on;
update RDB$PROCEDURES t set t.RDB$PROCEDURE_SOURCE = null where
coalesce(rdb$system_flag,0)=0 rows 1;
rollback;
update RDB$PROCEDURES t set t.RDB$RUNTIME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
rollback;
update RDB$PROCEDURES t set t.RDB$ENGINE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
rollback;
update RDB$PROCEDURES t set t.RDB$ENTRYPOINT = null where
coalesce(rdb$system_flag,0)=0 rows 1;
rollback;
update RDB$PROCEDURES t set t.RDB$PACKAGE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
rollback;
update RDB$PROCEDURES t set t.RDB$PRIVATE_FLAG = null where
coalesce(rdb$system_flag,0)=0 rows 1;
rollback;
---------------- start of script `test2.sql` ---------------
Run: isql.exe localhost/port:<path>\<file> -i test2.sql 1>result2.log 2>&1
Result-2:
#######
update RDB$PROCEDURES t set t.RDB$PROCEDURE_SOURCE = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Records affected: 1
rollback;
update RDB$PROCEDURES t set t.RDB$RUNTIME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Statement failed, SQLSTATE = 42000
UPDATE operation is not allowed for system table RDB$PROCEDURES
After line 13 in file test2.sql
Records affected: 0
rollback;
update RDB$PROCEDURES t set t.RDB$ENGINE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Statement failed, SQLSTATE = 42000
UPDATE operation is not allowed for system table RDB$PROCEDURES
After line 15 in file test2.sql
Records affected: 0
rollback;
update RDB$PROCEDURES t set t.RDB$ENTRYPOINT = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Statement failed, SQLSTATE = 42000
UPDATE operation is not allowed for system table RDB$PROCEDURES
After line 17 in file test2.sql
Records affected: 0
rollback;
update RDB$PROCEDURES t set t.RDB$PACKAGE_NAME = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Statement failed, SQLSTATE = 42000
UPDATE operation is not allowed for system table RDB$PROCEDURES
After line 19 in file test2.sql
Records affected: 0
rollback;
update RDB$PROCEDURES t set t.RDB$PRIVATE_FLAG = null where
coalesce(rdb$system_flag,0)=0 rows 1;
Statement failed, SQLSTATE = 42000
UPDATE operation is not allowed for system table RDB$PROCEDURES
After line 21 in file test2.sql
Records affected: 0
rollback;
How could 'rollback;' statements that are placed after each DML prohibit all
subsequent updates except first one ?
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
