Version: 3.0.3 [ 10810 ]

Version: 4.0 Alpha 1 [ 10731 ]

Component: Engine [ 10000 ]

summary: mon$statements can "skipor contains incorrect data in mon$state => mon$statements can "skip" record or contain incorrect data in mon$statement

summary: mon$statements can "skip" record or contain incorrect data in mon$statement => mon$statements can "skip" record or contain incorrect data in mon$state field

description: Prepare:

window-1: launch Python (fdb package must be added before this)
window-2: prepare script "monstt.sql", its content:

set list on;
set blob all;
set count on;
commit;
select 
     s\.mon$statement\_id
    ,s\.mon$transaction\_id
    ,s\.mon$state \|\| ', ' \|\| decode\( s\.mon$state, 0,'IDLE', 1,'RUNNING', 2,'STALLED'\) as mon\_state\_descr
    ,cast\(s\.mon$sql\_text as varchar\(80\)\) as sql\_text
from mon$statements s
    left join mon$transactions t on s\.mon$transaction\_id = t\.mon$transaction\_id
    left join mon$attachments a on s\.mon$attachment\_id = a\.mon$attachment\_id 
where a\.mon$attachment\_id \!= current\_connection
order by s\.mon$statement\_id
;

===
-- and launch ISQL with connect to empty database (say, 'localhost:e30')

EXPERIMENT-1
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

window-1 (in Python):
--------------
>>> import fdb
>>> con=fdb.connect(dsn='localhost:e30')
>>> print(con.firebird_version)
WI-V3.0.4.32980 Firebird 3.0
>>> sttm_arr=[ 'select cast( rand()*10000 as int ) from rdb$types rows %d' % (100+i) for i in range(1,5) ]
>>> sttm_arr
['select 1 from rdb$types rows 101', 'select 2 from rdb$types rows 102', 'select 3 from rdb$types rows 103', 'select 4 from rdb$types rows 104']
>>> curs_arr=[ con.cursor() for i in sttm_arr ]
>>> for k,s in enumerate( sttm_arr ):
... curs_arr[k].prep( s )
...
<fdb.fbcore.PreparedStatement object at 0x00E54B70>
<fdb.fbcore.PreparedStatement object at 0x00E54BF0>
<fdb.fbcore.PreparedStatement object at 0x00E54B70>
<fdb.fbcore.PreparedStatement object at 0x00E54BF0>

window-2 (in ISQL):
--------------
C:\MIX\firebird\QA\fbt-repo\tmp>isql /:e30 -n
Database: /:e30, User: SYSDBA
SQL> in monstt.sql;

MON$STATEMENT_ID 40
MON$TRANSACTION_ID <null>
MON_STATE_DESCR 0, IDLE
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 103

MON$STATEMENT_ID 41
MON$TRANSACTION_ID <null>
MON_STATE_DESCR 0, IDLE
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 104

Records affected: 2 --------------- WHY ? Where are records for statements with "... rows 101" ... and "102" ?

EXPERIMENT-2 (CONTINUE work in these windows)
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

window-1:
--------------
>>>
>>> for k,s in enumerate( sttm_arr ):
... curs_arr[k].execute( s )
...
<fdb.fbcore.Cursor object at 0x00E54A70>
<fdb.fbcore.Cursor object at 0x00E54A10>
<fdb.fbcore.Cursor object at 0x00E548F0>
<fdb.fbcore.Cursor object at 0x00E54A50>
>>>

window-2:
--------------

SQL> in monstt.sql;

MON$STATEMENT_ID 44
MON$TRANSACTION_ID 30
MON_STATE_DESCR 2, STALLED
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 101

MON$STATEMENT_ID 45
MON$TRANSACTION_ID 30
MON_STATE_DESCR 2, STALLED
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 102

MON$STATEMENT_ID 46
MON$TRANSACTION_ID 30
MON_STATE_DESCR 2, STALLED
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 103

MON$STATEMENT_ID 47
MON$TRANSACTION_ID <null>
MON_STATE_DESCR 0, IDLE
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 104

Records affected: 4

--- Why "MON_STATE_DESCR 0, IDLE" for statement with ".... rows 104" (i.e. LAST of executed) ?

EXPERIMENT-3 (CONTINUE work in these windows)
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

window-1:
--------------
>>> curs_res=[ c.fetchone() for c in curs_arr ]
>>> curs_res
[(1315,), (7762,), (5398,), (9983,)]
>>>

window-2:
--------------

SQL> in monstt.sql;

MON$STATEMENT_ID 44
MON$TRANSACTION_ID <null>
MON_STATE_DESCR 0, IDLE
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 101

MON$STATEMENT_ID 45
MON$TRANSACTION_ID <null>
MON_STATE_DESCR 0, IDLE
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 102

MON$STATEMENT_ID 46
MON$TRANSACTION_ID <null>
MON_STATE_DESCR 0, IDLE
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 103

MON$STATEMENT_ID 47
MON$TRANSACTION_ID <null>
MON_STATE_DESCR 0, IDLE
SQL_TEXT select cast( rand()*10000 as int ) from rdb$types rows 104

Records affected: 4
SQL>

-- WHY all of them now looks like "IDLE" ? IMO state should be =2 (STALLED) because client not yet finished fetching - isn't ?