PubSubListener with vanilla postgres

[wezell]

Parent Issue

No response

User Story

There are cases where the impossibl PG driver fails to connect to postgres servers for unknown reasons. To rule out other issues, it would be great to have a postgresql PubSubListener that only used the standard postgres jdbc driver and did not rely on 3rd party drivers.

Acceptance Criteria

Be able to use another pub/sub mechanism

Proposed Objective

Cloud Engineering

Proposed Priority

Priority 2 - Important

[jcastro-dotcms]

INTERNAL QA: PASSED

The new JDBCPubSubImpl provider is working as expected. By default, this is the logging you see when starting up dotCMS with the default provider:

11:51:13.490  WARN  dotpubsub.PostgresPubSubImpl - Restarting PGNotificationListener in 0 ms to retry postgres pub/sub connection
11:51:14.501  INFO  dotpubsub.PostgresPubSubImpl - Starting PostgresPubSub. Have servers:0
11:51:14.501  INFO  dotpubsub.PostgresPubSubImpl - PGNotificationListener connecting to pub/sub...
11:51:14.895  INFO  dotpubsub.PostgresPubSubImpl -  - LISTEN cluster_actions

If you set the following configuration property to use the new one:

DOT_PUBSUB_PROVIDER_OVERRIDE=com.dotcms.dotpubsub.JDBCPubSubImpl

The logging will change to this:

12:07:12.875  INFO  dotpubsub.JDBCPubSubImpl - PGListener listening : cluster_actions
12:07:12.875  INFO  dotpubsub.JDBCPubSubImpl - Running Listener Loop every 500ms

Additionally, if you enable the DEBUG logging for the new JDBCPubSubImpl provider:

curl --location --request PUT 'http://localhost:8080/api/v1/logger' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic YWRtaW5AZG90Y21zLmNvbTphZG1pbg==' \
--header 'Cookie: JSESSIONID=D68D47EA20CDF5230671A86CB11EDD7F; access_token=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJqdGkiOiI0NjE3NmUxMi02YzJkLTQwNmMtYjcyNC00ZGZhYzQ1NjI1MTciLCJ4bW9kIjoxNjk0NjIwMTQzNjU0LCJzdWIiOiJkb3RjbXMub3JnLjEiLCJpYXQiOjE2OTQ2MjAxNDMsImlzcyI6IjQ1ODFiMGMyYTMiLCJleHAiOjE2OTQ3MDY1NDN9.pyoDUIbbgffDKH1T0gFFtLj7v3f8Y9DOju1JrWAgToQ' \
--data '{
    "name": "com.dotcms.dotpubsub.JDBCPubSubImpl",
    "level": "DEBUG"
}'

You'll see several entries related to the provider constantly checking for notifications. For instance:

09:54:00.640  DEBUG dotpubsub.JDBCPubSubImpl - Got Notifications:0
09:54:01.146  DEBUG dotpubsub.JDBCPubSubImpl - Got Notifications:0
09:54:01.653  DEBUG dotpubsub.JDBCPubSubImpl - Got Notifications:0
09:54:02.158  DEBUG dotpubsub.JDBCPubSubImpl - Got Notifications:0
09:54:02.665  DEBUG dotpubsub.JDBCPubSubImpl - Got Notifications:0
09:54:03.171  DEBUG dotpubsub.JDBCPubSubImpl - Got Notifications:0

This means that the new provider working as expected.

[wezell]

nice internal QA @jcastro-dotcms !

[erickgonzalez]

Also customer confirmed is working on them https://dotcms.zendesk.com/agent/tickets/110932

[josemejias11]

Approved: Tested on master_8f0095a_SNAPSHOT, Docker, macOS 13.0, FF v113.0