15.00 - Rules for Consuming Rows From a Queue - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Rules for Consuming Rows From a Queue

The following rules and recommendations apply to consuming rows from a queue table (see SQL Data Manipulation Language for a complete description of the rules and limits for consuming queue table rows).

  • To consume rows from a queue table, specify the AND CONSUME TOP 1 keywords in your SELECT request. The AND CONSUME keywords indicate that the request is a consume mode request, while TOP 1 indicates that the oldest row from the queue table is to be retrieved.
  • The following example consumes a row from a queue table named shopping_cart.

        SELECT AND CONSUME TOP 1 * 
        FROM shopping_cart;    
  • You can also consume rows from stored procedures and embedded SQL applications that use the INTO clause to assign the values from a row to host or local variables.
  • You should place any action taken based on consuming a row from a queue table in the same transaction as the consume mode SELECT operation on that same queue table. This ensures that both the row consumption and the action taken on that queue table are committed together, so no row or action for that queue table is lost.
  • If no action is to be taken, then you should isolate any SELECT AND CONSUME request as the only request in a transaction (see SQL Request and Transaction Processing).

  • A multistatement request should contain only 1 consume mode SELECT request.
  • A trigger can call a stored procedure that consumes a row from a queue table.