FIFO Operations and SELECT AND CONSUME - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.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.
  • 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.