Repository: duckdb/duckdb (language: python)

Issue:
create_function -> Invalid Input Error: This function can not be called with an active transaction!, commit or abort the existing one first
### What happens?

  ```
conn = duckdb.connect(database=':memory:')
conn.execute(f"SELECT installed,loaded FROM duckdb_extensions() ;").fetchone()   
conn.create_function("line_3point_min_angle",CheckUdf.line_3point_min_angle )
```
before create_function,fetchone will be error 
`    conn.create_function(attr_name, attr_value,
duckdb.duckdb.InvalidInputException: Invalid Input Error: This function can not be called with an active transaction!, commit or abort the existing one first`

but fetchall()  not error
```
 conn = duckdb.connect(database=':memory:')
conn.execute(f"SELECT installed,loaded FROM duckdb_extensions() ;").fetchall()
conn.create_function("line_3point_min_angle",CheckUdf.line_3point_min_angle )

```



### To Reproduce

```
    conn = duckdb.connect(database=':memory:')
    conn.execute(f"SELECT installed,loaded FROM duckdb_extensions() ;").fetchone()
    conn.create_function(CheckUdf.line_3point_min_angle, "line_3point_min_angle")
```
fetchone() then create_function will be error
```
conn.create_function(attr_name, attr_value,
duckdb.duckdb.InvalidInputException: Invalid Input Error: This function can not be called with an active transaction!, commit or abort the existing one first

```
but fetchall() not error
```
    conn = duckdb.connect(database=':memory:')
    conn.execute(f"SELECT installed,loaded FROM duckdb_extensions() ;").fetchall()
    conn.create_function(CheckUdf.line_3point_min_angle, "line_3point_min_angle")
```

### OS:

MACOS

### DuckDB Version:

1.0.0

### DuckDB Client:

python

### Full Name:

s

### Affiliation:

l

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
Fetchone() creates a stream, an active query execution which is wrapped in a transaction.

Perhaps we should just cancel the active query execution + transaction instead of letting this fail

Fix patch:
diff --git a/src/include/duckdb/main/client_context.hpp b/src/include/duckdb/main/client_context.hpp
index b7aa81fc3cf6..c03e38ae0636 100644
--- a/src/include/duckdb/main/client_context.hpp
+++ b/src/include/duckdb/main/client_context.hpp
@@ -94,6 +94,7 @@ class ClientContext : public enable_shared_from_this<ClientContext> {
 
 	//! Interrupt execution of a query
 	DUCKDB_API void Interrupt();
+	DUCKDB_API void CancelTransaction();
 
 	//! Enable query profiling
 	DUCKDB_API void EnableProfiling();
diff --git a/src/main/client_context.cpp b/src/main/client_context.cpp
index f98ee9753f32..a413a4ef0812 100644
--- a/src/main/client_context.cpp
+++ b/src/main/client_context.cpp
@@ -1030,6 +1030,11 @@ void ClientContext::Interrupt() {
 	interrupted = true;
 }
 
+void ClientContext::CancelTransaction() {
+	auto lock = LockContext();
+	InitialCleanup(*lock);
+}
+
 void ClientContext::EnableProfiling() {
 	auto lock = LockContext();
 	auto &client_config = ClientConfig::GetConfig(*this);
diff --git a/tools/pythonpkg/src/pyconnection.cpp b/tools/pythonpkg/src/pyconnection.cpp
index 05586c7c4d80..8fffa355fce3 100644
--- a/tools/pythonpkg/src/pyconnection.cpp
+++ b/tools/pythonpkg/src/pyconnection.cpp
@@ -376,8 +376,7 @@ DuckDBPyConnection::RegisterScalarUDF(const string &name, const py::function &ud
 	auto &context = *connection.context;
 
 	if (context.transaction.HasActiveTransaction()) {
-		throw InvalidInputException(
-		    "This function can not be called with an active transaction!, commit or abort the existing one first");
+		context.CancelTransaction();
 	}
 	if (registered_functions.find(name) != registered_functions.end()) {
 		throw NotImplementedException("A function by the name of '%s' is already created, creating multiple "


Test patch:
diff --git a/tools/pythonpkg/tests/fast/udf/test_scalar.py b/tools/pythonpkg/tests/fast/udf/test_scalar.py
index 65f38bb2f864..5a13b1e2dedd 100644
--- a/tools/pythonpkg/tests/fast/udf/test_scalar.py
+++ b/tools/pythonpkg/tests/fast/udf/test_scalar.py
@@ -291,19 +291,9 @@ def func(x: int) -> int:
         # Using fetchone keeps the result open, with a transaction
         rel.fetchone()
 
-        # If we would allow a UDF to be created when a transaction is active
-        # then starting a new result-fetch would cancel the transaction
-        # which would corrupt our internal mechanism used to check if a UDF is already registered
-        # because that isn't transaction-aware
-        with pytest.raises(
-            duckdb.InvalidInputException,
-            match='This function can not be called with an active transaction!, commit or abort the existing one first',
-        ):
-            con.create_function('func', func)
+        con.create_function('func', func)
 
-        # This would cancel the previous transaction, causing the function to no longer exist
         rel.fetchall()
 
-        con.create_function('func', func)
         res = con.sql('select func(5)').fetchall()
         assert res == [(5,)]
diff --git a/tools/pythonpkg/tests/fast/udf/test_transactionality.py b/tools/pythonpkg/tests/fast/udf/test_transactionality.py
new file mode 100644
index 000000000000..50286e8eb4ba
--- /dev/null
+++ b/tools/pythonpkg/tests/fast/udf/test_transactionality.py
@@ -0,0 +1,18 @@
+import duckdb
+import pytest
+
+
+class TestUDFTransactionality(object):
+    @pytest.mark.xfail(reason='fetchone() does not realize the stream result was closed before completion')
+    def test_type_coverage(self, duckdb_cursor):
+        rel = duckdb_cursor.sql("select * from range(4096)")
+        res = rel.fetchone()
+        assert res == (0,)
+
+        def my_func(x: str) -> int:
+            return int(x)
+
+        duckdb_cursor.create_function('test', my_func)
+
+        with pytest.raises(duckdb.InvalidInputException, match='result closed'):
+            res = rel.fetchone()