handling "cached plan must not change result type" errors

[kmulvey]

I have recently been getting hit by the following error: "cached plan must not change result type (SQLSTATE 0A000)" and i found some recent PRs that addressed it #865 and corresponding pgconn pr. I saw the notes in these PRs as well as the original issue #841 where its mentioned: "We don't try to get fancy with retries or anything like that, just return the error and allow the application to handle it.".

Im struggling to understand how to handle the error, my application is fairly flexible in what could be done to fix the query, so here are a few questions:

1. Where does this error come out from my point of view? From the Query call:
   conn.Query("") or the rows.Next ?

     for rows.Next() {
         var err = rows.Err()
         if err != nil {
         ...
         }

2. I think i read in one of the issues that 0A000 is used for multiple types of errors so should i be inspecting the text of the error via this ?
3. Once I've trapped the error, I need to clear the statement cache, correct? If so can i run conn.StatementCache().Clear() and then retry the query? Or is there something more that needs to be done?

thank you in advance for your help.

[jackc]

The statement cache should automatically purge clear statements that fail with that error. All the application needs to do is retry the query.

See this test for an example:

pgx/conn_test.go

Line 883 in 922508c

func TestStmtCacheInvalidationConn(t *testing.T) {

1. It's an implementation detail whether an error is returned immediately by Query or is only available in rows.Err() after the query is complete. Any error that occurs will be available in rows.Err() at the end so I typically handle errors there.
2. Yes. Here is how pgconn does the check internally: https://github.com/jackc/pgconn/blob/9cf57526250f6cd3e6cbf4fd7269c882e66898ce/stmtcache/lru.go#L91
3. The bad statement is automatically cleared. If you want to clear the whole cache manually because you assume more statements are broken you can.

[kmulvey]

thanks, closing