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

Index operations for global temporary tables are not visible for the active connection [CORE1361] #1779

Closed
firebird-automations opened this issue Jul 16, 2007 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @dyemanov

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.

Commits: 8de5dd7

====== 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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1 Beta 2 [ 10190 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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.

@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

Workflow: jira [ 12594 ] => Firebird [ 14208 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> 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 ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully => Done with caveats

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