rpg (version 1.6)

async_query: Asynchronous query processing

Description

Manage an asynchronous query

Usage

async_query(sql = "", pars = NULL)
async_status()
is_busy()
cancel()
finish_async()

Arguments

sql

a query string

pars

a vector of parameters

Value

async_query: true if query was successfully sent (an invalid query will still return true)

async_status: a results status object, possibly indicating an invalid query

is_busy: a boolean

Details

These functions expose the asynchronous query interface from libpq. The function async_query issues a query. Its call is identical to query except that it will return immediately. When the issued command is ready, the function async_status will return a query status object exactly as query. Otherwise it will return "BUSY" to indicate the server has not finished or "DONE" to indicate there is nothing more to fetch.

If async_status does not return "DONE", then you should call finish_async to free pending results. Note that a call to finish_async may block until the server is finished processing the command. It calls cancel internally but there is no guarantee the command will abort.

Any pending results will be lost if you call query, execute or fetch with a sql string prior to async_query returning DONE. If you need to issue queries while waiting on an async call, then use push_conn to save the query state, connect to make a new connetion, and then pop_conn followed by async_status.

is_busy is a slightly faster shortcut to check whether the server has completed the query. You must still call async_status to fetch the results.

Examples

# NOT RUN {
# create a database
createdb("rpgtesting")
connect("rpgtesting")
begin()

# write data frame contents
data(mtcars)
write_table(mtcars)

# async processing on smallish result
# this wont be interesting if your machine is very fast
async_query("SELECT a.* FROM mtcars a, mtcars b")
repeat
{
  status = async_status()
  if ( status != "BUSY" ) break
  cat("busy...\n")
  Sys.sleep(1)
}
print(status)
head(fetch())
finish_async()
Sys.sleep(1)

# async processing on larger result
async_query("SELECT a.* FROM mtcars a, mtcars b, mtcars c")
count = 0
repeat
{
  status = async_status()
  if ( status == "BUSY" )
  {
    if ( count > 2 )
    {
      cat("calling cancel...\n")
      cancel()
    }
  }
  else break
  cat("busy... \n")
  Sys.sleep(1)
  count = count + 1
}
print(status)
finish_async()

# you can run multiple queries with async_query
rollback(); begin()
write_table(mtcars)
sql1 = "SELECT mpg FROM mtcars LIMIT 3"
sql2 = "SELECT cyl FROM mtcars LIMIT 4"
async_query(paste(sql1, sql2, sep = "; "))
while ( async_status() == "BUSY" ) NULL
fetch()
while ( is_busy() ) NULL
async_status()
fetch()
finish_async()

# issue an async query and come back later
async_query(sql1)
push_conn()
connect("rpgtesting")

# fails because of transaction isolation
fetch(sql2)
pop_conn()
async_status()

# results from sql1
fetch()

# this is automatic if you issue new queries
finish_async()

# cleanup
rollback()
disconnect()
dropdb("rpgtesting")
# }
# NOT RUN {
# }