search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.26k stars 216 forks source link

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

Open firebird-automations opened 9 years ago

firebird-automations commented 9 years ago

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 commented 9 years ago
Modified by: Sean Leyne (seanleyne) Link: This issue is related to [CORE3858](https://github.com/FirebirdSQL/firebird/issues?q=CORE3858+in%3Atitle) \[ [CORE3858](https://github.com/FirebirdSQL/firebird/issues?q=CORE3858+in%3Atitle) \]
firebird-automations commented 9 years ago

Commented by: Sean Leyne (seanleyne)

Edited "Summary" for readability

firebird-automations commented 9 years ago
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](https://github.com/FirebirdSQL/firebird/issues?q=CORE3858+in%3Atitle)\), 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](https://github.com/FirebirdSQL/firebird/issues?q=CORE3858+in%3Atitle)\), 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 commented 9 years ago

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.