Please provide ability to use FB profiler on DB created in dialect-1 #8880
Description
Please provide ability to use FB profiler for DB with dialect-1.
Currently attempt to run select rdb$profiler.start_session(...) fails with
Statement failed, SQLSTATE = HY000
Dynamic SQL Error
-SQL error code = -104
-Database SQL dialect 1 does not support reference to BIGINT datatype
-At function 'RDB$PROFILER.START_SESSION'
There is somewhat like 'hack' to resolve this problem but it looks very fragile: create empty DB in dialect-3, start/finish profiler and extract its metadata.
Then we can open this metadata in editor and change all bigint to int, and copy such DDL to the start of testing SQL.
For example, consider .sql from this attachment:
5x-dbd_1-with-precreated-profiler-tables.sql.zip
set sql dialect 1;
--create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
connect 'localhost:some_db_in_dialect_1' user 'sysdba' password 'masterkey';
. . .
create generator plg$prof_profile_id start with 1;
commit;
create table plg$prof_cursors (profile_id int /* hack for dialect-1 */ not null,
statement_id int not null,
cursor_id integer not null,
name char(63) character set utf8,
line_num integer,
column_num integer,
constraint plg$prof_cursors_pk primary key (profile_id, statement_id, cursor_id) using index plg$prof_cursors_profile_statement_cursor);
. . . other tables and views related to profiler . . .
create role plg$profiler;
grant delete, insert, select, update on plg$prof_cursors to role plg$profiler;
. . . other grants . . .
grant usage on sequence plg$prof_profile_id to role plg$profiler;
commit;
-- #####################
-- custom data and check
-- #####################
recreate view v_test as select count(*) as cnt from rdb$types,rdb$types;
commit;
set echo on;
select rdb$profiler.start_session('profile without "detailed_requests"') from rdb$database;
select * from v_test;
select * from v_test;
execute procedure rdb$profiler.finish_session(true);
commit;
set count on;
select
s.description,
v.*
from plg$prof_statement_stats_view v
join plg$prof_sessions s on s.profile_id = v.profile_id
where v.sql_text containing 'from v_test';
commit;
select rdb$profiler.start_session('profile with "detailed_requests"', null, null, null, 'DETAILED_REQUESTS') from rdb$database;
select * from v_test;
select * from v_test;
execute procedure rdb$profiler.finish_session(true);
commit;
set count on;
select
s.description,
v.*
from plg$prof_statement_stats_view v
join plg$prof_sessions s on s.profile_id = v.profile_id
where v.sql_text containing 'from v_test';
commit;
set count off;
set echo off;
select iif(1/3 = 0, 3, 1) as client_dialect, mon$sql_dialect as db_dialect from mon$database;
show version;
This "workaround" can not be considered as practically suitable: at least it will raise numeric overflow if profiler have to deal with data greater than 2^31.
PS.
Yes, it is known that dialect-1 does not support BIGINT datatype (HY000 "Client / Database SQL dialect 1 does not support reference to BIGINT datatype").
But we can see that several mon$ tables do have columns with such type:
show table mon$io_stats;
show table mon$record_stats;
show table mon$memory_usage;
So, may be there is ability to make similar "type extension" with profiler tables ?