Get Postgres Tips and Tricks
Subscribe to get advanced Postgres how-tos.
![]() |
![]() |
![]() |
3 Built-In Packages : 3.1 DBMS_ALERT
3.1 DBMS_ALERTThe DBMS_ALERT package provides the capability to register for, send, and receive alerts. The following table lists the supported procedures:
Advanced Server's implementation of DBMS_ALERT is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.Advanced Server allows a maximum of 500 concurrent alerts. You can use the dbms_alert.max_alerts GUC variable (located in the postgresql.conf file) to specify the maximum number of concurrent alerts allowed on a system.To set a value for the dbms_alert.max_alerts variable, open the postgresql.conf file (located by default in /opt/PostgresPlus/10AS/data) with your choice of editor, and edit the dbms_alert.max_alerts parameter as shown:alert_count specifies the maximum number of concurrent alerts. By default, the value of dbms_alert.max_alerts is 100. To disable this feature, set dbms_alert.max_alerts to 0.For the dbms_alert.max_alerts GUC to function correctly, the custom_variable_classes parameter must contain dbms_alerts:After editing the postgresql.conf file parameters, you must restart the server for the changes to take effect.3.1.1 REGISTERThe REGISTER procedure enables the current session to be notified of the specified alert.REGISTER(name VARCHAR2)The following anonymous block registers for an alert named, alert_test, then waits for the signal.3.1.2 REMOVEThe REMOVE procedure unregisters the session for the named alert.REMOVE(name VARCHAR2)3.1.3 REMOVEALLThe REMOVEALL procedure unregisters the session for all alerts.3.1.4 SIGNALThe SIGNAL procedure signals the occurrence of the named alert.3.1.5 WAITANYThe WAITANY procedure waits for any of the registered alerts to occur.Variable receiving the message sent by the SIGNAL procedure.The following anonymous block uses the WAITANY procedure to receive an alert named, alert_test or any_alert:3.1.6 WAITONEThe WAITONE procedure waits for the specified registered alert to occur.Variable receiving the message sent by the SIGNAL procedure.The following anonymous block is similar to the one used in the WAITANY example except the WAITONE procedure is used to receive the alert named, alert_test.Signal sent for alert_test sent by an anonymous block in a second session:3.1.7 Comprehensive ExampleThe following example uses two triggers to send alerts when the dept table or the emp table is changed. An anonymous block listens for these alerts and displays messages when an alert is received.The following anonymous block is executed in a session while updates to the dept and emp tables occur in other sessions:
3 Built-In Packages : 3.1 DBMS_ALERT
![]() |
![]() |
![]() |