Teach FB to immediatelly shutdown database when table with INDEXED field is
modified and that index key is calculated by some 'heavy' expression
------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4810
URL: http://tracker.firebirdsql.org/browse/CORE-4810
Project: Firebird Core
Issue Type: Improvement
Components: Engine, GFIX
Reporter: Pavel Zotov
Priority: Minor
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 (CORE-3858), 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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
