How can I unserialize a model object using PL/R in Greenplum/Postgres?

Question

Error unserializing model object in Greenplum via PL/R

I store model objects in a greenplum database (the open source version) and I've successfully been able to serialize my model objects, insert them into a table in greenplum and unserialize when needed, but using R version 3.5 installed on my machine (local). This is the R code below that runs successfully:

Code:

fromtable = 'modelObjDevelopment'
mod.id = '7919'
model_obj <- 
  dbGetQuery(conn, 
             sprintf("SELECT val from standard.%s where model_id::int = '%s';", 
                     fromtable, mod.id))
iter_model <- postgresqlUnescapeBytea(model_obj)
lm_obj_back <- unserialize(iter_model)
summary(lm_obj_back)

Recently, I have installed PL/R on greenplum with all the necessary libraries that I generally use. I am attempting to recreate the code I use in local R (mentioned above) to run on greenplum. After much research I have been trying to run the following transformed code, which relentlessly keeps failing and giving me the same error.

Code:

DROP FUNCTION IF EXISTS mdl_load(val bytea);
CREATE FUNCTION mdl_load(val bytea)
  RETURNS text AS
    $$
        require("RPostgreSQL")
        iter_model<-postgresqlUnescapeBytea(val)
        model<-unserialize(iter_model)
        return(length(val))
    $$
LANGUAGE 'plr';

select length(val::bytea) as len, mdl_load(val) as t
from modelObjDevelopment
where model_id::int = 7919

At this point I don't care what I return, I just want the unserialize function to work.

Error:

[22000] ERROR: R interpreter expression evaluation error Detail: Error in unserialize(iter_model) : unknown input format Where: In PL/R function mdl_load

Hope someone had a similar issue and might have a clue for me. It seems that the bytea object changes size after being passed into Pl/R. I am new to this method and hope someone can help.

Answer 1

  $$
  require(RPostgreSQL)

  ## load the PostgresSQL driver

  drv <- dbDriver("PostgreSQL")

  ## connect to the default db

  con <- dbConnect(drv, dbname = 'XXX')

  rows<-dbGetQuery(con, 'SELECT encode(val::bytea,'escape') from standard.modelObjDevelopment where model_id::int=1234')

  iter_model<-postgresqlUnescapeBytea(rows[[model_obj_column]])

  model<-unserialize(iter_model)
  $$

We solved this problem together. For future people coming to this site, get and unserialize model object inside R code is the way to go.