ALTER QUEUE v17

Suggest edits

EDB Postgres Advanced Server includes an extra syntax not offered by Oracle with the ALTER QUEUE SQL command. You can use this syntax with the DBMS_AQADM package.

Name

ALTER QUEUE — Allows a superuser or a user with the aq_administrator_role privilege to modify the attributes of a queue.

Synopsis

This command is available in four forms.

Parameters for the first form

The first form of this command changes the name of a queue:

ALTER QUEUE <queue_name> RENAME TO <new_name>

queue_name

The name (optionally schema-qualified) of an existing queue.

RENAME TO

To rename the queue, include the RENAME TO clause and a new name for the queue.

new_name

New name for the queue.

Parameters for the second form

The second form of the ALTER QUEUE command modifies the attributes of the queue:

ALTER QUEUE <queue_name> SET [ ( { <option_name option_value> ) } [,SET <option_name> ] ]

queue_name

The name (optionally schema-qualified) of an existing queue.

Include the SET clause and option_name/option_value pairs to modify the attributes of the queue.

option_name option_value

The name of an option to associate with the new queue and the corresponding value of the option. If you provide duplicate option names, the server returns an error.

  • If option_name is retries, provide an integer that represents the number of times to attempt a dequeue.
  • If option_name is retrydelay, provide a double-precision value that represents the delay in seconds.
  • If option_name is retention, provide a double-precision value that represents the retention time in seconds.

Parameters for the third form

Use the third form of the ALTER QUEUE command to enable or disable enqueuing or dequeuing on a queue:

ALTER QUEUE <queue_name> ACCESS { START | STOP } [ FOR { enqueue | dequeue } ] [ NOWAIT ]

queue_name

The name (optionally schema-qualified) of an existing queue.

ACCESS

Include the ACCESS keyword to enable or disable enqueuing or dequeuing on a queue.

START | STOP

Use the START and STOP keywords to indicate the desired state of the queue.

FOR enqueue|dequeue

Use the FOR clause to indicate if you are specifying the state of enqueueing or dequeueing activity on the specified queue.

NOWAIT

Include the NOWAIT keyword to specify for the server not to wait for the completion of outstanding transactions before changing the state of the queue. You can use the NOWAIT keyword only when specifying an ACCESS value of STOP. The server returns an error if NOWAIT is specified with an ACCESS value of START.

Parameters for the fourth form

Use the fourth form to ADD or DROP callback details for a queue.

ALTER QUEUE <queue_name> { ADD | DROP } CALL TO <location_name> [ WITH <callback_option> ]

queue_name

The name (optionally schema-qualified) of an existing queue.

ADD | DROP

Include the ADD or DROP keywords to enable add or remove callback details for a queue.

location_name

Specifies the name of the callback procedure.

callback_option

Can be context. Specify a RAW value when including this clause.

Examples

This example changes the name of a queue from work_queue_east to work_order:

ALTER QUEUE work_queue_east RENAME TO work_order;

This example modifies a queue named work_order. It sets the number of retries to 100, the delay between retries to 2 seconds, and the length of time that the queue retains dequeued messages to 10 seconds:

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

These commands enable enqueueing and dequeueing in 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;

These commands disable enqueueing and dequeueing in 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;

See also

CREATE QUEUE, DROP QUEUE

← Prev

ALTER PROFILE

↑ Up

Compatible SQL commands

Next →

ALTER QUEUE TABLE