Queue tables basics

Teradata Database queue tables are implemented as a Teradata extension to ANSI SQL at the database/ table level.

Queue tables have a user-defined insertion timestamp (QITS) as the first column of the table.

The QITS contains the time the row was inserted into the queue table as the means for approximate FIFO ordering.

Teradata Database queue tables support asynchronous first-in-first-out (FIFO) push, pop, and peek queue operations:

1) The FIFO push operation is defined as a SQL INSERT operation to store rows into a queue table.

2) The FIFO peek operation is defined as an SQL SELECT operation to retrieve rows from a queue table without deleting them.

This is also referred to as browse mode.

3) The FIFO pop operation is defined as an SQL SELECT AND CONSUME operation to retrieve a row from a queue table and delete that selected row upon completion of the read.

This is also referred to as consume mode.

Consumed rows are rolled back when a transaction containing a SELECT AND CONSUME does not complete successfully.

What if we do SELECT -CONSUME and table contains no data?

A consume mode request goes into a delayed state when a SELECT AND CONSUME finds no rows in the queue table.

SELECT AND CONSUME request will wait for the table to be populated.

The request remains idle until an INSERT to that queue table awakens the request; that row is returned, and then it's deleted.

Can we change the default FIFO orderring?

The default FIFO ordering of a queue table may be altered before consumption by altering the QITS. This is done with the SQL UPDATE, UPSERT form of UPDATE, or MERGE statements.

Is SELECT .. CONSUME only way to delete data from Queue tables?

Rows may also be removed from a queue table with the SQL DELETE.

QUEUE tables can be used for event processing:

Queue tables can be used to know the order in which events occured.

Or  it could contain messages generated by a application. Other aplication reads this messages from this table in the same

sequence as the sending application has posted.