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

Cannot shutdown database with operations running against table that has indexed COMPUTED BY 'heavy' expression field [CORE4810] #5108

Open
firebird-automations opened this issue May 22, 2015 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Is related to CORE3858

Rare case, but may be can appear.

Test-1.
#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test( text varchar(255), pattern varchar(255), ecs char(1) );
commit;
create index test_death on test computed by ( iif( text similar to pattern escape '\', 1, 0 ) );
commit;
insert into test( text, pattern ) values( 'sdf--asdf_sd.dsfsfh-.sdjskdjfh-_.', '(([[:ALNUM:]\_\-])+.?)+([[:ALNUM:]\_\-])+' );

-- or --

create table test(
text varchar(255),
pattern varchar(255),
match_result computed by ( iif( text similar to pattern escape '\', 1, 0 ) )
);
commit;
create index test_death2 on test computed by ( match_result );
commit;
insert into test( text, pattern ) values( 'sdf--asdf_sd.dsfsfh-.sdjskdjfh-_.', '(([[:ALNUM:]\_\-])+.?)+([[:ALNUM:]\_\-])+' );

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: you will wait several minutes in Window-2 untill Window-1 finished its statement.

Yes, this 'magic' text & pattern were taken from one of my tickets related to poor performance of similar to (CORE3858), but the same will be in case when computed-by index has a key based on some expression that DOES query to database.

Compare following:

Test-2:
#⁠#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test(x int); commit;
insert into test values( (select count(*) from rdb$types a,rdb$types b,rdb$types c) ); -- note: NO 'heavy-computed' index here

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: you will NOT wait, database will be in shutdown mode immediatelly. An this is because there is no such index as in test-3:

Test-3:
#⁠#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test(x int); commit;
create index test_death on test computed by ( x + (select count(*) from rdb$types a,rdb$types b,rdb$types c) );
commit;
insert into test values(1);

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: WAIT again untill Window-1 will finished its work. But note: here evaluation of index expression has to query database, so one might to suppose that shutdown process will be much faster than in Test-1.
Furthermore, when Window-1 finishes, there is no message like

SQL> Statement failed, SQLSTATE = 08003
connection shutdown
Statement failed, SQLSTATE = 08006
Error writing data to the connection.

-- it just silently returns to ISQL prompt.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE3858 [ CORE3858 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Edited "Summary" for readability

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: Rare case, but may be can appear.

Test-1.
#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test( text varchar(255), pattern varchar(255), ecs char(1) );
commit;
create index test_death on test computed by ( iif( text similar to pattern escape '\', 1, 0 ) );
commit;
insert into test( text, pattern ) values( 'sdf--asdf_sd.dsfsfh-.sdjskdjfh-_.', '(([[:ALNUM:]\_\-])+.?)+([[:ALNUM:]\_\-])+' );

-- or --

create table test(
text varchar(255),
pattern varchar(255),
match_result computed by ( iif( text similar to pattern escape '\', 1, 0 ) )
);
commit;
create index test_death2 on test computed by ( match_result );
commit;
insert into test( text, pattern ) values( 'sdf--asdf_sd.dsfsfh-.sdjskdjfh-_.', '(([[:ALNUM:]\_\-])+.?)+([[:ALNUM:]\_\-])+' );

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: you will wait several minutes in Window-2 untill Window-1 finished its statement.

Yes, this 'magic' text & pattern were taken from one of my tickets related to poor performance of similar to (CORE3858), but the same will be in case when computed-by index has a key based on some expression that DOES query to database.

Compare following:

Test-2:
#⁠#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test(x int); commit;
insert into test values( (select count(*) from rdb$types a,rdb$types b,rdb$types c) ); -- note: NO 'heavy-computed' index here

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: you will NOT wait, database will be in shutdown mode immediatelly. An this is because there is no such index as in test-3:

Test-3:
#⁠#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test(x int); commit;
create index test_death on test computed by ( x + (select count(*) from rdb$types a,rdb$types b,rdb$types c) );
commit;
insert into test values(1);

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: WAIT again untill Window-1 will finished its work. But note: here evaluation of index expression has to query database, so one might to suppose that shutdown process will be much faster than in Test-1.
Furthermore, when Window-1 finishes, there is no message like

SQL> Statement failed, SQLSTATE = 08003
connection shutdown
Statement failed, SQLSTATE = 08006
Error writing data to the connection.

-- it just silently returns to ISQL prompt.

=>

Rare case, but may be can appear.

Test-1.
#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test( text varchar(255), pattern varchar(255), ecs char(1) );
commit;
create index test_death on test computed by ( iif( text similar to pattern escape '\', 1, 0 ) );
commit;
insert into test( text, pattern ) values( 'sdf--asdf_sd.dsfsfh-.sdjskdjfh-_.', '(([[:ALNUM:]\_\-])+.?)+([[:ALNUM:]\_\-])+' );

-- or --

create table test(
text varchar(255),
pattern varchar(255),
match_result computed by ( iif( text similar to pattern escape '\', 1, 0 ) )
);
commit;
create index test_death2 on test computed by ( match_result );
commit;
insert into test( text, pattern ) values( 'sdf--asdf_sd.dsfsfh-.sdjskdjfh-_.', '(([[:ALNUM:]\_\-])+.?)+([[:ALNUM:]\_\-])+' );

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: you will wait several minutes in Window-2 untill Window-1 finished its statement.

Yes, this 'magic' text & pattern were taken from one of my tickets related to poor performance of similar to (CORE3858), but the same will be in case when computed-by index has a key based on some expression that DOES query to database.

Compare following:

Test-2:
#⁠#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test(x int); commit;
insert into test values( (select count(*) from rdb$types a,rdb$types b,rdb$types c) ); -- note: NO 'heavy-computed' index here

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: you will NOT wait, database will be in shutdown mode immediatelly. An this is because there is no such index as in test-3:

Test-3:
#⁠#⁠#⁠#⁠#⁠#⁠

Window-1:

recreate table test(x int); commit;
create index test_death on test computed by ( x + (select count(*) from rdb$types a,rdb$types b,rdb$types c) );
commit;
insert into test values(1);

Window-2:

C:\MIX\firebird\fb30\gfix.exe -shut full -force 0 host/port:alias

Result: WAIT again untill Window-1 will finished its work. But note: here evaluation of index expression has to query database, so one might to suppose that shutdown process will be much faster than in Test-1.
Furthermore, when Window-1 finishes, there is no message like

SQL> Statement failed, SQLSTATE = 08003
connection shutdown
Statement failed, SQLSTATE = 08006
Error writing data to the connection.

-- it just silently returns to ISQL prompt.

summary: Teach FB to immediatelly shutdown database when table with INDEXED field is modified and that index key is calculated by some 'heavy' expression => Cannot shutdown database with operations running against table that has indexed COMPUTED BY 'heavy' expression field

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

PS.

Samples in this ticket were created after reading in CORE3034:

The main bug is that JRD_reschedule() should not be called if thread holds any LM's lock ! So, bug exists al long as expression indices was enabled.

I couldn't get "Bugcheсk 300 (can't find shared latch)" in log - perhaps because gfix can`t interrupt process of inserting key into such index.

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

No branches or pull requests

1 participant