search

jackc / tern

The SQL Fan's Migrator
MIT License
988 stars 71 forks source link

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

Open ChristianSch opened 5 months ago

ChristianSch commented 5 months ago

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 commented 5 months ago

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 commented 5 months ago

@jackc I use cli and the version is 2.2.0

jackc commented 5 months ago

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 commented 2 months ago

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 commented 2 months ago

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.