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.