Обсуждение: [HACKERS] Why type coercion is not performed for parameters?
Hi hackers, If I evaluate expression typename('literal'), then type coercion is performed and the function is successfully resolved, i.e. SELECT regnamespace('"pg_catalog"'); But if I want to prepare this query, I get the error: postgres=# prepare foo as SELECT regnamespace($1); ERROR: function regnamespace(unknown) does not exist LINE1: prepare foo as SELECT regnamespace($1); Certainly, I can explicitly specify parameter type: prepare foo (text) as SELECT regnamespace($1); and it will work. But it is not always possible. Actually coerce_type function can normally handle parameters. But func_get_detail always allows coercion only for constants: if (sourceType == UNKNOWNOID && IsA(arg1, Const)) { /* always treat typename('literal')as coercion */ iscoercion = true; } If this condition is changed to: if (sourceType == UNKNOWNOID && (IsA(arg1, Const) || IsA(arg1, Param))) then the example above will normally work. Why do I need it? I want to implement autoprepare. My original intention was to let parse_analyze_varparams to infer type of parameters from the context. But it is not always possible and sometime leads to different behavior of query. For example if the query: select count(*) from test_range_gist where ir @> 10; is replaced with select count(*) from test_range_gist where ir @> $1; then type of parameter will be int4range rather then int, which corresponds to the different operator. This is why now I infer parameter type from literal value. But in this case I get errors in parse_analyze_varparams which is not able to resolve some functions. The fix in func_get_detail functions solves the problem and doesn't cause some new issues: all regression tests are passed. So my question is whether it is possible to use the same rule for type coercion of parameters as for constant? -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On Fri, May 5, 2017 at 10:58 AM, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:
But you know that the type of the literal "10" is int. If you're throwing that information away, surely that's a bug in your code.
If I evaluate expression typename('literal'), then type coercion is performed and the function is successfully resolved, i.e.
SELECT regnamespace('"pg_catalog"');
But if I want to prepare this query, I get the error:
postgres=# prepare foo as SELECT regnamespace($1);
ERROR: function regnamespace(unknown) does not exist
LINE 1: prepare foo as SELECT regnamespace($1);
Certainly, I can explicitly specify parameter type:
prepare foo (text) as SELECT regnamespace($1);
and it will work. But it is not always possible.
There are other similar examples which have even bigger issues, such as now() - interval '6 hours'. now() - interval $1 won't even parse.
Why do I need it? I want to implement autoprepare.
My original intention was to let parse_analyze_varparams to infer type of parameters from the context.
But it is not always possible and sometime leads to different behavior of query.
For example if the query:
select count(*) from test_range_gist where ir @> 10;
is replaced with
select count(*) from test_range_gist where ir @> $1;
then type of parameter will be int4range rather then int, which corresponds to the different operator.
But you know that the type of the literal "10" is int. If you're throwing that information away, surely that's a bug in your code.
.m
On 05.05.2017 13:29, Marko Tiikkaja wrote:
Yes, in case of integer literal I can easily determine parameter type.But you know that the type of the literal "10" is int. If you're throwing that information away, surely that's a bug in your code.
But in case of string literal I have to set UNKNOWNOID type otherwise a lot of queries will not work.
.m
-- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes: > So my question is whether it is possible to use the same rule for type > coercion of parameters as for constant? It's not possible, I think, and even if it is, we would almost certainly reject a patch that tried to do it. There are four different ways to spell type coercion of a literal: cast('foo' as typename)'foo'::typenametypename_thats_a_simple_identifier('foo')typename 'foo' The last of those is a huge syntactic PITA because it's so close to being ambiguous against other constructs. If you tried to allow anything but a string literal there, it almost certainly would be ambiguous, resulting in bison failures. Even if you managed to wedge it into the grammar today, I for one would vote to reject the patch because of the near certainty that it would result in syntactic conflicts further down the line. The others are better for your purposes, because at least syntactically they allow either a literal or something else as the subject of the coercion. But there's still an important point I think you're missing, which is that even though these syntaxes look like type coercion (that is, run-time conversion of values from one type to another), they are not that when applied to a literal string. Instead they represent initial assignment of a type to the literal; so they feed the string to the type's typinput function and then produce a Const with a resolved type, not a type-coercion expression node. Params are sort of a mess because depending on parser context, an attempt to coerce them might result in either a runtime type coercion, or a decision that a previously-indeterminate-type Param is now of a known type. The latter bears some similarities to assignment of a type to an unknown literal, but it's not the same thing. The code you are looking at in func_get_detail() can handle the situation where the argument is a literal, because it knows what coerce_type() will do in that case. However, it does not know what coerce_type() would do with a Param, and it can't readily find out because that information is hidden behind a parser-hook API. As noted in the comments in func_get_detail, we *must not* return FUNCDETAIL_COERCION unless we know that coerce_type will succeed, and we do not know that for the case you are concerned with. I could imagine extending the parser hook API, in the direction of adding a function that can be asked "if we were to call p_coerce_param_hook on this Param, would that be interpreted as a type assignment?". But that seems ugly: it's only squishily defined, and it would require a bunch of places to supply additional hook code. Given that you're not going to get anywhere with the "typename $1" syntax, I don't see much point in complicating the parser hook API to resolve the third case. You need to think of a different way to approach what you're trying to do. Personally I'd think about replacing the entire literal-with-cast construct with a Param having already-known type. regards, tom lane