async: Asynchronous query processing

In rpg: Easy Interface to Advanced PostgreSQL Features

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

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.

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

Note

In practice, you will be much better off using cursor as that will usually return very quickly even for large queries, and has the advantage of retrieving the results in chunks. You can call cancel while a cursor is active. The cursor will return PGRES_FATAL_ERROR if the cancel is effective. Alternately, issuing any query that sets the result status will have the same effect as finish_async.

Author(s)

Timothy H. Keitt

Examples

## Not run: 
# create a database
system("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()
system("dropdb rpgtesting")
## End(Not run)

rpg documentation built on May 2, 2019, 5:49 p.m.