Issue Details (XML | Word | Printable)

Key: CORE-3029
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Vlad Khorsun
Votes: 0
Watchers: 0
Operations

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

Bugcheck "Too many savepoints (287)" at rollback after exception at EXECUTE BLOCK with exception handler

Created: 03/Jun/10 10:57 AM   Updated: 28/Nov/13 01:45 PM
Component/s: Engine
Affects Version/s: 2.1.3, 2.5 RC2
Fix Version/s: 2.5.1, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
1. Prepare the schema

create sequence test_gen;

recreate table test_row
(id int not null,
 did int not null,
 pid int not null,
 dep int not null
);
alter table test_row add constraint pk_test_row primary key(id);
create unique index ix_test_row1 on test_row(did, pid, dep);
commit;

insert into test_row(id, did, pid,dep) values(1, 2, 3, 4);
commit;


2. test

execute block returns(id int, did int, dep int, pid int)
as
declare variable xid int;
begin
  select id,did, pid,dep
    from test_row
   where id=(select min(id) from test_row)
    into :xid, :did, pid, :dep;

  while (1=1) do
  begin
    delete from test_row r where r.id = :xid;
    
    insert into test_row(id, did, dep, pid)
    values (gen_id(test_gen, 1), :did, :dep, :pid);

    suspend;

  when any do
    exception;
  end
end

  Raised (expected) error:

Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values.
attempt to store duplicate value (visible to active transactions) in unique index "IX_TEST_ROW1".

After ROLLBACK we have

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
internal Firebird consistency check (Too many savepoints (287), file: tra.cpp line: 1341).



3. Create stored procedure with the same contents as EXECUTE BLOCK above

create or alter procedure sp_test
  returns(id int, did int, dep int, pid int)
as
declare variable xid int;
begin
  select id,did, pid,dep
    from test_row
   where id=(select min(id) from test_row)
    into :xid, :did, pid, :dep;

  while (1=1) do
  begin
    delete from test_row r where r.id = :xid;
    
    insert into test_row(id, did, dep, pid)
    values (gen_id(test_gen, 1), :did, :dep, :pid);

    suspend;

  when any do
    exception;
  end
end

4. select * from sp_test

  Raised (expected) error:

Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values.
attempt to store duplicate value (visible to active transactions) in unique index "IX_TEST_ROW1".
At procedure 'SP_TEST' line: 15, col: 5.
At procedure 'SP_TEST' line: 20, col: 12.

rollback is OK

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 08/Nov/10 04:40 PM
The fix is committed into both v2.5 and v3.0. Solution for v2.1 looks impossible at the moment.