Ditto what Craig said, it appears that the size limit for text types in PostgreSQL is 1GB so the JSON for the old and new alarm values must be at least 500MB. This seems... very excessive.

So my question is what are 'alarms with a huge Description' Are these supplied OpenNMS alarm definitions or new definitions from the user.
Could we have an example alarm definition which is causing this problem please.

This problem appears to be caused by limitations to the maximum size of a notification within postgresql. As such, there probably isn't much we can do other than advise that alarms should not have unreasonably large alarm descriptions (or any other text field) if the alarm change notifier is used.
The postgresql trigger definitions are defined in
/opennms/features/alarm-change-notifier/main-module/src/main/resources/OSGI-INF/blueprint/mainModuleBlueprint.xml
The trigger takes the old alarm row OLD, and the new alarm row NEW, converts them to json and then concatenates them into a single json message containing the old alarm values and the new alarm values:
temp_new = row_to_json(NEW);
temp_old = row_to_json(OLD);
IF (temp_new::text != temp_old::text) THEN notification=CONCAT('[', new_data::text,',', old_data::text, ']');
PERFORM pg_notify('opennms_alarm_changes',notification);

Clearly in this case, the notification field is too long for pg_notify which causes the error.
We could possibly add a test in the SQL function to reject alarms which generate too large a notification string but I don't know what the limits should be and whether this is actually a widely experienced case.

How large is the alarm payload?