Обсуждение: BUG #11402: Prepared statement cache invalidation and unknown types

The following bug has been logged on the website:

Bug reference:      11402
Logged by:          Marko Tiikkaja
Email address:      marko@joh.to
PostgreSQL version: 9.4beta2
Operating system:   Any
Description:

Hi,

This might have been reported before, but I hit this quite horrible
behaviour with prepared statements this week.  Observeth:

local:marko=# create table foo(a int);
CREATE TABLE
local:marko=#* prepare qwr(unknown) as select * from foo where a = $1;
PREPARE
local:marko=#* execute qwr('1');
 a
---
(0 rows)

local:marko=#* alter table foo alter column a type text;
ALTER TABLE
local:marko=#* execute qwr('1');
ERROR:  operator does not exist: text = integer
HINT:  No operator matches the given name and argument type(s). You might
need to add explicit type casts.

I hit the behaviour on 9.1 originally with functions, but tested it against
a reasonably fresh HEAD and it was still broken.  Somehow it seems that the
prepared statement isn't willing to forget the data type of the argument
after it has been resolved once (even though it was specified to be unknown
:-( ).  This leads to some grotesque cache invalidation code in our API.

If this has been discussed before and has been decided to not be worth
fixing, feel free to call me names, but I just thought I'd bring it to your
attention before I forget again.

marko@joh.to writes:
> I hit the behaviour on 9.1 originally with functions, but tested it against
> a reasonably fresh HEAD and it was still broken.  Somehow it seems that the
> prepared statement isn't willing to forget the data type of the argument
> after it has been resolved once (even though it was specified to be unknown
> :-( ).  This leads to some grotesque cache invalidation code in our API.

I'm not exactly convinced that this is a bug.  Having a prepared statement
silently change its input parameter types seems a lot more dangerous than
just failing; especially since we lack any signaling mechanism by which
the client application could be warned that whatever it may have known
about the statement is now invalid.

For largely the same reason, we don't allow a prepared statement to
change output column types:

regression=# create table tt (f1 int);
CREATE TABLE
regression=# insert into tt values(1);
INSERT 0 1
regression=# prepare foo as select * from tt;
PREPARE
regression=# execute foo;
 f1
----
  1
(1 row)

regression=# alter table tt alter column f1 type real;
ALTER TABLE
regression=# execute foo;
ERROR:  cached plan must not change result type

I'm not sure why we would think that changing input parameter types is any
safer.

            regards, tom lane

On 9/12/14, 3:55 AM, Tom Lane wrote:
> marko@joh.to writes:
>> I hit the behaviour on 9.1 originally with functions, but tested it against
>> a reasonably fresh HEAD and it was still broken.  Somehow it seems that the
>> prepared statement isn't willing to forget the data type of the argument
>> after it has been resolved once (even though it was specified to be unknown
>> :-( ).  This leads to some grotesque cache invalidation code in our API.
>
> I'm not exactly convinced that this is a bug.  Having a prepared statement
> silently change its input parameter types seems a lot more dangerous than
> just failing; especially since we lack any signaling mechanism by which
> the client application could be warned that whatever it may have known
> about the statement is now invalid.

Yeah, that's probably fair.  I guess it's possible for an app to use
unknown type parameters and then ask the database about the data types
and do something different based on that.

I'll just have to live with the (increasingly) horrible logic.  Thanks
for your reply.


.marko