PolarDB for PostgreSQL(Compatible with Oracle) provide the syntax of the ALTER QUEUE SQL command that is not provided by Oracle. You can use this command with the DBMS_AQADM package.

Description

You can use the ALTER QUEUE command to modify a queue if you have the aq_administrator_role privilege. This command has the following four types of syntax based on functions.

Change the name of a queue

You can use the first type of syntax to change the name of a queue. The syntax is as follows:

ALTER QUEUE queue_name RENAME TO new_name
ParameterDescription
Table 1. Parameters
ParameterDescription
queue_nameThe name of the queue. The name can be schema-qualified.
RENAME TOThe RENAME TO clause that is used to rename the queue. The clause is followed by a new name of the queue.
new_nameThe new name of the queue.

Modify parameters of a queue

You can use the second type of syntax to modify parameters of a queue.

ALTER QUEUE queue_name SET [ ( { option_name option_value  } [,SET option_name
ParameterDescription
Table 2. Parameters
ParameterDescription
queue_nameThe name of the queue. The name can be schema-qualified.

To specify parameters to be modified, you must include the SET clause and option_name/option_value pairs.

option_name option_value

The names and values of one or more options that are associated with the new queue. If you provide duplicate option names, the server returns an error.

  • If the value of the option_name parameter is retries, you must provide an integer that indicates the number of dequeuing attempts.
  • If the value of the option_name parameter is retrydelay, you must provide a double-precision value that indicates the delay in seconds.
  • If the value of the option_name parameter is retention, you must provide a double-precision value that indicates the retention period in seconds.

Enable or disable enqueuing and dequeuing

You can use the third type of syntax to enable or disable enqueuing and dequeuing for a queue.

ALTER QUEUE queue_name ACCESS { START | STOP } [ FOR { enqueue | dequeue } ] [ NOWAIT ]
ParameterDescription
Table 3. Parameters
ParameterDescription
queue_nameThe name of the queue. The name can be schema-qualified.
ACCESSTo enable or disable enqueuing and dequeuing for a queue, you must include the ACCESS clause.
START | STOPThe required state of the queue.
FOR enqueue|dequeueSpecifies whether to enable the enqueuing or dequeuing feature for the queue.
NOWAITSpecifies that the server does not wait for the completion of outstanding transactions before changing the state of the queue. The NOWAIT keyword can be used only if you specify STOP in the ACCESS clause. If you specify START in the ACCESS clause, the server returns an error.

Add or remove callback details of a queue

You can use the fourth type of syntax to add or remove callback details of a specified queue.

ALTER QUEUE queue_name { ADD | DROP } CALL TO location_name [ WITH callback_option ]
ParameterDescription
ParameterDescription
queue_nameThe name of the queue. The name can be schema-qualified.
ADD | DROPSpecifies whether to add or remove the callback details of a queue.
location_nameThe name of the callback stored procedure.
callback_optionA valid value of the lback_option parameter is context. You must specify a RAW value when including the callback_option parameter.

Examples

In the following example, the name of a queue is changed from work_queue_east to work_order:

ALTER QUEUE work_queue_east RENAME TO work_order;

The following example shows how to modify a queue named work_order. The number of retries is set to 100, the interval between retries is set to 2 seconds, and the retention period of dequeued messages is set to 10 seconds.

ALTER QUEUE work_order SET (retries 100, retrydelay 2, retention 10);

The following examples show how to enable enqueuing and dequeuing for a queue named work_order:

ALTER QUEUE work_order ACCESS START;
ALTER QUEUE work_order ACCESS START FOR enqueue;
ALTER QUEUE work_order ACCESS START FOR dequeue;

The following examples show how to disable enqueuing and dequeuing for a queue named work_order:

ALTER QUEUE work_order ACCESS STOP NOWAIT;
ALTER QUEUE work_order ACCESS STOP FOR enqueue;
ALTER QUEUE work_order ACCESS STOP FOR dequeue;