DBMS_AQ v11

EDB Postgres Advanced Server Advanced Queueing provides message queueing and message processing for the Advanced Server database. User-defined messages are stored in a queue; a collection of queues is stored in a queue table. Procedures in the DBMS_AQADM package create and manage message queues and queue tables. Use the DBMS_AQ package to add messages to a queue or remove messages from a queue, or register or unregister a PL/SQL callback procedure.

Advanced Server also provides extended (non-compatible) functionality for the DBMS_AQ package with SQL commands, see the Database Compatibility for Oracle Developers SQL Guide for detailed information about the following SQL commands:

  • ALTER QUEUE
  • ALTER QUEUE TABLE
  • CREATE QUEUE
  • CREATE QUEUE TABLE
  • DROP QUEUE
  • DROP QUEUE TABLE

The DBMS_AQ package provides procedures that allow you to enqueue a message, dequeue a message, and manage callback procedures. The supported procedures are:

Function/ProcedureReturn TypeDescription
ENQUEUEn/aPost a message to a queue.
DEQUEUEn/aRetrieve a message from a queue if or when a message is available.
REGISTERn/aRegister a callback procedure.
UNREGISTERn/aUnregister a callback procedure.

Advanced Server's implementation of DBMS_AQ is a partial implementation when compared to Oracle's version. Only those procedures listed in the table above are supported.

Advanced Server supports use of the constants listed below:

ConstantDescriptionFor Parameters
DBMS_AQ.BROWSE (0)Read the message without locking.dequeue_options_t.dequeue_mode
DBMS_AQ.LOCKED (1)This constant is defined, but will return an error if used.dequeue_options_t.dequeue_mode
DBMS_AQ.REMOVE (2)Delete the message after reading; the default.dequeue_options_t.dequeue_mode
DBMS_AQ.REMOVE_NODATA (3)This constant is defined, but will return an error if used.dequeue_options_t.dequeue_mode
DBMS_AQ.FIRST_MESSAGE (0)Return the first available message that matches the search terms.dequeue_options_t.navigation
DBMS_AQ.NEXT_MESSAGE (1)Return the next available message that matches the search terms.dequeue_options_t.navigation
DBMS_AQ.NEXT_TRANSACTION (2)This constant is defined, but will return an error if used.dequeue_options_t.navigation
DBMS_AQ.FOREVER (0)Wait forever if a message that matches the search term is not found, the default.dequeue_options_t.wait
DBMS_AQ.NO_WAIT (1)Do not wait if a message that matches the search term is not found.dequeue_options_t.wait
DBMS_AQ.ON_COMMIT (0)The dequeue is part of the current transaction.enqueue_options_t.visibility, dequeue_options_t.visibility
DBMS_AQ.IMMEDIATE (1)This constant is defined, but will return an error if used.enqueue_options_t.visibility, dequeue_options_t.visibility
DBMS_AQ.PERSISTENT (0)The message should be stored in a table.enqueue_options_t.delivery_mode
DBMS_AQ.BUFFERED (1)This constant is defined, but will return an error if used.enqueue_options_t.delivery_mode
DBMS_AQ.READY (0)Specifies that the message is ready to process.message_properties_t.state
DBMS_AQ.WAITING (1)Specifies that the message is waiting to be processed.message_properties_t.state
DBMS_AQ.PROCESSED (2)Specifies that the message has been processed.message_properties_t.state
DBMS_AQ.EXPIRED (3)Specifies that the message is in the exception queue.message_properties_t.state
DBMS_AQ.NO_DELAY (0)This constant is defined, but will return an error if usedmessage_properties_t.delay
DBMS_AQ.NEVER (NULL)This constant is defined, but will return an error if usedmessage_properties_t.expiration
DBMS_AQ.NAMESPACE_AQ (0)Accept notifications from DBMS_AQ queues.sys.aq$_reg_info.namespace
DBMS_AQ.NAMESPACE_ANONYMOUS (1)This constant is defined, but will return an error if usedsys.aq$_reg_info.namespace

The DBMS_AQ configuration parameters listed in the following table can be defined in the postgresql.conf file. After the configuration parameters are defined, you can invoke the DBMS_AQ package to use and manage messages held in queues and queue tables.

ParameterDescription
dbms_aq.max_workersThe maximum number of workers to run.
dbms_aq.max_idle_timeThe idle time a worker must wait before exiting.
dbms_aq.min_work_timeThe minimum time a worker can run before exiting.
dbms_aq.launch_delayThe minimum time between creating workers.
dbms_aq.batch_sizeThe maximum number of messages to process in a single transaction. The default batch size is 10.
dbms_aq.max_databasesThe size of DBMS_AQ’s hash table of databases. The default value is 1024.
dbms_aq.max_pending_retriesThe size of DBMS_AQ’s hash table of pending retries. The default value is 1024.

enqueue dequeue register unregister