[R-sig-DB] RPostgreSQL Row Inserts on Remote Servers

[Whit Armstrong]

you can use my driver. which will someday make it to cran.  it uses a
binary connection to write the data.

http://github.com/armstrtw/unifieddbi

depending on what you are doing, you can get between a 10x and 50x speedup.

-Whit



On Tue, Jul 20, 2010 at 11:37 AM, McGehee, Robert
<Robert.McGehee using geodecapital.com> wrote:
> Hello,
> An issue discussed before on this list server is difficulty using
> dbWriteTable from the RPostgreSQL/DBI package to insert rows when
> either:
> 1) the R client and PostgreSQL server are on different computers (or at
> least don't share a common filesystem), and thus bulk copy is
> unavailable.
> 2) the postgres user does not have read permission for the user's file
> (See https://stat.ethz.ch/pipermail/r-sig-db/2009q4/000741.html, for
> example)
>
> To address this, I wrote a helper function called dbInsert() that some
> of you may find useful. The function converts a data frame into a single
> INSERT query transaction that inserts the data frame into an existing
> table. My hope is that this is a relatively fast way to insert rows when
> bulk copy is unavailable.
>
> If any of you have already discovered a faster way to insert data
> without using bulk copy, or find ways of improving this code, please
> share. I'm also CC:ing the RPostgreSQL package maintainer in case he
> thinks this functionality should be merged into the postgresWriteTable
> function for when bulk copy is not possible.
>
> Cheers,
> Robert
>
> dbInsert <- function(con, name, value, row.names = TRUE, ...) {
>    xx <- dbSendQuery(con, paste("select * from", name, "LIMIT 1;"))
>    cols <- dbColumnInfo(xx)$name
>    dbClearResult(xx)
>    if (row.names) {
>        if (!"row_names" %in% cols) stop("row_names column missing from
> ", sQuote(name))
>        value[["row_names", exact=TRUE]] <- rownames(value)
>    }
>    if (length(setdiff(names(value), cols)))
>      stop("names of 'value' do not match columns of ", sQuote(name))
>
>    cdt  <- which(sapply(value, inherits, c("Date", "POSIXt")))
>    ctxt <- which(sapply(value, postgresqlDataType)=="text")
>    for (i in cdt)
>      value[[i]] <- ifelse(is.na(value[[i]]), "NULL",
> sQuote(format(value[[i]])))
>    for (i in setdiff(ctxt, cdt))
>      value[[i]] <- ifelse(is.na(value[[i]]), "NULL",
> sQuote(value[[i]]))
>
>    m <- as.matrix(value)
>    class(m) <- "character"
>    m[is.na(m)] <- "NULL"
>
>    q1 <- paste("BEGIN; INSERT INTO", name, "(", paste(names(value),
> collapse=", "), ") VALUES")
>    q2 <- apply(m, 1, function(x) paste("(", paste(x, collapse=","),
> ")", sep=""))
>    q3 <- "; COMMIT;"
>    qry <- paste(q1, paste(q2, collapse=","), q3)
>    dbGetQuery(con, qry)
> }
>
> Robert McGehee, CFA
> Geode Capital Management, LLC
> One Post Office Square, 28th Floor | Boston, MA | 02109
> Tel: 617/392-8396    Fax:617/476-6389
> mailto:robert.mcgehee using geodecapital.com
>
>
> This e-mail, and any attachments hereto, are intended fo...{{dropped:12}}
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>