pgsql: Coerce 'unknown' type parameters to the right type in the

heikki@postgresql.org (Heikki Linnakangas) writes:

Log Message:
-----------
Coerce 'unknown' type parameters to the right type in the fixed-params
parse_analyze() function. That case occurs e.g with PL/pgSQL
EXECUTE ... USING 'stringconstant'.

The coercion with a CoerceViaIO node. The result is similar to the coercion
via input function performed for unknown constants in coerce_type(),
except that this happens at runtime.

Unfortunately, this entirely fails to enforce the rule that an unknown
Param be coerced the same way everywhere. You'd need a cleanup pass as
well, cf check_variable_parameters().

regards, tom lane

On 18/08/10 16:57, Tom Lane wrote:

heikki@postgresql.org (Heikki Linnakangas) writes:

Log Message:
-----------
Coerce 'unknown' type parameters to the right type in the fixed-params
parse_analyze() function. That case occurs e.g with PL/pgSQL
EXECUTE ... USING 'stringconstant'.

The coercion with a CoerceViaIO node. The result is similar to the coercion
via input function performed for unknown constants in coerce_type(),
except that this happens at runtime.

Unfortunately, this entirely fails to enforce the rule that an unknown
Param be coerced the same way everywhere. You'd need a cleanup pass as
well, cf check_variable_parameters().

Yeah, you're right. I'll find a way to do the cleanup pass in fixed
params case too.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On 18/08/10 18:03, Heikki Linnakangas wrote:

On 18/08/10 16:57, Tom Lane wrote:

heikki@postgresql.org (Heikki Linnakangas) writes:

Log Message:
-----------
Coerce 'unknown' type parameters to the right type in the fixed-params
parse_analyze() function. That case occurs e.g with PL/pgSQL
EXECUTE ... USING 'stringconstant'.

The coercion with a CoerceViaIO node. The result is similar to the
coercion
via input function performed for unknown constants in coerce_type(),
except that this happens at runtime.

Unfortunately, this entirely fails to enforce the rule that an unknown
Param be coerced the same way everywhere. You'd need a cleanup pass as
well, cf check_variable_parameters().

Yeah, you're right. I'll find a way to do the cleanup pass in fixed
params case too.

It turned out to be messier than I imagined, but I have a working patch
now. It still doesn't behave exactly like the variable params case,
though. To wit:

postgres=# DO $$
declare
t text;
begin
EXECUTE 'SELECT 1+ $1, $1' INTO t USING '123' ;
RAISE NOTICE '%', t;
end;
$$;
ERROR: could not determine data type of parameter $1
LINE 1: SELECT 1+ $1, $1
^
QUERY: SELECT 1+ $1, $1
CONTEXT: PL/pgSQL function "inline_code_block" line 5 at EXECUTE statement

The varparams code doesn't thrown an error on that. At the first
reference to $1, the parameter is resolved to int4. On the 2nd
reference, it's an int4 and there's nothing to force coercion to
anything else, so it's returned as an int4. In the fixed params case,
however, that throws an error. We could make it behave the same if we
really wanted to, but that would add even more code.

I'm starting to wonder if it's worth enforcing the rule that all unknown
Params must be coerced to the same target type. We could just document
the behavior. Or maybe we should revert the whole thing, and add a check
to PL/pgSQL EXECUTE USING code to just throw a nicer error message if
you pass an unknown parameter in the USING clause.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

I'm starting to wonder if it's worth enforcing the rule that all unknown
Params must be coerced to the same target type. We could just document
the behavior. Or maybe we should revert the whole thing, and add a check
to PL/pgSQL EXECUTE USING code to just throw a nicer error message if
you pass an unknown parameter in the USING clause.

+1 for the latter. There's no good reason to be passing unknowns to USING.
I'm also getting more and more uncomfortable with the amount of new
behavior that's being pushed into an existing SPI call.

Another possibility is for EXECUTE USING to coerce any unknowns to TEXT
before it calls the parser at all. This would square with the typical
default assumption for unknown literals, and it would avoid having to
have any semantics changes below the SPI call.

regards, tom lane

On Thu, Aug 19, 2010 at 9:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Another possibility is for EXECUTE USING to coerce any unknowns to TEXT
before it calls the parser at all.  This would square with the typical
default assumption for unknown literals, and it would avoid having to
have any semantics changes below the SPI call.

That seems more intuitive than just chucking an error.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Aug 19, 2010, at 8:08 AM, Robert Haas wrote:

Another possibility is for EXECUTE USING to coerce any unknowns to TEXT
before it calls the parser at all. This would square with the typical
default assumption for unknown literals, and it would avoid having to
have any semantics changes below the SPI call.

That seems more intuitive than just chucking an error.

It'd be nice if SPI itself could work this way for UNKNOWNs, too.

Best,

David

On 19/08/10 18:08, Robert Haas wrote:

On Thu, Aug 19, 2010 at 9:47 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Another possibility is for EXECUTE USING to coerce any unknowns to TEXT
before it calls the parser at all. This would square with the typical
default assumption for unknown literals, and it would avoid having to
have any semantics changes below the SPI call.

That seems more intuitive than just chucking an error.

Ok, I reverted the previous patch, and did that instead.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com