Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Expression index can be created while doing inserts into table [CORE1746] #2170

Closed
firebird-automations opened this issue Feb 16, 2008 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Thomas Steinmaurer (tsteinmaurer)

Jira_subtask_outward CORE1780

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;

Commits: 48abd47 bdb54be d69c68a

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Open [ 1 ]

Target: 2.1 RC2, 2.0.4 [ 10250, 10211 ]

Fix Version: 2.1 RC2 [ 10250 ]

Fix Version: 2.0.4 [ 10211 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

For v2.5 i prepare more complex fix to avoid code dublication

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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).

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: No test => Deferred

Test Details: Waiting for reply on letter 10-may-2016 09:53 (sent to dimitr, hvlad): FB crash detected when doing "create index ..." while another session make inserts.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Fix is commited into 2.5.6, 3.0.1, 4.0 trees

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Version: 3.0.0 [ 10740 ]

Version: 2.5.5 [ 10670 ]

Version: 2.5.4 [ 10585 ]

Version: 2.5.3 Update 1 [ 10650 ]

Version: 2.5.3 [ 10461 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

Fix Version: 2.5.6 [ 10721 ]

Fix Version: 3.0.1 [ 10730 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Deferred => Done successfully

Test Details: Waiting for reply on letter 10-may-2016 09:53 (sent to dimitr, hvlad): FB crash detected when doing "create index ..." while another session make inserts. =>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment