Re: [BUGS] BUG #14483: Prepared statement does not re-plan while itshould

> 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.

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs