AppSheet + Supabase: Since new Pooler, the connection is basically impossible to work with

Open

SkrOYCopened 2y ago

Bug report

Describe the bug

When using port "5432" things work with the obvious limits. AppSheet (a no-code platform owned by Google) opens a connection for each CRUD operation and this was solved by using the pooler on port "6543". Everything worked perfect untill PgBouncer was left aside. It justs seems using the pooler's port is not a transparent solution from the client's side. Since this is a platform not managed by me I don't know the ins and outs of it, but I can say this was working perfectly before with PgBouncer.

To Reproduce

Use AppSheet and connect to the database using the Postgres connector with the 5432 port. Everything works perfect. Change it to 6543 and you will face timeout issues. I can't add tables to my apps nor sync data on others apps already working with 5432.

Expected behavior

That the connection to the database under 6543 should work the same way 5432 does, being transparent on the client's side.

Additional info

This is a post in Appsheet's official community I made to promote Suabase and where users have been reporting the issue, which I was able to confirm https://www.googlecloudcommunity.com/gc/Tips-Tricks/Updated-Supabase-mets-AppSheet/m-p/442135/highlight/true#M6685

Activity

encima2y ago

Thanks for opening! To confirm, you have 3 options to connect with AppSheet:

Direct - 5432 - This is a direct connection to the database
Pooler - Transaction - 6543 - This is a connection to your database through the Pooler where the connection is opened only for the lifetime of a transaction
Pooler - Session - 5432 - This is a connection to your database through the Pooler where the connection is open until the client disconnects (so you are likely to hit connection limits here)

AppSheet may recommend connection types but I would typically go through the Pooler's transaction (and modify timeouts if needed) rather than going through session as the client may keep connections open longer than needed so they are not freed for others.

Read more here

Let us know if that answers your question!

SkrOYC2y ago

That's kinda what I already knew about it. The issue in this case is that I need transaction mode and it just doesn't work at all

encima2y ago

"Doesn't work at all" - Do you have more info on this? What are the logs on your instance reporting for the timeouts? What is the instance size and pool size you have configured?

Your Expected behaviour section expects Transaction mode to work the same as a direct connection but this is not the case. Session mode works the same as a direct connection.

SkrOYC2y ago

What I mean to what I expect is that from the client side we should see things working since Transaction mode has benefits on top of what Session mode can offer. I can connect using Session mode but if I change to port 6543 to not have issues with open connections (since the platform I'm using doesn't close them) it doesn't do anything other than authenticating. I can't sync data. I could do some troubleshooting if you tell me where I should find the logs for the sync attempts

encima2y ago

Noted, thanks for the extra info. I have a gut feeling that the outcome of this will be: "AppSheet does not support transaction mode when connecting to Postgres"

But, I am not sure so let's confirm this: you can check your Pooler logs in the dashboard under Logs by clicking Pooler.

SkrOYC2y ago

About the your first comment, my only knowledge about the way AppSheet works under the hood is that it was working wonderfully before with PgBouncer. I expect it to work as good as before with Supavisor but for some reason it doesn't. I though Supavisor was supposed to be a drop in replacement for PgBouncer. If there is a difference in the way Supavisor exposed the database in comparison to PgBouncer, that should be the issue.

I'll do some troubleshooting and post the logs here.

Thanks for your help!

encima2y ago

Hey @SkrOYC

That is a good point and I am not sure if the switch was called out as pgbouncer was set to transaction mode by default. When you say AppSheet worked with pgBouncer was that after changing to session mode as well?

encima2y ago

Closing due to inactivity

SkrOYC2y ago

Finding time to keep troubleshooting. This is still an issue. In the meantime, I think we need to confirm from the people that was tied to the supavisor implementation if there is any difference in the way it behaves compared to pgbouncer that may make this not a 1:1 replacement to pgbouncer

SkrOYC2y ago

Anyone has any idea about why the new Supavisor integration was not a 1:1 replacement to pgBouncer?

SkrOYC2y ago

I tried with a fresh database, so no "pgBouncer pending removal" issue. Transaction mode, new app inside AppSheet instead of an already working one and no luck. It doesn't work. These are the logs when I tried to connect

encima2y ago

Transferring to the Supavisor repo so the @supabase/pooler team are aware

SkrOYC2y ago

Hi everyone. I have a client wanting to use Supabase but still laking a solution on this

encima2y ago

Checking in with the Pooler team, @SkrOYC, thank you for the ping!

Khoanguyen01092y ago

could i know how long will it take to help resolve this issue?

encima2y ago

Difficult to estimate, we will update this issue with any updates so just be sure to subscribe for notifications!

NguyenHoadev2y ago

Difficult to estimate, we will update this issue with any updates so just be sure to subscribe for notifications!

Have you fixed this bug yet?

NguyenHoadev2y ago

??????????????????????????????

SkrOYC2y ago

Hi @encima. Is there anyone in particular in the Supavisor team that could help us with testing? This has been left aside for months. Thanks

encima2y ago

Hey @SkrOYC The pooler team is aware of this issue and it is currently in their backlog