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

Server ignores asynchronous (monitoring or cancellation) requests while preparing a query with lot of windowed functions [CORE4292] #4615

Closed
firebird-issue-importer opened this issue Dec 7, 2013 · 10 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Dec 7, 2013

Submitted by: @pavel-zotov

Attachments:
prepare_too_long_and_hangs_monitoring.zip

Consider two complex queries - see attach.
Script "prepare_too_long_and_hangs_monitoring.sql" contains a lot of calls to windowed functions and script "also_complex_but_NOT_trouble_in_preparing.sql" has no such calls but contains lot of scalar subqueries (in places where windowed functions were in first one).

The following scenario illustrates what there will be if we try to get execution plan of both queries.

session #⁠1

C:\1Install\fb30>isql localhost/3330:empty30 -n
Database: localhost/3330:empty30
SQL>

session #⁠2

C:\1Install\fb30>isql localhost/3330:empty30 -n
Database: localhost/3330:empty30
SQL> in prepare_too_long_and_hangs_monitoring.sql;
-- hangs (preparing takes about five minutes on my Linux server)

session #⁠1

SQL> set list on;
SQL> commit; select * from mon$attachments where mon$attachment_id<>current_connection
CON> and mon$remote_protocol > is not null;
-- ALSO HANGS untill execution plan will appear in session #⁠2!

So, we can not querying MON$ATTACHMENTS during five minutes. And any other MON$-tables too.

PS. Script "also_complex_but_NOT_trouble_in_preparing.sql" does not leads to such trouble though it also too comples and also have lot of "calls" inside itself.

Commits: 68e2043 FirebirdSQL/fbt-repository@9317da9

====== Test Details ======

Checked on WI-V3.0.0.32081, SS / SC / CS (32 bit). Result: all fine.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 7, 2013

Modified by: @pavel-zotov

Attachment: prepare_too_long_and_hangs_monitoring.zip [ 12390 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 8, 2013

Modified by: @dyemanov

summary: MON$-tables are unavaliable when optimizer prepares to execute query with lot of windowed functions => Server ignores asynchronous (monitoring or cancellation) requests while preparing a query with lot of windowed functions

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 9, 2014

Modified by: @dyemanov

Regression: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 17, 2014

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 5, 2015

Modified by: @dyemanov

Fix Version: 3.0 RC 1 [ 10584 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 30, 2015

Modified by: @asfernandes

assignee: Dmitry Yemanov [ dimitr ] => Adriano dos Santos Fernandes [ asfernandes ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 8, 2015

Commented by: @pavel-zotov

Still can`t understand: why attachment that preparing complex query too long and than is killed by another, does not quits from ISQL.

Session #⁠1
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
isql /3333:e30 -q

SQL>

Session #⁠2
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

isql /3333:e30 -q

SQL> in prepare_too_long_and_hangs_monitoring.sql ; -- this file you can see in attached .zip
SQL> _ -- now give this attachment to work about 1-2 minutes

Session #⁠1
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

SQL> commit; select * from mon$attachments where mon$remote_address is not null and mon$attachment_id<>current_connection;

MON$ATTACHMENT_ID 9
MON$SERVER_PID 2088
MON$STATE 0
MON$ATTACHMENT_NAME e30
MON$USER SYSDBA
MON$ROLE NONE
MON$REMOTE_PROTOCOL TCPv4
MON$REMOTE_ADDRESS 192.168.43.154
MON$REMOTE_PID 5400
MON$CHARACTER_SET_ID 0
MON$TIMESTAMP 2015-10-08 15:54:12.6380
MON$GARBAGE_COLLECTION 1
MON$REMOTE_PROCESS C:\MIX\Firebird\fb30\isql.exe
MON$STAT_ID 20
MON$CLIENT_VERSION WI-V3.0.0.32067 Firebird 3.0 Release Candidate 1
MON$REMOTE_VERSION P13
MON$REMOTE_HOST csprog
MON$REMOTE_OS_USER zotov
MON$AUTH_METHOD Legacy_Auth
MON$SYSTEM_FLAG 0

SQL> commit; delete from mon$attachments;
SQL> commit; select * from mon$attachments where mon$remote_address is not null and mon$attachment_id<>current_connection;
SQL>

Session #⁠2
*** still hangs *** (no message about connection lost etc, no quit from ISQL)

PS.

Checked on SuperServer.

ISQL Version: WI-V3.0.0.32067 Firebird 3.0 Release Candidate 1
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-V3.0.0.32067 Firebird 3.0 Release Candidate 1"
Firebird/Windows/Intel/i386 (remote server), version "WI-V3.0.0.32067 Firebird 3.0 Release Candidate 1/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-V3.0.0.32067 Firebird 3.0 Release Candidate 1/tcp (csprog)/P13"
on disk structure version 12.0

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 8, 2015

Commented by: @dyemanov

Asynchronous cancellation usually don't affect queries being prepared. One particular case (for this ticket) was improved, but no general solution exists yet.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 11, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

Test Details: Checked on WI-V3.0.0.32081, SS / SC / CS (32 bit). Result: all fine.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 11, 2015

Modified by: @pavel-zotov

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

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

No branches or pull requests

2 participants