FIFO Operations and SELECT AND CONSUME - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Attributes of a Queue Table

A queue table is similar to an ordinary base table, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue.

The first column of a queue table contains Queue Insertion TimeStamp (QITS) values. The CREATE TABLE request must define the first column with these data type and attributes:

     TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

The QITS value of a row indicates the time the row was inserted into the queue table, unless a different, user-supplied value is inserted.

FIFO Ordering of Queue Table Rows Is Approximate

A queue table provides only provides an approximate FIFO ordering of its rows for these reasons:
  • System clocks on the nodes of an MPP system are not synchronized.
  • A user-supplied value that is different from the current timestamp can be inserted into the QITS column.
  • The rollback of a transaction or a request may restore rows with an earlier QITS value than rows already consumed.
  • Multiple rows use the same value for the QITS column when the INSERT statements in a multistatement request use the default value.

SELECT AND CONSUME Statements Operate Like a FIFO Pop Operation

SELECT AND CONSUME statements operate like a FIFO pop in these ways.
  • Data is returned from the row that has the oldest value in the QITS column of the specified queue table.
  • The row is deleted from the queue table, guaranteeing that the row is processed only once.