Queue Tables - Advanced SQL Engine - Teradata Database

SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
zwv1557098532464.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1141
lifecycle
previous
Product Category
Teradata Vantageâ„¢

Teradata Database supports queue tables, which are similar to ordinary base tables, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue. Queue tables are useful for applications that want to submit queries that wait for data to be inserted into queue tables without polling.

When you create a queue table, you must define a TIMESTAMP column with a default value of CURRENT_TIMESTAMP. The values in the column indicate the time the rows were inserted into the queue table, unless different, user-supplied values are inserted.

You can then use a SELECT AND CONSUME statement, which operates like a FIFO pop:

  • Data is returned from the row with the oldest timestamp in the specified queue table.
  • The row is deleted from the queue table, guaranteeing that the row is processed only once.

If no rows are available, the transaction enters a delay state until one of the following occurs:

  • A row is inserted into the queue table.
  • The transaction aborts, either as a result of direct user intervention, such as the ABORT statement, or indirect user intervention, such as a DROP TABLE statement on the queue table.

To perform a peek on a queue table, use a SELECT statement.