nbenn opened a new issue, #4821:
URL: https://github.com/apache/arrow-adbc/issues/4821
In the PostgreSQL driver, `AdbcConnectionGetObjects()` does not return
temporary tables. With the driver built from `main` at f1378d6, against
PostgreSQL 17.11:
```r
library(adbcdrivermanager)
db <- adbc_database_init(
adbc_driver("adbc_driver_postgresql"),
uri = Sys.getenv("ADBC_POSTGRESQL_TEST_URI")
)
con <- adbc_connection_init(db)
stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "CREATE TEMPORARY TABLE tmp (a INTEGER)")
adbc_statement_execute_query(stmt)
#> [1] -1
objects <- nanoarrow::convert_array_stream(
adbc_connection_get_objects(con, depth = 3L, table_name = "tmp")
)
unlist(lapply(objects$catalog_db_schemas, function(schemas) {
unlist(lapply(schemas$db_schema_tables, function(tables)
tables$table_name))
}))
#> character(0)
```
The table lives in the session's temporary schema, `pg_temp_N`, and the
schema query leaves out every schema matching `^pg_`
([source](https://github.com/apache/arrow-adbc/blob/f1378d664b61ce65f25a54c3b863aa62ea9d2480/c/driver/postgresql/connection.cc#L74-L76)),
so the tables in it are never listed. Downstream, adbi's `dbListTables()`
omits the table and `dbExistsTable()` returns `FALSE` for it. The SQLite driver
had the same gap, reported in #1141 and fixed by #1603.
Admitting the session's own temporary schema fixes it:
```diff
static const char* kSchemaQueryAll =
"SELECT nspname FROM pg_catalog.pg_namespace WHERE "
- "nspname !~ '^pg_' AND nspname <> 'information_schema'";
+ "(nspname !~ '^pg_' OR oid = pg_my_temp_schema()) "
+ "AND nspname <> 'information_schema'";
```
With that change, the example above returns `"tmp"`, and adbi's
`dbListTables()` and `dbExistsTable()` find the table. A session without
temporary tables lists the same schemas as before, because
`pg_my_temp_schema()` returns 0 until the session creates one
([docs](https://www.postgresql.org/docs/current/functions-info.html)). I have
not run the driver's test suite with it. I can open a PR.
This report was drafted with an AI assistant; the code above was run as
shown.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
