Re: Problem with FDW wrapper errors

On 7/14/20 10:03 AM, Michael Nolan wrote:
> An application I wrote is being modified by our development team to use
> a FDW to a remote MySQL table instead of the postgresql table for a query.
>
> We're getting this error in the logs periodically.  Maybe about once
> every 50,000 queries:
>
> 2020-07-14 11:35:22.799 CDT uscf ::1 ERROR:  failed to execute the MySQL
> query:
>         Prepared statement needs to be re-prepared
> 2020-07-14 11:35:22.799 CDT uscf ::1 STATEMENT:  select memtp,
> ('2004-12-18'::date-membirthdt)/365.25  as mage from
> memmast_simulant_ratings where memid = '13036134'
>
> If I restart it, it usually goes through, but might fail on that query,
> but using different data.
>
> Is this possibly due to transient communication issues between the
> postgresql server and the remote MySQL server

https://www.postgresql.org/docs/12/sql-prepare.html

"Prepared statements only last for the duration of the current database
session. When the session ends, the prepared statement is forgotten, so
it must be recreated before being used again. This also means that a
single prepared statement cannot be used by multiple simultaneous
database clients; however, each client can create their own prepared
statement to use. Prepared statements can be manually cleaned up using
the DEALLOCATE command."

Dropped connections could be an issue per above. You might also explore
the other conditions in the above.

>
> If I just trap the error, wait a few seconds and re-issue the query, is
> that likely to be sufficient?

I'm guessing not, you will need to issue the PREPARE again per the error
message:

"Prepared statement needs to be re-prepared"

> --
> Mike Nolan
> htfoot(at)gmail(dot)com <mailto:htfoot(at)gmail(dot)com>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com