The payload data should not be passed via `pg_notify`, but instead accessed from the DB by the listener

[spwoodcock]

Is your feature request related to a problem? Please describe.

Follow on from #8

• There is a 8000 byte limit for pg_notify data, which isn't much.
• This obviously doesn't work for sending large submission XML data.

Describe the solution you'd like

• Instead of aggregating all data for the payload in the trigger, perhaps we actually need to pass a lightweight object through pg_notify, then have the listener gather all the data required from the DB before posting:

PERFORM pg_notify('odk-events', jsonb_build_object(
  'action', TG_OP,
  'type', action_type,
  'id', NEW.id
)::text);

• We pass the entity or submission def ID, then get the data from the listener code.

The downside here (and reason for not doing this originally), is the need for two db transactions, not just one.

Describe alternatives you've considered

• Handling submission related events separately, as this is probably only where we will have issues (entity / review events will generally have smaller payloads).

[BrunoXIII-Gav]

Hello spwoodcock

I'm interested about central-webhook.

About this issue, i had the similar idea, that pg_notify only send an submission id, but then it need to run another query to get full the data, it works but with two db transaccion. Despite that, I thinking to develop it like adittion of central-webhook, at least that is what I would like.

Another optiions it could be an intelligent polling that depending of the submission quantity in audits table that allow scanning by varying the time and register all the events that happen in audits. However, the last option could be heavy for the server by the number of daily transaction, so also another option it could be replace pg_notify with http_post from http extension of postgresql, that send the data direct from trigger.

I would greatly appreciate your opinion on this topic, thanks.

[spwoodcock]

Hey @BrunoXIII-Gav, thanks for your interest in the tool!

I also considered the http extension for postgres, to send requests directly to the webhook. I believe at the time I thought authentication would be tricky. But it looks like it supports sending custom headers, so this could be the simplest option!

If anyone is able to test this, report back, and confirm it works well (or I may find time), we could possibly replace this tool:

• Deprecate central-webhook.
• Provide a library of Postgres triggers, including info for adding pg_http and configuration for Authorization headers.
• Also provide a guide for how to integrate this into the existing Central Postgres database.

IMO this approach would be a lot simpler 👍

[BrunoXIII-Gav]

Hi @spwoodcock , I aprecciate your response.

I did some changes, but only for the submission.create action. I added an http_post from the http extension, working in conjunction with pg_notify, which sent the logs to the "odk-events" channel and blocking the SendRequest function to also prevent double submissions.

I tested this implementation, and it works very well, even under heavy workloads. This implementation allows for sending a large amount of data.

I submitted a PR and would like that you test it if you have time. Thanks for your time.