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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.
  • Teradata workload analysis management software rules can defer the SELECT AND CONSUME statement. For example, if a statement references a user, account, or other object that has a rule defined on it, the statement is subject to that rule. Deferment might be because of a Teradata dynamic workload management software throttle against an attribute of the containing request that causes it to be delayed until some currently running requests finish.

    The best practice is not to apply Teradata workload analyzer restrictions to sessions that execute SELECT AND CONSUME statements.

    For information about creating workload management rules, see Teradata® Workload Analyzer User Guide, B035-2514.

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.