[YSQL] Support Listen/Notify

[m-iancu]

Jira Link: DB-1793

[adamfeldman]

Thrilled to see YugaByte is exploring Hasura support!

[adamfeldman]

It looks like Hasura (and Prisma) is now supported: https://docs.yugabyte.com/latest/develop/graphql/hasura.

Was Listen/Notify implemented in YugaByte to support Hasura?

Or did Hasura change something to support YugaByte? About a month ago I checked-out Hasura's codebase and found that their main use of Listen/Notify is to watch for schema changes: https://discordapp.com/channels/407792526867693568/428469959530643466/611662653630316548

My apologies if this discussion is off-topic for this Listen/Notify Issue.

[schoudhury]

@adamfeldman thanks for the question. YugabyteDB does not yet support Listen/Notify so Hasura features that depend on such support won't work for now. We are in design discussions with the Hasura team on how best to implement this. We will update this issue as the design gets finalized.

[frink]

Since YEDIS PUBSUB is already well supported, in theory this should be able to use the same plumbing. Ideally, it would be fairly magical if channels could be shared by the two front ends so that messages could be sent and received by either API.

[frink]

@schoudhury Any particular reason why this was moved out of v2.0? Just curious...

[schoudhury]

The solution is non-trivial in a distributed database like YugabyteDB. One approach that we would like to explore is to use YugabyteDB's Change Data Capture (CDC) stream. But CDC is itself is active dev (currently in Beta and moving towards GA in 2.1) so would like to pick this up after the 2.1 release.

[frink]

Thanks for the explanation.

Brokering messages in a cluster is definitely painfully complex. Good idea to reuse optimized components. But I still don't understand why not reuse the existing Yedis PUBSUB. It seems on the surface like a simpler option. After all, Postgres LISTEN / NOTIFY is functionally equivalent to Redis PUBLISH / SUBSCRIBE... Is Yedis PUBSUB going to CDC stream as well?

[ddorian]

@frink

After all, Postgres LISTEN / NOTIFY is functionally equivalent to Redis PUBLISH / SUBSCRIBE...

LISTEN/NOTIFY is transactional , there are probably more differences compared to redis.
Postgresql has process-per-connection, which is inefficient compared to redis (async polling)

Is Yedis PUBSUB going to CDC stream as well?

YEDIS layer is currently on maintenance. No new features are planned. See: https://docs.yugabyte.com/latest/yedis/ (@m-iancu can explain)

[frink]

Okay. That makes a lot more sense...

If you've got to include all the transaction overhead then it's going to be a huge mess to do. Never going to be as performant as Yedis PUBSUB. This probably also means that there cannot be cross talk between the two. (Which is sad, but I get it...)

Thanks for the commentary.

[jcmoore0]

Awesome. Any update on this one?
I am very interested to begin testing a migration.

[ddorian]

@jcmoore0 listen/notify is not in the near term roadmap
maybe we can find a workaround for your application
you can find me on slack

[FranckPachot]

To add in the list of software that require LISTEN/NOTIFY to get notified of schema changes

• PostgREST https://postgrest.org
• PostGraphile https://www.graphile.org/postgraphile

[xvaara]

PostGraphile also uses LISTEN/NOTIFY for realtime subscriptions. https://www.graphile.org/postgraphile/subscriptions/

[apatrida]

see also #12714

[St4NNi]

We would also love to have this feature, currently we are working around this via a separate notification system but keeping this in perfect sync with yugabyte is not an easy task.

[jhg03a]

The Graphile Worker job queue system is dependent on LISTEN/NOTIFY

[ammirator-administrator]

Is Hasura compatible with Yugabyte?
I have a yugabyte db started using the official Helm chart
And hasura successfully connects to it, but when running the migrations for some reason hasura tries twice to run these and then fails because tables already exists

So my understanding is that is not yet compatimble, at least the OSS version

Has that something to do with this issue?

[Bessonov]

@ammirator-administrator, one year ago, I used Hasura with Yugabyte, and it worked very well on its own. I discontinued this setup because Yugabyte's DDL operations were too slow and not transactional. I believe it's best to create a small, reproducible example and open an issue on Hasura side.

[ammirator-administrator]

Yes I come to the same conclusion,
I was able to connect these and run the migrations finally)
But why is it that slowww ~ "My first impression"
Yes I guess I'll have to investigate other solutions for now, and move from Yugabyte to something else
Running a query fro minutes) is just inacceptable for every type of application

[FranckPachot]

But why is it that slowww ~ "My first impression"

@ammirator-administrator are you talking about DDL or regular application queries?
For DDL, PostgreSQL approach (transactional) is nice on empty schema but locks the catalog for the whole duration. That doesn't scale in a distributed database as the catalog is shared.
For regular queries, the defaults may not fit your application. Do you have some example of slow queries?

[ammirator-administrator]

I'm trying to use with hasura
And I'm talking about the default query Hasura does to retrieve the tables schema
This or takes forever or returns timeout errors