[I] Temporary tables are missing from `GetObjects` in the PostgreSQL driver [arrow-adbc]

["nbenn (via GitHub)" <gi...@apache.org>]

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: issues-unsubscribe@arrow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] Temporary tables are missing from `GetObjects` in the PostgreSQL driver [arrow-adbc]

["lidavidm (via GitHub)" <gi...@apache.org>]

lidavidm closed issue #4821: Temporary tables are missing from `GetObjects` in the PostgreSQL driver
URL: https://github.com/apache/arrow-adbc/issues/4821


-- 
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: issues-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org