5.12. Example - Asynchronous Query with Event-Driven Results

Like the previous example, this example uses the asynchronous query commands described in Section 4.4, “Asynchronous Query Processing Commands”. Unlike that example in Section 5.11, “Example - Asynchronous Queries”, this example uses pg_result_callback to establish an event handler to notify the Tcl interpreter when a result is ready. This feature was added to pgtcl-ng at version 1.6.0. In this example, Tcl waits for either an alarm-clock timeout or a result from the query. The same method can be used to wait for file or socket events, or to respond to user actions in a Tk interface, while also waiting for query results. Note: this is not a complete script.

Example 5.19. Asynchronous Query with Event-Driven Results

# Timeout handler. It sets a global flag to indicate TIMEOUT.
proc event_timeout {} {
  global event_flag
  if {$event_flag eq "WAITING"} {
    set event_flag TIMEOUT

# Query ready handler. It sets a global flag to indicate READY.
proc event_result {} {
  global event_flag
  if {$event_flag eq "WAITING"} {
    set event_flag READY

# Issue a query with timeout.
#   conn is the database connection handle.
#   sql is the SQL text to execute.
#   timeout is the number of seconds to wait for the query.
# If the query completes, this just displays the row count. In real life,
# you would do something more with the results.
proc query_timeout {conn sql {timeout 20}} {
  global event_flag

  # Calculate timeout in milliseconds:
  set timeout_msec [expr {1000 * $timeout}]

  # Initialize the global flag:
  set event_flag WAITING

  # Establish the result callback:
  pg_result_callback $conn event_result
  # Set the alarm clock:
  set after_id [after $timeout_msec event_timeout]

  # Send the query off, but do not wait:
  pg_sendquery $conn $sql

  # Wait for something - timeout or results - to change the global flag:
  vwait event_flag

  # Did the query complete?
  if {$event_flag eq "READY"} {
    # Cancel the alarm clock:
    after cancel $after_id
    puts "Query complete. Fetching result:"
    set res [pg_getresult $conn]
    puts "The query returned [pg_result $res -numTuples] row(s)."
    pg_result $res -clear
  } elseif {$event_flag eq "TIMEOUT"} {
    puts "Query did not complete! It timed out in about $timeout seconds."
    puts "Canceling the request:"
    # Note pg_cancelrequest also cancels the result callback.
    pg_cancelrequest $conn
    # Now we have to block until the backend is ready.
    pg_result [pg_getresult $conn] -clear
    puts "Request canceled."

Here is an example of using the above function. The pg_sleep PostgreSQL backend function (added at PostgreSQL-8.2.0) is used to simulate a slow query. Because the query time is longer than the specified timeout, this will time out. If the numbers are switched, the query will complete.

  query_timeout $conn "select pg_sleep(6)" 4
SourceForge.net Logo

This version of the manual was produced for the Pgtcl-ng Sourceforge project web service site, which requires the logo on each page.

To download a logo-free copy of the manual, see the Pgtcl-ng project downloads area.