Issue Details (XML | Word | Printable)

Key: CORE-1746
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Thomas Steinmaurer
Votes: 0
Watchers: 3
Operations

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

Expression index can be created while doing inserts into table

Created: 16/Feb/08 03:09 PM   Updated: 16/May/15 10:30 AM
Component/s: Engine
Affects Version/s: 2.0.3, 2.1 RC1
Fix Version/s: 2.1 RC2, 2.0.4

Environment: Windows XP Prof. SP 2, Firebird Classic Server

Target: 2.1 RC2 and 2.0.4

Sub-Tasks  All   Open   

 Description  « Hide
when I'm trying to activate an index while inserting records into the
same table, I get an object in use error message.This does not happen
when activating an expression index though. Why is there a difference
and am I asking for troubles (e.g. corrupted index) when activating an
expression index while doing bulk inserts?

Test case:

1) Create metadata

CREATE GENERATOR T1_GEN;

SET TERM ^^ ;
CREATE PROCEDURE P_FILL_T1 (
  NR_OF_RECORDS BigInt) AS
BEGIN
  SUSPEND;
END
 ^^
SET TERM ; ^^

CREATE TABLE T1
(
  T1_ID BIGINT NOT NULL,
  VC1 CHAR( 1) COLLATE NONE,
 CONSTRAINT PK_T1 PRIMARY KEY (T1_ID)
);

SET TERM ^^ ;
ALTER PROCEDURE P_FILL_T1 (
  NR_OF_RECORDS BigInt) AS
declare variable i integer;
begin
  i = 1;
  while (i <= nr_of_records) do
  begin
    insert into t1 (t1_id, vc1) values (gen_id(t1_gen, 1), 'a');
    i = i + 1;
  end
end ^^
SET TERM ; ^^

commit;

2) Execute SP

execute procedure p_fill_t1 (500000);


3) Create index in another database session

While the procedure is still running, the following index creation will fail at commit time, which is, according to Vlad and Dmitry OK.

create index i_t1_vc1 on t1 (vc1);
commit;

While the procedure is still running, the following expression index creation will succeed, which is, according to Vlad and Dmitry a BUG.

create index i_t1_vc1_expression on t1 computed by (upper(vc1));
commit;



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 08/Mar/08 10:04 AM
For v2.5 i prepare more complex fix to avoid code dublication

Pavel Zotov added a comment - 16/May/15 10:30 AM
Following script:

show version;

create table test(id int primary key using index test_pk, x int, y int);
commit;

set transaction read committed no wait;

insert into test(id, x, y)
with recursive r as (select 0 i from rdb$database union all select r.i+1 from r where r.i < 99)
select r1.i*100 + r2.i + 1, rand()*1000, rand()*1000
from r r1, r r2;

set term ^;
execute block as
begin

  execute statement 'create unique index test_idx1 on test computed by (id)'
  with autonomous transaction
  on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME')
  as user 'sysdba' password 'masterke' role 'R001';

  execute statement 'create descending index test_idx2 on test computed by(x+y)'
  with autonomous transaction
  on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME')
  as user 'sysdba' password 'masterke' role 'R002';


  execute statement 'create descending index test_idx_non_comp on test (x,y)'
  with autonomous transaction
  on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME')
  as user 'sysdba' password 'masterke' role 'R003';

end
^
set term ;^

set list on;
select
  rdb$index_name
  ,rdb$index_id
  ,rdb$unique_flag
  ,rdb$index_inactive
  ,rdb$expression_source
from rdb$indices ri
where ri.rdb$relation_name = upper('test');

rollback;

show index;

-- will produce:

1) STDOUT:

RDB$INDEX_NAME TEST_PK
RDB$INDEX_ID 1
RDB$UNIQUE_FLAG 1
RDB$INDEX_INACTIVE <null>
RDB$EXPRESSION_SOURCE <null>

RDB$INDEX_NAME TEST_IDX1
RDB$INDEX_ID 2
RDB$UNIQUE_FLAG 1
RDB$INDEX_INACTIVE <null>
RDB$EXPRESSION_SOURCE 4:1df
(id)

RDB$INDEX_NAME TEST_IDX2
RDB$INDEX_ID 3
RDB$UNIQUE_FLAG 0
RDB$INDEX_INACTIVE <null>
RDB$EXPRESSION_SOURCE 4:1e1
(x+y)


TEST_IDX1 UNIQUE INDEX ON TEST COMPUTED BY (id)
TEST_IDX2 DESCENDING INDEX ON TEST COMPUTED BY (x+y)
TEST_PK UNIQUE INDEX ON TEST(ID)

2) STDERR:
Statement failed, SQLSTATE = 42000
Execute statement error at transaction commit :
335544345 : lock conflict on no wait transaction
335544351 : unsuccessful metadata update
335544453 : object TABLE "TEST" is in use


both on WI-V2.5.5.26870 and WI-T3.0.0.31836.

This mean that expression index still CAN be created while another attach keeps table in use (unlike common index, see STDERR).