PostgresError: cached plan must not change result type

[michael-land]

1|server   | PostgresError: cached plan must not change result type
1|server   |     at Object.ErrorResponse (node_modules/postgres/lib/backend.js:130:33)
1|server   |     at TLSSocket.data (node_modules/postgres/lib/connection.js:233:25)
1|server   |     at TLSSocket.emit (events.js:311:20)
1|server   |     at TLSSocket.EventEmitter.emit (domain.js:482:12)
1|server   |     at addChunk (_stream_readable.js:294:12)
1|server   |     at readableAddChunk (_stream_readable.js:275:11)
1|server   |     at TLSSocket.Readable.push (_stream_readable.js:209:10)
1|server   |     at TLSWrap.onStreamRead (internal/stream_base_commons.js:186:23)
1|server   |     at query (node_modules/postgres/lib/index.js:169:36)
1|server   |     at Object.postgres [as sql] (node_modules/postgres/lib/index.js:70:12)
1|server   |     at SalesOrdersController.fulfill (dist/apps/server/main.js:2042:29)
1|server   |     at node_modules/@nestjs/core/router/router-execution-context.js:38:29
1|server   |     at runMicrotasks (<anonymous>)
1|server   |     at processTicksAndRejections (internal/process/task_queues.js:97:5)
1|server   |     at async node_modules/@nestjs/core/router/router-execution-context.js:46:28
1|server   |     at async node_modules/@nestjs/core/router/router-proxy.js:9:17 {
1|server   |   name: 'PostgresError',
1|server   |   severity_local: 'ERROR',
1|server   |   severity: 'ERROR',
1|server   |   code: '0A000',
1|server   |   file: 'plancache.c',
1|server   |   line: '723',
1|server   |   routine: 'RevalidateCachedQuery',
1|server   |   query: 'SELECT * FROM sales_order WHERE id = $1',
1|server   |   parameters: [ { type: 0, value: '848315' } ]
1|server   | }

After googling I found this answer

I was able to reliably reproduce the error by running schema upgrades (i.e. DDL statements) while my back-end app that used the DB was running. If the app was querying a table that had been changed by the schema upgrade (i.e. the app ran queries before and after the upgrade on a changed table) - the postgres driver would return this error because apparently it does caching of some schema details.
source

My question is how to handle this kind of error?

I want catch the exception, close the prepared statement and recreate a new one with the same query string. anyone how to achieve that?

[porsager]

Sorry for the late reply. This is a good case! I'm wondering if maybe Postgres.js should simply clear the cached statement and rerun the query automatically again. Would that be fine in your case?

[karlhorky]

I'm also running into this error with this function in my demo application:

2021-03-11 10:07:06.601 CET [40141] ERROR:  cached plan must not change result type
2021-03-11 10:07:06.601 CET [40141] STATEMENT:  DELETE FROM
	      sessions
	    WHERE
	      expiry < NOW()
	    RETURNING *

Not sure if I completely understand what's going on, but I guess if the query was triggered somehow to be run (but didn't because of a change in the schema due to something like migrations), clearing the cache and re-running the query would not have any big downsides?

[porsager]

It'd be great if you could try the recreate prepared statements branch https://github.com/porsager/postgres/tree/retry-invalidated-prepared-statements