When we cancel a query execution, the select continues running in the database.

[lscapim]

When we cancel a query execution, the select continues running in the database.
Would you know how we can solve this problem in Oracle?

When we use a direct connector in the database, such as Dbeaver or SQL Plus, the query is canceled immediately, but not in Redash.

[justinclift]

@snickerjp Any ideas? 😄

[snickerjp]

I looked at the documentation for python-oradcledb

There was the following description

Statement break/reset (see Connection.cancel())

Support
python-oracledb Thin Mode: Yes
python-oracledb Thick Mode: Yes

https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_a.html

I don't know how to implement it yet.😅

[snickerjp]

It appears to be already implemented.
However, I do not know why. Is it not working?

redash/redash/query_runner/oracle.py

Lines 179 to 180 in ec1c4d0

	except (KeyboardInterrupt, JobTimeoutException):
	connection.cancel()

[lscapim]

Hey,

That's it, we have the parameter but it doesn't work:

When we cancel a resulting large query, the cancellation does not work. And the process continues to run in the database

[lscapim]

@snickerjp Do you have any idea what could be happening?

[snickerjp]

@lscapim
I can't come up with a good way to do this.

In my environment, I created 100 million rows of sample data and ran the SQL.
I cancelled it in the middle and the cancellation worked.

Redash-Query-Stop-1.mp4

Environment:

• Database

  • Oracle Database 23c Free Edition on Ubuntu 23.10
  • Docker 25.0.2

• Redash

  • Redash previrew Version: 24.01.0-dev (dev) on Ubuntu 22.04.3 LTS
  • Docker 25.0.2

SQL:

select count(*) from OT.SAMPLE_EMPLOYEES

COUNT(*)
10,001,000

SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, 
       (SALARY * 0.07 + 100) / (SALARY * 0.03 + 200) AS COMPLEX_CALCULATION
FROM OT.SAMPLE_EMPLOYEES
ORDER BY COMPLEX_CALCULATION DESC;

Can you tell us your environment?
And can you tell us the steps to reproduce it?