Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for Supabase/Supavisor #238

Open
brandur opened this issue Feb 28, 2024 · 16 comments
Open

Support for Supabase/Supavisor #238

brandur opened this issue Feb 28, 2024 · 16 comments

Comments

@brandur
Copy link
Contributor

brandur commented Feb 28, 2024

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
Copy link

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
Copy link
Contributor Author

brandur commented Feb 28, 2024

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
Copy link
Contributor Author

brandur commented Feb 28, 2024

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
Copy link

JanRuettinger commented Feb 28, 2024

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 #205 (comment) a potential fix (b2cb142) 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
Copy link
Contributor Author

brandur commented Feb 29, 2024

@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 #205 (comment) a potential fix (b2cb142) 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
Copy link

JanRuettinger commented Feb 29, 2024

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
Copy link
Contributor

@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
Copy link
Contributor

kamikazechaser commented Mar 3, 2024

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
Copy link

brycegoh commented Mar 6, 2024

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
Copy link
Contributor Author

brandur commented Mar 6, 2024

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
Copy link

brycegoh commented Mar 6, 2024

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:
image

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
Copy link

@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
Copy link

chasers commented Mar 8, 2024

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
Copy link
Contributor Author

brandur commented Apr 23, 2024

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
Copy link

brycegoh commented Aug 12, 2024

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
Copy link
Contributor Author

brandur commented Aug 18, 2024

@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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

6 participants