Re: BUG #14483: Prepared statement does not re-plan while it should

2017-01-03 8:25 GMT+01:00 CN <cnliou9(at)fastmail(dot)fm>:

> > This behavior is intentional: it's to keep applications from having to
> > deal with the possibility that they prepare a statement, Describe it
> > to find out what columns it returns, and then when they actually execute
> > it, it returns some other column set.
>
> Many thanks for the clarification!
>
> I am not sure if it is appropriate to turn this bug report into feature
> request, again. We will get several results by searching key phrase
> "cached plan must not change result type".
>
> It will be much easier for me to use prepared statements if they are
> automatically re-parsed after any DDL that affects them or "SET
> SEARCH_PATH TO" is executed. I believe this is especially true where
> connection pool products are used.
>
>
Is good to switch SEARCH_PATH only on session start - or reset session
before.

Regards

Pavel

> Even without using connections pooling, the following example shows that
> simple usage can also cause production applications to malfunction -
> they get the error when developers are also changing the database
> schema:
>
> ====
> test=# CREATE TABLE t1 (c1 INTEGER);
> CREATE TABLE
> test=# PREPARE p1 AS SELECT c1 FROM t1;
> PREPARE
> test=# EXECUTE p1;
> c1
> ----
> (0 rows)
>
> test=# ALTER TABLE t1 ALTER c1 TYPE TEXT;
> ALTER TABLE
> test=# EXECUTE p1;
> ERROR: cached plan must not change result type
> ====
>
> Once an application encounters this problem, disconnecting from the
> database and reconnecting back, which usually means restarting the
> application, seems to be the only way to "fix" it.
>
> Although deallocating prepared statements wherever necessary ultimately
> avoids this issue, it is complicated for me to correctly implement such
> applications. As a result, I currently avoid using prepared statements
> altogether and hence obviously greatly degrade the overall performance.
>
> Best Regards,
> CN
>
> --
> http://www.fastmail.com - Choose from over 50 domains or use your own
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>