FIFO Operations and SELECT AND CONSUME - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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.