Обсуждение: 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