S
Supabase•9mo ago
epsilon42

Timeouts after deployment when using Supavisor Session

I have the following application: - NextJS App router app - Deployed on Vercel - Supabase Postgres database - Drizzle ORM - WorkOS Authkit I have a transaction in one of my routers: 
export const pollRouter = createTRPCRouter({
  create: protectedProcedure
    .input(createPollSchema)
    .mutation(async ({ ctx, input }) => {
      return await ctx.db.transaction(async (trx) => {
        await trx.insert(a).values(...).returning();
        await trx.insert(b).values(...).returning();
        await trx.insert(c).values(...).returning();
      })
...
export const pollRouter = createTRPCRouter({
  create: protectedProcedure
    .input(createPollSchema)
    .mutation(async ({ ctx, input }) => {
      return await ctx.db.transaction(async (trx) => {
        await trx.insert(a).values(...).returning();
        await trx.insert(b).values(...).returning();
        await trx.insert(c).values(...).returning();
      })
...
This is to ensure a doesn't get inserted if b or c fails (is there a better approach to this that doesn't use Postgres transactions?) I thought this was working well but recently I discovered that sometimes when creating a poll it would intermittently fail. When I looked into it I was using the Supavisor Transaction mode connection string which didn't seem to support Postgres transactions. I forget what the message in the log was but it was something along the lines of "prepared statement not existing". I then read:
Transaction mode does not support prepared statements. To avoid errors, turn off prepared statements for your connection library.
https://supabase.com/docs/guides/database/connecting-to-postgres So I disabled it like so: 
postgres(env.DATABASE_URL, { prepare: false });
postgres(env.DATABASE_URL, { prepare: false });
However, after making this change there was now a noticeable performance drop when using my application. Everything felt sluggish. I don't have exact metrics but clicking between different pages felt almost instant before making this change. Afterwards it seems like every page click took at least a second longer. So I then explored the option of Supavisor Session mode and used this connection string instead. ...Continued below...
Connect to your database | Supabase Docs
Connect to Postgres from your frontend, backend, or serverless environment
No description
3 Replies
epsilon42
epsilon42OP•9mo ago
I thought everything was going well, but then I discovered that in the few minutes after a deployment, I would have a timeout message (see attached) when attempting to visit any page. Oddly it wouldn't normally be the first page load of the new deployment, I would need to click around the site a few times before being greeted with the timeout page. After this timeout appears for awhile, the site then functions normally and I am unable to get the timeout to reappear (unless I deploy again). I'm a bit out of my depth as I'm a frontend developer who is not entirely familiar with how all the pieces work so assume I may have missed something stupidly obvious, but was hoping someone could shed some light one what might be happening as I couldn't see anything that jumped out in the logs. As I don't experience this behaviour with Supavisor Transaction mode, I thought it may have something to do with lingering sessions. For the purpose of testing my theory, is there a way to kill these upon each Vercel deployment? Supavisor Transaction mode definitely sounds like a better fit according to the docs, but I really wanted to use Postgres transactions so it seemed like my options were to disable prepared statements (which for me resulted in a slower app), or use Session mode (timeouts after deploy). Would appreciate any help/suggestions.
garyaustin
garyaustin•9mo ago
https://supabase.com/docs/guides/database/prisma/prisma-troubleshooting I believe you need to be running in transaction mode.
Troubleshooting prisma errors | Supabase Docs
Prisma error troubleshooting
epsilon42
epsilon42OP•9mo ago
Thanks for the quick response @garyaustin. Transaction mode does seem like a better fit. Is it expected that disabling prepared statements would result in a big performance dip? Any suggestions on how I could improve things? If I don't disable prepared statements, it seemed like everything still worked and was speedy EXCEPT for Postgres "transactions". What other kinds of issues might I run into if I choose not to disable prepared statements when running Transaction mode?

Did you find this page helpful?