Error initializing migrator: ERROR: prepared statement already exists (SQLSTATE 42P05)

[ChristianSch]

Hi,

I have three migrations: a pg dump in it's entirety and two subsequent, smaller changes. In the pg dump I had an ownership change and that didn't work. The migration failed. I fixed that, but since then I get the following:

Error initializing migrator:
  ERROR: prepared statement "stmtcache_f79766b7ce94605d03a610756ccc50f2971d8e6d09672dec" already exists (SQLSTATE 42P05)

I have no idea how to get rid of that. I can't deallocate it, I can't deallocate all. I did the first migration manually and bumped the version. Still running into that. One migration did work via tern, but can't reproduce/remember exactly. I can't use tern anymore because I always get this error. How do I get rid of it? I don't use any prepared statements. I can't find any code related to this and I don't see anything in my db related to this. What's going on?

[jackc]

Are you running the tern CLI or embedding the library? What version?

That error is from pgx's prepared statement cache, but it shouldn't be able to happen.

[ChristianSch]

@jackc I use cli and the version is 2.2.0

[jackc]

Well, that's very confusing. That really shouldn't be able to happen. I was wondering if you had some funny connection configuration going on underneath the tern connection, but that's not possible with the CLI.

[Arden144]

Happens to me as well. In my case I'm assuming it's because I'm using Supabase Postgres which has a connection pooler

Edit: The Supabase pooler shares prepared statements between connections https://supabase.com/blog/supavisor-postgres-connection-pooler

[jackc]

Ah, a connection pooler that shares statements. Yup, that would cause the problem.

I would suggest connecting to the server directly to perform migrations. This also allows you to have separate maintenance and application users which is also a really good idea.

But anyway, tern doesn't really need prepared statements, it's simply the default in pgx. Try #104 and see if that solves the problem.