用DBI & RPostgres读写Postgres大型对象

Question

我试图在R中使用PostgreSQL的大型对象(PostgreSQL)特性，而且我在使用{DBI}/{RPostgres}编写和阅读方面遇到了一些困难。

以下是我到目前为止尝试过的：

# Getting the db
docker run --rm --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5433:5432 postgres

library(DBI)
con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "postgres",
  host = "localhost",
  port = 5433,
  user = "postgres",
  password = "mysecretpassword"
)

创作作品：

> dbGetQuery(con, "SELECT lo_create(1234);")
  lo_create
1      1234

但是我很难弄清楚如何将R对象写到这个大对象中。例如，如何使用mtcars和{RPostgres}在Postgres中将{DBI}编写为大型对象

然后，如何在R中再读一遍呢？

Answer 1

考虑R的serialize() (..RData/..RDS格式的底层构建)，将R对象保存到大型对象的Postgres OID列中，并使用Postgres v10+ 服务器端大对象函数创建和检索内容。下面可以通过删除所有bytea函数来处理lo_*类型。

假设表结构：

CREATE TABLE my_table(
   ...
   r_object OID
   ...
)

要追加R对象，请执行以下操作：

# PREPARED STATEMENT
sql <- "INSERT INTO my_table(r_object) VALUES (lo_from_bytea(0, ?r_obj))"

# BIND PARAMETER OF SERIALIZED RAW VECTOR
query <- DBI::sqlInterpolate(conn, sql, r_obj = serialize(mtcars))

# EXECUTE ACTION
dbExecute(conn, query)

要检索R对象：

sql <- "SELECT lo_get(r_object) AS r_data FROM my_table"
pg_data <- dbGetQuery(conn, sql)

# UNSERIALIZE RETURNED RAW VECTOR
mtcars_from_pg <- unserialize(pg_data$r_data[1])

或者，您可以使用Postgres的文本类型(长度无限)和R的dput (ASCII表示R对象)和返回的dput字符串上的eval + parse。

CREATE TABLE my_table(
   ...
   r_text TEXT
   ...
)

# PREPARED STATEMENT
sql <- "INSERT INTO my_table(r_text) VALUES (?r_obj)"

# BIND PARAMETER OF DPUT STRING OUTPUT
query <- DBI::sqlInterpolate(conn, sql, r_obj = capture.output(dput(mtcars)))

# EXECUTE ACTION
dbExecute(conn, query)

# RETRIEVE CONTENT
sql <- "SELECT r_text FROM my_table"
pg_data <- dbGetQuery(conn, sql)

# UNSERIALIZE RETURNED RAW VECTOR
mtcars_from_pg <- eval(parse(text=pg_data$r_text[1]))