Re: Allowing implicit 'text' -> xml|json|jsonb

On 09/07/2014 02:24 AM, Tom Lane wrote:
>>> >> The problem here seems to be only related to mistyped parameters. Can
>>> >> we contain the damage to that part only somehow? Or make this optional
>>> >> (defaulting to off, I hope)?
>> > I'd love to make it affect only parameters, actually, for v3 protocol
>> > bind/parse/execute. That would be ideal.
> Well, let's talk about that. Doing something with parameter type
> assignment seems a lot less likely to result in unexpected side-effects
> than introducing a dozen new implicit casts.

I think it'd meet the needs of the group of users I see running into
issues and would minimise impact, so that sounds good if it's practical.

However, see below. It looks like just sending 'unknown' instead of
'text' for strings from drivers might be the way to go. My concerns
about introducing new overloads may have been unfounded.

>> > Right now the main workaround is to send all string-typed parameters as
>> > 'unknown'-typed, but that causes a mess with function overload
>> > resolution, and it's wrong most of the time when the parameter really is
>> > just text.
> If you think adding implicit casts *won't* cause a mess with function
> overload resolution, I wonder why.
>
> Really though it seems like the question is how much clarity there is
> on the client side about what data types parameters should have.
> I get the impression that liberal use of "unknown" is really about
> the right thing in a lot of client APIs ...

So we'd be going down the path of asking client drivers to change how
they bound string-type parameters to 'unknown' by default, or asking
users to routinely change that default.

Thinking about it some more, that's really no different to how things
work right now when you write unparameterised queries using literals
without an explicit type-specifier or cast. Pg will resolve an
unknown-typed literal to text if there's ambiguity and one of the
choices is a text-type.

e.g. with md5(text) vs md5(bytea), if you call it with an unknown-typed
literal the text form is chosen:

regress=> SELECT md5('abcdef');
md5
----------------------------------
e80b5017098950fc58aad83c8c14978e
(1 row)

same as if you bind an explicitly unknown-typed parameter:

regress=> PREPARE md5p(unknown) AS SELECT md5($1);
PREPARE
regress=> EXECUTE md5p('abcdef');
md5
----------------------------------
e80b5017098950fc58aad83c8c14978e
(1 row)

In fact, to my surprise, using 'unknown' won't break callers who
currently send an explicit 'text' type when there's a 'varchar' overload
of the function:

regress=> create or replace function identity(varchar) returns text
language plpgsql as $$ begin raise notice 'varchar'; return $1; end; $$;
CREATE FUNCTION
regress=> create or replace function identity(text) returns text
language plpgsql as $$ begin raise notice 'text'; return $1; end; $$;
CREATE FUNCTION

regress=> SELECT identity('fred');
NOTICE: text
identity
----------
fred
(1 row)

regress=> PREPARE identity_text(text) AS SELECT identity($1);
PREPARE
craig=> EXECUTE identity_text('fred');
NOTICE: text
identity
----------
fred
(1 row)

regress=> PREPARE identity_unknown(unknown) AS SELECT identity($1);
PREPARE
craig=> EXECUTE identity_unknown('fred');
NOTICE: text
identity
----------
fred
(1 row)

regress=> PREPARE identity_varchar(varchar) AS SELECT identity($1);
PREPARE
regress=> EXECUTE identity_varchar('fred');
NOTICE: varchar
identity
----------
fred
(1 row)

So - if a driver currently sends 'varchar' for string types, and the
user has a 'varchar' and 'text' overload of the same function defined,
it'd change the overload selected. That's a (tiny) BC break.

Perhaps the solution here is just to make 'unknown' the default for
stirng-types in client drivers, make sure people have a way to change it
back, and relnote it clearly in the driver release?

In PgJDBC that's just a matter of changing the default for 'stringtype'
to 'unknown' in the 9.4 release.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services