potential regression for duckdb 1.5.2 (or earlier) and postgresql temporary tables

[wrobell]

In duckdb 1.3.1 creating PostgreSQL temporary table and then accessing it works with

In [5]: cn.execute("call postgres_execute('db', 'create temporary table t1 (a integer)')")
Out[5]: <duckdb.duckdb.DuckDBPyConnection at 0x7f970a88f9b0>

In [6]: cn.execute("select * from db.pg_temp.t1").fetchall()
Out[6]: []

In duckdb 1.5.2, we get error

In [7]: cn.execute("select * from db.pg_temp.t1").fetchall()
---------------------------------------------------------------------------
CatalogException                          Traceback (most recent call last)
Cell In[7], line 1
----> 1 cn.execute("select * from db.pg_temp.t1").fetchall()

CatalogException: Catalog Error: Table with name t1 does not exist!
Did you mean "db.information_schema.tables or information_schema.tables"?

LINE 1: select * from db.pg_temp.t1                   ^

[staticlibs]

Hi, thanks for the report! I believe this is working as expected - temporary table is only visible from the Postgres connection that has created it. But one DuckDB connection can use multiple Postgres connections - in both parallel scans and in sequential queries.

What can work reliably is to disable the worker threads and to start a transaction to have hold a Postgres connection, then temporary table will be visible. It will be gone after the transaction is over:

DuckDB v1.5.2 (Variegata)
Enter ".help" for usage hints.
memory D SET threads = 1;
memory D ATTACH 'postgres:postgresql://postgres:postgres@127.0.0.1:5432/test1' AS p1;
memory D BEGIN TRANSACTION;
memory D FROM postgres_execute('p1', 'CREATE TEMP TABLE tab1(col1 int)');
┌─────────┐
│ Success │
│ boolean │
└─────────┘
  0 rows 
memory D FROM p1.pg_temp.tab1;
┌────────┐
│  col1  │
│ int32  │
└────────┘
  0 rows
memory D INSERT INTO p1.pg_temp.tab1 VALUES(41), (42);
memory D FROM p1.pg_temp.tab1;
┌───────┐
│ col1  │
│ int32 │
├───────┤
│    41 │
│    42 │
└───────┘
memory D FROM postgres_query('p1', 'SELECT * FROM tab1');
┌───────┐
│ col1  │
│ int32 │
├───────┤
│    41 │
│    42 │
└───────┘
memory D ROLLBACK;
memory D FROM postgres_query('p1', 'SELECT * FROM tab1');
Binder Error:
Failed to prepare query "SELECT * FROM tab1": ERROR:  relation "tab1" does not exist
LINE 1: SELECT * FROM tab1
                      ^


LINE 1: FROM postgres_query('p1', 'SELECT * FROM tab1');

To have some control over the Postgres session state we need to implement support for direct connection handling, like it is done in the ODBC extension, but we currently don't have it in this extension.

Closing this as answered, please leave a comment if this needs to be reopened.

[wrobell]

@staticlibs @Mytherin This works indeed. Thank you.

I hope you do not mind me pointing out that searching for pg_temp in the documentation yields nothing.