ERROR: cached plan must not change result type

[geekontheway]

Hi,

We runs mutiple Ruby on Rails Project and started to using pgbouncer recently, it is a good tool 👍.
However we are having an issue that has documented: ERROR: cached plan must not change result type

ERROR:  cached plan must not change result type

You can avoid such errors by not having multiple clients that use the exact
same query string in a prepared statement, but expecting different argument or
result types. One of the most common ways of running into this issue is during
a DDL migration where you add a new column or change a column type on an
existing table. In those cases you can run RECONNECT on the PgBouncer admin
console after doing the migration to force a re-prepare of the query and make
the error goes away.

My question is that do we need to mannully run the RECONNECT command on every pgbouncer client server every time after the database migration?

[Melkij]

Yes, after each migration that may change the result. For example, it is clear that create table/create index/drop index/etc cannot change the type of the result.

A patch for postgresql is proposed to remove this limitation: https://commitfest.postgresql.org/47/4518/

[geekontheway]

@Melkij Thanks for your kindly respond 💐 . do we have better ways other than mannully open the admin console and run reconnect, I mean, when you have pgbouncer on mutiple different servers, like, before the code deployment start, execute a shell script to connect to the admin console and run reconnect?

[alphavector]

It seems that after @knizhnik's recent changes under PR #972, we can call DEALLOCATE ALL directly in postgres, instead of an additional connection directly to pgbouncer and calling RECONNECT as described in the documentation. No migration tool I know of supports any additional operations on pgbouncer, and with DEALLOCATE ALL we could write it to the end of the migration file as a workaround, e.g.

alter table sample alter column col type;
DEALLOCATE ALL;

But this is just a theory, @knizhnik or @JelteF could help confirm or deny this

[JelteF]

DEALLOCATE ALL only applies to the current connection. So no that does not fix the issue.