Repository: duckdb/duckdb (language: python)

Issue:
`duckdb.duckdb.InvalidInputException: Invalid Input Error: PandasScan called but GIL was already held!` on recursive python calls
### What happens?

A code previously working until duckdb 1.0.0 started erroring out in duckdb 1.1 by raising following error:

```
    res = eval(code, gs, ns)
          ^^^^^^^^^^^^^^^^^^
  File "<string>", line 1, in <module>
duckdb.duckdb.InvalidInputException: Invalid Input Error: PandasScan called but GIL was already held!
```


### To Reproduce

```
import pandas as pd
import duckdb

get_data = lambda start, N=10: pd.DataFrame({'a':range(N), 'b':[f'{i+1}' for i in range(start, start+N)]})

tab1 = get_data(65)
tab2 = get_data(66)

db = duckdb.connect()
sql = '''
create table fixed_ids as
with recursive fanout as (
    SELECT c1.a as node_id, c2.a as comp_id2,
        from tab1 c1
        inner JOIN tab2 c2 on c1.b = c2.b
        inner JOIN tab2 c2l2 on c2.a = c2l2.a
    UNION all
    SELECT fo.comp_id2 as node_id, c2l1.b as comp_id2
        FROM tab1 c1 inner JOIN fanout fo ON fo.comp_id2 = c1.a
        inner JOIN tab2 c2l1 on c1.b = c2l1.b
)
SELECT node_id, first(comp_id2)::uint64 as fixed_id from fanout group by node_id;

SELECT * from fixed_ids
'''
if 0: # Enable to run sql statement-by-statement to workaround https://github.com/duckdb/duckdb/issues/13863
    stmts = db.extract_statements(sql)
    for stmt in stmts[:-1]:
        db.execute(stmt.query)
    sql = stmts[-1].query
print(db.execute(sql).df())
```



### OS:

RH8

### DuckDB Version:

1.1.0

### DuckDB Client:

python

### Hardware:

_No response_

### Full Name:

Pankaj Pandey

### Affiliation:

Cadence

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

Tested with duckdb 1.1 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:
Thanks for the report! A reproducer would be very helpful to track this down.
I have spent an inordinately long time to get a reproducible example!

1. The error happens in a recursive CTE when it it used to create a table and queried at the same time. Nothing to do with the "virtual computed table" implementation in OP (can update the description)
2. I was able to work around the error by splitting the multi-statement query into individual statements in my wrapper function:
```
import pandas as pd
import duckdb

get_data = lambda start, N=10: pd.DataFrame({'a':range(N), 'b':[f'{i+1}' for i in range(start, start+N)]})

tab1 = get_data(65)
tab2 = get_data(66)

db = duckdb.connect()
sql = '''
create table fixed_ids as
with recursive fanout as (
    SELECT c1.a as node_id, c2.a as comp_id2,
        from tab1 c1
        inner JOIN tab2 c2 on c1.b = c2.b
        inner JOIN tab2 c2l2 on c2.a = c2l2.a
    UNION all
    SELECT fo.comp_id2 as node_id, c2l1.b as comp_id2
        FROM tab1 c1 inner JOIN fanout fo ON fo.comp_id2 = c1.a
        inner JOIN tab2 c2l1 on c1.b = c2l1.b
)
SELECT node_id, first(comp_id2)::uint64 as fixed_id from fanout group by node_id;

SELECT * from fixed_ids
'''
if 0: # Enable to run sql statement-by-statement to workaround https://github.com/duckdb/duckdb/issues/13863
    stmts = db.extract_statements(sql)
    for stmt in stmts[:-1]:
        db.execute(stmt.query)
    sql = stmts[-1].query
print(db.execute(sql).df())

```

Thanks for the reproduction!
Especially the detail that multiple statements are run with a single `.execute` call will be helpful and gives me a good idea of where to search for the issue 👍 

Fix patch:
diff --git a/tools/pythonpkg/src/pyconnection.cpp b/tools/pythonpkg/src/pyconnection.cpp
index 9f3c92a0f91c..dfaa0092dc93 100644
--- a/tools/pythonpkg/src/pyconnection.cpp
+++ b/tools/pythonpkg/src/pyconnection.cpp
@@ -1396,6 +1396,7 @@ unique_ptr<DuckDBPyRelation> DuckDBPyConnection::ReadCSV(const py::object &name_
 
 void DuckDBPyConnection::ExecuteImmediately(vector<unique_ptr<SQLStatement>> statements) {
 	auto &connection = con.GetConnection();
+	py::gil_scoped_release release;
 	if (statements.empty()) {
 		return;
 	}


Test patch:
diff --git a/tools/pythonpkg/tests/fast/api/test_duckdb_execute.py b/tools/pythonpkg/tests/fast/api/test_duckdb_execute.py
index 4a4d95d5c297..fba01a0cf38e 100644
--- a/tools/pythonpkg/tests/fast/api/test_duckdb_execute.py
+++ b/tools/pythonpkg/tests/fast/api/test_duckdb_execute.py
@@ -74,3 +74,14 @@ def to_insert_from_generator(what):
         duckdb_cursor.execute("CREATE TABLE unittest_generator (a INTEGER);")
         duckdb_cursor.executemany("INSERT into unittest_generator (a) VALUES (?)", gen)
         assert duckdb_cursor.table('unittest_generator').fetchall() == [(1,), (2,), (3,)]
+
+    def test_execute_multiple_statements(self, duckdb_cursor):
+        pd = pytest.importorskip("pandas")
+        df = pd.DataFrame({'a': [5, 6, 7, 8]})
+        sql = """
+            select * from df;
+            select * from VALUES (1),(2),(3),(4) t(a);
+        """
+        duckdb_cursor.execute(sql)
+        res = duckdb_cursor.fetchall()
+        assert res == [(1,), (2,), (3,), (4,)]