Issue Details (XML | Word | Printable)

Key: CORE-1361
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Dmitry Yemanov
Votes: 0
Watchers: 1
Operations

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

Index operations for global temporary tables are not visible for the active connection

Created: 16/Jul/07 10:35 AM   Updated: 27/May/15 04:43 PM
Component/s: Engine
Affects Version/s: 2.1 Alpha 1, 2.1 Beta 1
Fix Version/s: 2.1 Beta 2

QA Status: Done with caveats
Test Details:
Note: it was found that message
===
internal Firebird consistency check (invalid SEND request (167),
file: JrdStatement.cpp line: 325)
===
can appear in firebird.log, see comments in .fbt


 Description  « Hide
Neither CREATE INDEX nor SET STATISTICS statements affect the current connection. While it's logical for DELETE ROWS temporary tables, I'd expect the PRESERVE ROWS tables to take the changes into account, i.e. to use the index and to update the statistics.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 28/Aug/07 04:21 PM
Create index will create index instance in current connection.
Alter index and set statistics will work with index instance in current connection.
Drop index will delete index instance in current connection but will fail if there are instances of this index in another connections.

Pavel Zotov added a comment - 18/Apr/15 06:52 AM
> Create index will create index instance in ***CURRENT*** connection.

Is it correct that after such index will be created in the 'CURRENT' connection, all *SUBSEQUENT* connections will also see it and will use in their queries ?

Consider following script - it creates THREE attachments (in addition to starting one) inside ES/EDS by using new role name for each one:

set term ^;
execute block as
begin
  begin execute statement 'drop role poor_dba'; when any do begin end end
  begin execute statement 'drop role cool_dba'; when any do begin end end
  begin execute statement 'drop role super_dba'; when any do begin end end
end
^
set term ;^
commit;

recreate global temporary table gtt_session(x int, y int) on commit preserve rows;
commit;
create role poor_dba;
create role cool_dba;
create role super_dba;
commit;

grant poor_dba to sysdba;
grant cool_dba to sysdba;
grant super_dba to sysdba;
commit;

set transaction read committed;
set list on;
set blob all;
set term ^;
execute block returns(att_id int, att_role varchar(31), cnt int, sql_plan blob) as
   declare v_dbname type of column mon$database.mon$database_name;
   declare v_usr varchar(31) = 'SYSDBA';
   declare v_pwd varchar(31) = 'masterke';
   declare role_1 varchar(31) = 'POOR_DBA';
   declare role_2 varchar(31) = 'COOL_DBA';
   declare role_3 varchar(31) = 'SUPER_DBA';
begin

   v_dbname = 'localhost:' || rdb$get_context('SYSTEM', 'DB_NAME');

   execute statement 'insert into gtt_session select rand()*10, rand()*10 from rdb$types union all select -2, -3 from rdb$database'
   on external v_dbname
   as user v_usr password v_pwd role upper( role_1 );

   execute statement 'select current_connection,current_role,(select count(*) as cnt1 from gtt_session g where g.x + g.y = -5),mon$explained_plan from mon$statements s where s.mon$transaction_id = current_transaction'
   on external v_dbname
   as user v_usr password v_pwd role upper( role_1 )
   into att_id, att_role, cnt, sql_plan;
   suspend;

   --------------------------------------------------------

   execute statement 'create index gtt_session_x_y on gtt_session computed by ( x+y )'
   with autonomous transaction
   on external v_dbname
   as user v_usr password v_pwd role upper( role_2 );

   execute statement 'insert into gtt_session select rand()*10, rand()*10 from rdb$types union all select -2, -3 from rdb$database'
   on external v_dbname
   as user v_usr password v_pwd role upper( role_2 );
   
   execute statement 'select current_connection,current_role,(select count(*) as cnt2 from gtt_session g where g.x + g.y = -5),mon$explained_plan from mon$statements s where s.mon$transaction_id = current_transaction'
   on external v_dbname
   as user v_usr password v_pwd role upper( role_2 )
   into att_id, att_role, cnt, sql_plan;
   suspend;

/*
   execute statement 'alter index gtt_session_x_y inactive'
   with autonomous transaction
   on external v_dbname
   as user v_usr password v_pwd role upper( role_2 );
*/

   --------------------------------------------------------

   execute statement 'insert into gtt_session select rand()*10, rand()*10 from rdb$types union all select -2, -3 from rdb$database'
   on external v_dbname
   as user v_usr password v_pwd role upper( role_3 );

   execute statement 'select current_connection,current_role,(select count(*) as cnt2 from gtt_session g where g.x + g.y = -5),mon$explained_plan from mon$statements s where s.mon$transaction_id = current_transaction'
   on external v_dbname
   as user v_usr password v_pwd role upper( role_3 )
   into att_id, att_role, cnt, sql_plan;
   suspend;

end
^
set term ;^

Output:
======

ATT_ID 167
ATT_ROLE POOR_DBA
CNT 1
SQL_PLAN 0:1

Select Expression
    -> Singularity Check
        -> Aggregate
            -> Filter
                -> Table "GTT_SESSION" as "G" Full Scan
Select Expression
    -> Filter
        -> Table "MON$STATEMENTS" as "S" Full Scan

ATT_ID 169
ATT_ROLE COOL_DBA
CNT 1
SQL_PLAN 0:2

Select Expression
    -> Singularity Check
        -> Aggregate
            -> Filter
                -> Table "GTT_SESSION" as "G" Access By ID
                    -> Bitmap
                        -> Index "GTT_SESSION_X_Y" Range Scan (full match)
Select Expression
    -> Filter
        -> Table "MON$STATEMENTS" as "S" Full Scan

ATT_ID 170
ATT_ROLE SUPER_DBA
CNT 1
SQL_PLAN 0:3

Select Expression
    -> Singularity Check
        -> Aggregate
            -> Filter
                -> Table "GTT_SESSION" as "G" Access By ID
                    -> Bitmap
                        -> Index "GTT_SESSION_X_Y" Range Scan (full match)
Select Expression
    -> Filter
        -> Table "MON$STATEMENTS" as "S" Full Scan


Note: attachment #3 (as 'SUPER_DBA') *can* see index created in attachment #2.
It's OK ?

Vlad Khorsun added a comment - 18/Apr/15 07:28 AM
> Is it correct that after such index will be created in the 'CURRENT' connection, all *SUBSEQUENT* connections will also see it and will use in their queries ?

Sure. New connection creates own new instance of GTT using most current metadata, including new index.