Support for Supabase/Supavisor

[brandur]

Extracted from @andreirtaylor in #205:

I'm assuming that this is related, but I have not been able to get river to work with supabase they use a proprietary connection pooling system called supavisor.

The error output from river is below when using session pooling.

Happy to help with any testing here and or opening up a new issue.

time=2024-02-25T11:52:38.976-05:00 level=ERROR msg="Scheduler: Error scheduling jobs" error="error deleting completed jobs: ERROR: prepared statement \"stmtcache_9233d4c846c6a2a54af178e1e500878088ef7296ba8c6bf0\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:39.275-05:00 level=ERROR msg="error attempting reelection" elector.err="ERROR: prepared statement \"stmtcache_e924bba8ef07b260df276cc58553fdf77a5d2ac1321679b8\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:39.504-05:00 level=ERROR msg="producer: Error fetching jobs" err="ERROR: prepared statement \"stmtcache_533cb1679bed1f4917f01ee331deb9347cca82532ad1cad9\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:40.416-05:00 level=ERROR msg="producer: Error fetching jobs" err="ERROR: prepared statement \"stmtcache_533cb1679bed1f4917f01ee331deb9347cca82532ad1cad9\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:41.393-05:00 level=ERROR msg="producer: Error fetching jobs" err="ERROR: prepared statement \"stmtcache_533cb1679bed1f4917f01ee331deb9347cca82532ad1cad9\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:42.405-05:00 level=ERROR msg="producer: Error fetching jobs" err="ERROR: prepared statement \"stmtcache_533cb1679bed1f4917f01ee331deb9347cca82532ad1cad9\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:43.381-05:00 level=ERROR msg="producer: Error fetching jobs" err="ERROR: prepared statement \"stmtcache_533cb1679bed1f4917f01ee331deb9347cca82532ad1cad9\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:43.977-05:00 level=ERROR msg="Scheduler: Error scheduling jobs" error="error deleting completed jobs: ERROR: prepared statement \"stmtcache_9233d4c846c6a2a54af178e1e500878088ef7296ba8c6bf0\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:44.394-05:00 level=ERROR msg="producer: Error fetching jobs" err="ERROR: prepared statement \"stmtcache_533cb1679bed1f4917f01ee331deb9347cca82532ad1cad9\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:44.644-05:00 level=ERROR msg="error attempting reelection" elector.err="ERROR: prepared statement \"stmtcache_e924bba8ef07b260df276cc58553fdf77a5d2ac1321679b8\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:45.374-05:00 level=ERROR msg="producer: Error fetching jobs" err="ERROR: prepared statement \"stmtcache_533cb1679bed1f4917f01ee331deb9347cca82532ad1cad9\" already exists (SQLSTATE 42P05)"
time=2024-02-25T11:52:46.418-05:00 level=ERROR msg="producer: Error fetching jobs" err="ERROR: prepared statement \"stmtcache_533cb1679bed1f4917f01ee331deb9347cca82532ad1cad9\" already exists (SQLSTATE 42P05)"

[andreirtaylor]

Happy to help with testing.

But just wanted to let you know I'm not actually using Supabase in production (I'm using crunchy data which seems fine).

[brandur]

Currently, I'm thinking that River should support a "poll only" mode for listening for new jobs and leader election in order to work around systems where nothing like LISTEN is available. It wouldn't be that hard to do this.

A separate problem is that the errors above appear to be from prepared statement problems, although theoretically Hypavisor supports prepared statements now. More investigation needed to detangle what's happening there.

[brandur]

Thanks @andreirtaylor.

But just wanted to let you know I'm not actually using Supabase in production (I'm using crunchy data which seems fine).

Good choice!

[JanRuettinger]

We also run into issues with supabase. End of January supabase switched all users over to Supavisor. So we connect to Supavisor in session mode and run into the issue that too many connections are opened and eventually we run out of free connections. But everything works for a while. We can start the river queue and runs fine for a couple of minutes.

I am fairly new to Supavisor + River. So far I tried the following to debug the issue:

By looking at the queries that open the new connections with SELECT * FROM pg_stat_activity where query like '%river%' order by pid desc I learnt that the issue is caused by the query LISTEN river_insert which keeps on opening new connections.

Is there a workaround for that issue?

In the comment https://github.com/riverqueue/river/issues/205#issuecomment-1968745525 a potential fix (https://github.com/riverqueue/river/commit/b2cb1422b4b5c6193a32fce5f78df48c6c442729) was mentioned. To me it looks like it addresses a different issue. Is that correct?

We are not using any supabase specific features (except auth). So we might just migrate away from them to a different managed Postgres provider if the issue can't be fixed easily.

[brandur]

@JanRuettinger It is possible there's a bug in there somewhere, I'll try to repro it. Can you give us a few more details about what your code looks like to help with the repro? i.e. Client configuration, what your worker configuration roughly looks like, etc.

In the comment https://github.com/riverqueue/river/issues/205#issuecomment-1968745525 a potential fix (https://github.com/riverqueue/river/commit/b2cb1422b4b5c6193a32fce5f78df48c6c442729) was mentioned. To me it looks like it addresses a different issue. Is that correct?

Yeah, I don't think that's related to what you're encoutering.

[JanRuettinger]

I have created a minimal example to reproduce the issue: https://github.com/JanRuettinger/river_issue/tree/main => Unfortunately you still need to create a supabase project to reproduce the issue.

EDIT: connecting directly to the db works without issues. So the bug seems to be related to supavisor.

[kamikazechaser]

@JanRuettinger Could you try adding the following to https://github.com/JanRuettinger/river_issue/blob/main/jobqueue/river.go#L39:

config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

You need to also import the https://github.com/riverqueue/river/tree/brandur-simple-protocol branch for it to work with the above change.

[kamikazechaser]

supabase they use a proprietary connection pooling system called supavisor.

Not really proprietary . But they do advertise it as an: Easy drop-in replacement for PgBouncer.

I think this should be a low priority and if possible, be pushed to the Supabase team to properly support binary protocol because river now works with pgbouncer.

[brycegoh]

Any updates on this? I am using supabase and sometimes, I do get these errors.

Would like to know what's the current situation, what's getting affected and if there are any recommended changes on my end. Thanks!

[brandur]

sometimes, I do get these errors.

@brycegoh Can you elaborate which errors you're getting? I would think that you'd either see a total failure if using Supavisor, or reasonable success if using PgBouncer.

[brycegoh]

sometimes, I do get these errors.

@brycegoh Can you elaborate which errors you're getting? I would think that you'd either see a total failure if using Supavisor, or reasonable success if using PgBouncer.

Thanks for the quick reply.

I am getting these kind of errors:

I use both normal jobs and periodic jobs with only 1 worker, both seems to be working even though I get those errors. Therefore, I am trying to understand the degree of the problem and if this is an urgent bug on my end.

Apologies as I don't 100% understand the inner workings of the prepared-statements and river etc, would appreciate some assistance as I wrap my head around this.

[JanRuettinger]

@brycegoh have you checked how many connections are open at the same time?

Re @brandur, for now I connect directly to Postgres and don't use any connection pooler as a workaround. I will try your suggested solution later. I agree that it's something Supabase should fix on their end.

[chasers]

prepared statement ... already exists

This means you're using transaction mode. You can use session mode on Supabase with Supavisor using port 5432 with your pooler url. You can also set port 6543 to session mode if you want but not necessary.

You can use two separate pool types this way (seems may be useful for this project from reading another thread).

re: listen/notify on Supavisor

Should work with session mode.

re: binary protocol

We proxy the binary protocol like pgbouncer does. Extended queries work.

re: @JanRuettinger your repro repo

This is amazing! Thanks so much.

Was able to reproduce. Seems we're getting:

ClientHandler: Receive query error: {:error, {:invalid_msg, {:tls_alert, {:bad_record_mac, 'TLS server: In state connection at tls_record_1_3.erl:351 generated SERVER ALERT: Fatal - Bad Record MAC\n decryption_failed'}}}}

Which I suspect is leaving stray connections open eventually eating up your connection pool. I will open a Supavisor issue.

[brandur]

River picked up a poll-only mode in version 0.3.0 that doesn't use listen/notify. It may help with some of the Supabase cases, if anyone wants to try it.

[brycegoh]

Hi, I am using supabase with riverqueue. I have 2 river clients using river v0.0.15:

1. A server with a river client as insert only mode (no queues specified)
2. Another server acting as the workers for the queue.

Both insert only and worker river client are connected to supabase using the session mode url.

I am getting error beginning transaction errors when inserting jobs on the insert only client. It worked when I switch the DB url on the insert only client to transaction mode though.

May I ask for some advice on this issue please. Thanks!

[brandur]

@brycegoh I'm afraid near myself nor Blake are all that familiar with Supabase, especially with regards to their connection pooler.

You're saying though that if you switch it to use the more strict transaction mode, it actually starts working? That seems odd. Is there any other detail in the error message to indicate why there was an error beginning the transaction?