Error during upload - prepared statement already exists

[crisptrutski]

Describe the bug

When creating a model from an uploaded CSV a user received ERROR: prepared statement "S_1" already exists.

See: https://metaboat.slack.com/archives/C05MPF0TM3L/p1712664980135029

To Reproduce

1. Use postgres as the upload database.
2. Upload the same file a number of times
3. See the error

Expected behavior

Uploads should continue to succeed

Logs

No response

Information about your Metabase installation

- Metabase version: master (~50.0)

Severity

P3

Additional context

No response

[crisptrutski]

The issue appears to relate to the server side pooling implementation used by Supabase for "transaction mode" connections. These connections are designed for use with serverless applications, not a session based application like Metabase which uses its own connection pools.

The issue is also intermittent - retrying can succeed, I guess when the connection you get from Metabase's connection pool is not one that thinks its already run a particular statement (but Supabase was switched out the actual underlying connection in its proxy).

[crisptrutski]

As far as I can tell the issue only affects connection that use explicit transactions - when the transaction is closed it indicates to the Supabase proxy that it can return the underlying connection to its internal pool. Since Uploads are the only place where we use transactions, the scope should be quite narrow - especially since it's unlikely that users would need to upload the same data multiple times within a short time (the duration our pool keeps connections alive).

Perhaps something like the following:

Error executing prepared statement - this may be caused by using a transaction or statement based connection pool. Make sure to use Session level pooling if this is the case.

This would give them the relevant search terms to use with Supabase, Supavisor, PgBouncer, etc. It's possible there are other proxies out there that use other terminology though.

[calherries]

I labelled this P3 as we discussed it should be relatively rare and has an easy workaround (use the correct mode for the connection)

[crisptrutski]

To help anyone finding this issue when having issues with Supabase, the solution is to change from the tx port (6543) to the session port (5432).