Re: [HACKERS] Why type coercion is not performed for parameters?

[Tom Lane]

Konstantin Knizhnik  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'::typename
typename_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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Why type coercion is not performed for parameters?

[Konstantin Knizhnik]

On 05.05.2017 13:29, Marko Tiikkaja 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.



Yes, in case of integer literal I can easily determine parameter type.
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

Re: [HACKERS] Why type coercion is not performed for parameters?

[Marko Tiikkaja]

On Fri, May 5, 2017 at 10:58 AM, Konstantin Knizhnik <
k.knizh...@postgrespro.ru> wrote:

> 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

[HACKERS] Why type coercion is not performed for parameters?

[Konstantin Knizhnik]

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

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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers