User defined ENUM types are `NA`

[andreranza]

library(DBI)
library(RPostgres)

# Connection -------------------------------------------------------------------

creds <- c(
  user = "postgres",
  psw = "postgres",
  host = "localhost"
)

con <- dbConnect(
  Postgres(),
  dbname = "postgres",
  user = creds[["user"]],
  password = creds[["psw"]],
  host = creds[["host"]]
)

# Create custom user enum type -------------------------------------------------

q_drop <- "DROP TYPE IF EXISTS \"UserRole\" CASCADE;"
dbExecute(con, q_drop)
#> NOTICE:  drop cascades to column role of table sample_users
#> [1] 0

q_create_typ <- "CREATE TYPE \"UserRole\" AS ENUM ('user', 'admin');"
dbExecute(con, q_create_typ)
#> [1] 0

# Create table -----------------------------------------------------------------

dbExecute(con, "DROP TABLE IF EXISTS sample_users")
#> [1] 0
q_create_tbl <- "
CREATE TABLE sample_users (
  role \"UserRole\" NOT NULL DEFAULT 'user'
);"
dbExecute(con, q_create_tbl)
#> [1] 0

# Check How Custom Type is represented correctly in DB -------------------------

q_list_types <- "
SELECT
    t.typname AS type_name,
    CASE
        WHEN t.typtype = 'e' THEN 'ENUM'
        WHEN t.typtype = 'c' THEN 'COMPOSITE'
        WHEN t.typtype = 'd' THEN 'DOMAIN'
        WHEN t.typtype = 'r' THEN 'RANGE'
        WHEN t.typtype = 'm' THEN 'MULTIRANGE'
        ELSE t.typtype::text
    END AS type_category,
    n.nspname AS schema_name
FROM
    pg_type t
JOIN
    pg_namespace n ON t.typnamespace = n.oid
WHERE
    -- Filter for user-defined types (not system types)
    n.nspname NOT IN ('pg_catalog', 'information_schema')
    -- Exclude array types and implicit types
    AND t.typelem = 0
    AND t.typtype NOT IN ('p')
ORDER BY
    schema_name,
    type_name;
"

dbGetQuery(con, q_list_types)
#>      type_name type_category schema_name
#> 1     UserRole          ENUM      public
#> 2 sample_users     COMPOSITE      public

# Insert data ------------------------------------------------------------------

q_ins <- "INSERT INTO sample_users (role)
VALUES ('admin'), ('user');"

dbExecute(con, q_ins)
#> [1] 2

out <- dbGetQuery(con, "SELECT * FROM sample_users;")

# Issue (1) --------------------------------------------------------------------

# pq_NA rather than pq_UserRole
constructive::construct(out)
#> list(
#>   role = c("admin", "user") |>
#>     structure(class = "pq_NA")
#> ) |>
#>   structure(row.names = c(NA, -2L), class = "data.frame")

# `match()` call returning NA in debug(RPostgres:::type_lookup)
# Apparently, the oid in the typnames data.frame from `ret` does not match with the one from con.
# UserRole it's there but with a different oid

# Issue (2) --------------------------------------------------------------------

# derived from issue 1: cannot use dplyr::rows_insert()
dbExecute(con, "DROP TABLE IF EXISTS sample_users")
#> [1] 0
q_create_tbl <- "
CREATE TABLE sample_users (
  role \"UserRole\" NOT NULL DEFAULT 'user'
);"
dbExecute(con, q_create_tbl)
#> [1] 0
target <- dplyr::tbl(con, "sample_users")

source <- tibble::tibble(
  role = c("admin", "user") |>
    structure(class = "pq_UserRole")
)

try(dplyr::rows_insert(target, source, conflict = "ignore", copy = TRUE))
#> Error in db_copy_to(dest$con, name, df, overwrite = overwrite, types = types,  : 
#>   Can't copy data to table "dbplyr_BP9t2EMwur".
#> Caused by error in `dplyr::db_write_table()`:
#> ! Can't write table table "dbplyr_BP9t2EMwur".
#> Caused by error:
#> ! Failed to fetch row : ERROR:  type "na" does not exist at character 55

^{Created on 2025-05-23 with reprex v2.1.1}

[krlmlr]

Thanks, will look into it.

[toppyy]

I believe the problem exists because the types available are queried upon creating the connection with dbConnect().

If you re-create the connection after dropping and creating the type, the type should be pq_UserRole, not pq_NA.

So you are seeing the UserRole type under two different oid's in scenarions where the type exists before running the script. An imaginary example scenario:

1. dbConnect() runs and finds the UserRole type under the oid 123. This is stored with the connection object.
2. You run DROP TYPE and CREATE TYPE. UserRole is assigned a new oid 456
3. Quering the data tries to find the type with oid 456 from the connection data, but fails as only 123 exists

As a workaround, you can reconnect after altering the types. A proper is fix is a bit trickier since we'd have to know when to refresh the types stored with connection. Any thoughts @krlmlr ?