New prepared Statement scheme might be causing errors like: expected 0 arguments, got 2

[edulop91]

Describe the bug
We upgraded from v5.4.3 -> v5.5.0 and started getting a ton of errors around a mismatch in arguments to our SQL queries. These look like expected 0 arguments, got 2.

We downgraded back to v5.4.3 and the errors immediately disappeared again.

I haven't gotten to the bottom of it, but saw in the changelog some changes wrt the way prepared statements are created/referenced. I assume it has something to do with the prepared statement caching, but haven't investigated too far yet.

Instead, might also need to deallocate prepared statements from the DB server side as part of the pgx version upgrade.

To Reproduce
I still need to figure out a self-contained repro. We're using https://github.com/ent/ent

Expected behavior
SQL statements and # of arguments match.

Actual behavior
See above.

Version

• Go: go1.21 linux/amd64
• PostgreSQL: AWS RDS Aurora 15.4
• pgx: v5.5.0

[simonjpartridge]

I am in the process of migrating my services to pgx from lib/pq. I believe I am encountering a similar problem too.

I get errors like this in 5.5.0 but downgrading to 5.4.3 makes the problem go away. I've just tried to reproduce in a simple way but not having any luck at the moment!

"calling batchCreateOwnershipPathResolvedProportionTx: preparing: ERROR: prepared statement \"stmt_2ab41dee0111313feebb84b22dbb7b4f350392a802642c1f\" already exists (SQLSTATE 42P05)

[oschwald]

I am not sure if this is one issue or two, but we have also started seeing this after upgrading to 5.5.0:

ERROR: prepared statement "stmtcache_8619f6359b70b157fe882bf94474e3e1af9f565486e3d791" already exists (SQLSTATE 42P05)

[jackc]

PR #1797 fixes some prepared statement issues. I think it might resolve this, but it's hard to know without a test case. Can you all test this?

[edulop91]

Looks like this did the trick for us, thank you @jackc ! i think I spoke prematurely

[edulop91]

I've also gotten a couple of BUG: cannot unlock unlocked connection on #1797

[jackc]

@edulop91 That should be a panic so there should be a stack trace. Can you post that? And if possible a reproducible test would be great.

[simonjpartridge]

This fixed my issue, around prepared statement already exists. Thanks so much 🎉 .

Do you have plans to cut a release any time soon with this change?

[jacobmikesell]

@jackc FYI we upgraded but we are now occasionally seeing this issue on 5.5

Its VERY rare (~ 1/10k requests) and it looks like things are working?

trace info for the prepare

prepare_already: false
prepare_name: stmtcache_310ac7c77a75dddb45900e194293aef4111cbcdb3890c4ce
prepare_sql: /*{trace comment info injected}*/ 
select * from my_table where date = $1;

query_type: pgx.prepare

it also seems like the code still executes against the database (though this might be an anomoly in our tracing stuff)
despite the trace ending with the error expected 0 arguments, got 1

[jackc]

@jacobmikesell Can you try current master? I think that #1797 should have fixed this.

@edulop91 Can you provide an example that demonstrates the panic or at least a stack trace? I'd like to tag a release with this fix, but I'd like to make sure it is totally fixed. Also, if you can run your example with the race detector that would be helpful. I reviewed the connection lock/unlock code this morning and was unable to find anything that might be causing the issue.

[jackc]

Given that at least the original issue has been fixed, I released v5.5.1 so everyone can use it.

I think the other issues brought up on this thread may have different causes. I'm going to close this issue. Please open new issue(s) for them.