listen to postgresql notify

[mxfactorial]

please document on https://v4.benthos.dev/docs/components/inputs/sql_raw whether the postgres driver supports https://www.postgresql.org/docs/current/sql-notify.html

[mihaitodor]

Hey @mxfactorial 👋 I don't think this is currently supported since the sql_* components in Connect don't listen for events. I'm guessing you're looking for some CDC functionality. Feel free to raise an issue describing your use case in more detail.

[mxfactorial]

you're looking for some CDC functionality

building a debezium connector image consumes 1.6gb of storage

which is costly if, in a single line of code, you can directly send a postgres value as an event to a stream processor from a triggered function:

PERFORM pg_notify('revenue', NEW.revenue);

after perusing, youre only using https://pkg.go.dev/database/sql in the code so this is turning into a major feature request requiring a postgres specific driver

please consider documenting which dml statements are supported anyway

waiting for more +1s

[mihaitodor]

I don't think there are any specific limitations when queries are invoked via the sql_raw components because you can write any SQL query you wish and it will be executed if the underlying library supports it (in the case of Postgres, it's https://github.com/lib/pq). I'm no SQL expert though, and don't really know what subsets of queries are supported by the supported drivers. To be honest, it's not straightforward to tell in many cases, never mind document.

Having said that, if all you need to do is to run PERFORM pg_notify('revenue', NEW.revenue) just like you'd execute it via psql, then you can do that via a simple pipeline like this:

input:
  generate:
    count: 1
    mapping: |
      root.new_revenue = 42

output:
  sql_raw:
    driver: postgres
    dsn: # TODO
    query: "PERFORM pg_notify('revenue', $1)"
    args_mapping: |
      root = [
        this.new_revenue
      ]

However, from your initial query, I assumed what you're looking for is the listener part as well as described for example here https://bun.uptrace.dev/postgres/listen-notify.html. Again, I'm not an expert and I never used this feature before.

please consider documenting which dml statements are supported anyway

It would help a lot to know what exactly you expect to see in the docs and how it should be structured. PRs are welcome. Otherwise, the request is very vague...

[mxfactorial]

what you're looking for is the listener part

yes, notifying from postgresql and listening on connect as an input without debezium and a broker

what exactly you expect to see in the docs and how it should be structured

"raw" hints a user can do anything the driver supports

but https://github.com/lib/pq requires use of Listener and Notification when input_sql_raw.go uses DB and Rows

so you can say under https://v4.benthos.dev/docs/components/inputs/sql_raw#driver

A database driver to use. Only row returning DML statements are supported.

[mihaitodor]

Only row returning DML statements are supported.

I don't think that's accurate, since you can use sql_raw to call a stored procedure or to update / delete some rows without any issues. You won't get any data back, but that's not a problem. If you're using the sql_raw processor, you can wrap it in a branch processor, so then it just executes whatever SQL needs to be executed without altering the current message.

[mxfactorial]

You won't get any data back, but that's not a problem.

its not an input either

how the sql_raw input can be used to not return data is unrelated to a doc and feature named "input"

if you intend to support running a task with your stream processor, then calling the feature a "task" and using Exec() instead of Query() is intuitive

but drawing attention to how inputs can be used to not return data requires you to start saying complicated things like "non-input returning input" and "input returning input"

steering this back to the original intent while wrangling the current feature naming complexity for others who land here:

if youre configuring your connect stream processor with an "input returning input" from postgres, sql_raw does not support returning notifications