transactions.R

# RPostgres is an R binding to libpq. NULLs in R = NA
library(DBI)
con <- dbConnect(RPostgres::Postgres())
dbGetQuery(con, "select * from pg_locks where pid = pg_backend_pid()")

## Warning in result_create(conn@ptr, statement): Unknown field type (28) in
## column transactionid

##     locktype database relation page tuple virtualxid transactionid classid
## 1   relation    16384    11090   NA    NA       <NA>          <NA>      NA
## 2 virtualxid       NA       NA   NA    NA     3/1524          <NA>      NA
##   objid objsubid virtualtransaction   pid            mode granted fastpath
## 1    NA       NA             3/1524 42258 AccessShareLock    TRUE     TRUE
## 2    NA       NA             3/1524 42258   ExclusiveLock    TRUE     TRUE

dbGetQuery(con, "begin")

## data frame with 0 columns and 0 rows

dbGetQuery(con, "begin") # to verify a transaction has started

## data frame with 0 columns and 0 rows

dbGetQuery(con, "select * from pg_locks where pid = pg_backend_pid()")

## Warning in result_create(conn@ptr, statement): Unknown field type (28) in
## column transactionid

##     locktype database relation page tuple virtualxid transactionid classid
## 1   relation    16384    11090   NA    NA       <NA>          <NA>      NA
## 2 virtualxid       NA       NA   NA    NA     3/1527          <NA>      NA
##   objid objsubid virtualtransaction   pid            mode granted fastpath
## 1    NA       NA             3/1527 42258 AccessShareLock    TRUE     TRUE
## 2    NA       NA             3/1527 42258   ExclusiveLock    TRUE     TRUE