16.20 - Queue Tables - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageâ„¢ SQL Fundamentals

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
zce1519094756513.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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.