Support `SHOW` command

[mcrumiller]

What feature or improvement would you like to see?

In postgres, the show command returns server/session configuration attributes:

SHOW all

The show command doesn't appear to be recognized by abdc:

>>> cur.execute("show all")    
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Projects\cqn-bur\.venv_cqn\Lib\site-packages\adbc_driver_manager\dbapi.py", line 698, in execute
    handle, self._rowcount = _blocking_call(
                             ^^^^^^^^^^^^^^^
  File "adbc_driver_manager\\_lib.pyx", line 1590, in adbc_driver_manager._lib._blocking_call
  File "adbc_driver_manager\\_lib.pyx", line 1213, in adbc_driver_manager._lib.AdbcStatement.execute_query
  File "adbc_driver_manager\\_lib.pyx", line 260, in adbc_driver_manager._lib.check_error
adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: [libpq] Failed to execute query: could not begin COPY: ERROR:  syntax error at or near "show"
LINE 1: COPY (show all) TO STDOUT (FORMAT binary)

[paleolimbot]

Thank you for reporting!

In the forthcoming release we'll have a workaround for queries not supported by COPY, although we probably need to do some work to integrate the option into dbapi. The workaround in the current development version might look like:

import adbc_driver_postgresql.dbapi

con = adbc_driver_postgresql.dbapi.connect(
    uri="postgresql://localhost:5432/postgres?user=postgres&password=password"
)

with con.cursor() as cur:
    cur.adbc_statement.set_options(**{"adbc.postgresql.use_copy": "false"})
    cur.execute("show all")
    df = cur.fetch_df()

df

In R the workaround would look like:

library(adbcdrivermanager)

con <- adbc_database_init(
  adbcpostgresql::adbcpostgresql(), 
  uri = "postgresql://localhost:5432/postgres?user=postgres&password=password"
) |> 
  adbc_connection_init()

out <- nanoarrow::nanoarrow_allocate_array_stream()
con |> 
  adbc_statement_init(
    "adbc.postgresql.use_copy" = "false"
  ) |> 
  adbc_statement_set_sql_query("SHOW ALL") |> 
  adbc_statement_execute_query(out)
#> [1] -1
tibble::as_tibble(out)
#> # A tibble: 362 × 3
#>    name                       setting      description                          
#>    <chr>                      <chr>        <chr>                                
#>  1 allow_in_place_tablespaces "off"        Allows tablespaces directly inside p…
#>  2 allow_system_table_mods    "off"        Allows modifications of the structur…
#>  3 application_name           ""           Sets the application name to be repo…
#>  4 archive_cleanup_command    ""           Sets the shell command that will be …
#>  5 archive_command            "(disabled)" Sets the shell command that will be …
#>  6 archive_library            ""           Sets the library that will be called…
#>  7 archive_mode               "off"        Allows archiving of WAL files using …
#>  8 archive_timeout            "0"          Sets the amount of time to wait befo…
#>  9 array_nulls                "on"         Enable input of NULL elements in arr…
#> 10 authentication_timeout     "1min"       Sets the maximum allowed time to com…
#> # ℹ 352 more rows

^{Created on 2024-08-21 with reprex v2.1.1}

[mcrumiller]

Thank you!