sql: support NOTIFY, LISTEN, and UNLISTEN commands of postgresql

[yuzefovich]

PostgreSQL has support for NOTIFY, LISTEN, and UNLISTEN commands which generate and listen for a notification, respectively. This is not a part of SQL standard; however, some ORMs (like go-pg) seems to be using it.

Jira issue: CRDB-5438

[asubiotto]

The https://github.com/lib/pq test suite tests this as well

[jordanlewis]

I think that this would be pretty easy to implement with changefeeds.

Here's a mini proposal:

CREATE TABLE system.pgnotify (
    channel STRING PRIMARY KEY,
    payload STRING
);

When a user runs LISTEN x, which is an asynchronous command, we start a goroutine running a SQL changefeed against system.pgnotify:

EXPERIMENTAL CHANGEFEED FOR system.pgnotify;

The goroutine filters the changefeed for rows with channel equal to x, and sends them as asynchronous notifications to the pgwire connection.

When a user runs NOTIFY x, we run:

UPSERT INTO system.pgnotify VALUES (x, NULL);

When a user runs NOTIFY x 'value', we run:

UPSERT INTO system.pgnotify VALUES(x, 'value');

When a user runs UNLISTEN x, we stop the goroutine running the SQL changefeed for x.

Once changefeeds can be filtered in SQL, we can accordingly update the implementation to be more efficient.

[jordanlewis]

Might be a decent Friday project!

[lsraj]

Our S3 lambda notifications are published into cockroach db. Monitoring lambda events with
LISTEN/NOTIFY cannot be done lacking cockroach db support for these APIs. I have tested
by using pq.NewListener/Listen() and they fail as:

NewListener - event : 3, err - pq: syntax error at or near "listen"
NewListener - event : 3, err - pq: syntax error at or near "listen"
NewListener - event : 3, err - pq: syntax error at or near "listen"

Are there any options or workaround?
Is there a timeline when this could be supported?

[jmls]

did this feature make it into a release ?

[sandstrom]

This is also useful for popular Rails ActiveJob adapters such as Good Job and Que, since they rely on NOTIFY/LISTEN.

https://github.com/bensheldon/good_job

[vy-ton]

A Serverless user inquired about this feature.

Curious about @amruss or @shermanCRL thoughts on Jordan's suggestion to user changefeeds.

@rafiss seems like the issue was on a closed project so moving to SQL Experience temporarily

[lessless]

Hey CockroachLabs,

I appreciate this is a big undertaking but also an important one. Could you please advise where it's on the priority list?

Cheers

[yuzefovich]

AFAIK this feature hasn't been prioritized yet, so it won't be available at least until 24.1 (and perhaps even later), cc @dikshant

[lessless]

Understood. I believe that having that functionality would unlock a broader adoption for CockroachDB. For example we have two packages in our current codebase that rely on NOTIFY/LISTEN and I believe it's the case with many other products out there.

[dikshant]

We don't have plans to invest in this area right now. Could you tell us more about your use case? Some or most of this functionality can be achieved in CockroachDB today using Changefeeds:

Changefeed:
https://www.cockroachlabs.com/docs/stable/changefeed-for

Enterprise Changefeed:
https://www.cockroachlabs.com/docs/stable/create-changefeed

[jkthorne]

I think the use case was listed in the comment above.

You have a codebase and cockroachdb is sold as a dropin replacement. The use case is taking an existing codebase with Notify/Listen and try it with cockroachdb.

[lessless]

@dikshant if that's an official CockroachDB position, it's quite hard to comprehend. CDC is a completely separate feature that doesn't enable using CockroachDB with libraries relying on NOTIFY/LISTEN.

Is there anyone else who can provide more details as to why this functionality is not a part of the foreseeable future?

[benperiton]

From my POV, I use PG as a queue in some instances with https://github.com/graphile/worker and I'd rather have NOTIFY/LISTEN than polling. It is pretty much the only thing stopping me from moving over at the moment.

[sandstrom]

Our use-case is that we'd rather stay with our current work queue infrastructure running on Postgres, than switch to a database where we cannot use the current work queue system (https://github.com/bensheldon/good_job).

[maitredede]

Hello :)

An other use case (that should be a showcase) is Odoo, a big ERP using Postgresql. They use NOTIFY/LISTEN for scheduling and async operations. Since these features are not available in CRDB, Odoo does simply not start.

[Paillat-dev]

Hello :)

An other use case (that should be a showcase) is Odoo, a big ERP using Postgresql. They use NOTIFY/LISTEN for scheduling and async operations. Since these features are not available in CRDB, Odoo does simply not start.

I'm here for the exact same reason

For reference: odoo/odoo#83730

[TeddyAlbina]

Our use-case is that we'd rather stay with our current work queue infrastructure running on Postgres, than switch to a database where we cannot use the current work queue system (https://github.com/bensheldon/good_job).

I have the same issue with the Hangfire library, i'm implementing cockroach and think CHANGEFEED can do the trick just fine

[lessless]

I honestly don't know how CocktoachDB is planning to compete with modern databases like SurrealDB that offer a complete change stream out of the box when they don't even support established features of such relic databases like PostgreSQL. /Yevhenii On 2 Feb 2024, at 01:14, XavXander ***@***.***> wrote: Our use-case is that we'd rather stay with our current work queue infrastructure running on Postgres, than switch to a database where we cannot use the current work queue system (https://github.com/bensheldon/good_job). I have the same issue with the Hangfire library, will try to implement a custom infrastructure so it can notify with DB or some external provider —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: ***@***.***>