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.
