Re: [HACKERS] Overly strict casting rules? (was: proposal: fix corner use case of variadic fuctions usage)

On 01/21/2013 02:37 AM, Robert Haas wrote:
> On Sat, Jan 19, 2013 at 11:58 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> We introduced VARIADIC "any" function. Motivation for this kind of
>> function was bypassing postgresql's coerce rules - and own rules
>> implementation for requested functionality. Some builtins function
>> does it internally - but it is not possible for custom functions or it
>> is not possible without some change in parser. Same motivation is
>> reason why "format" function is VARIADIC "any" function.
> I'd just like to draw the attention of all assembled to the fact that
> this is another example of the cottage industry we've created in
> avoiding our own burdensome typecasting rules. I not long ago
> proposed a patch that went nowhere which would have obviated the need
> for this sort of nonsense in a much more principled way, which of
> course went nowhere, despite the design being one which Tom himself
> proposed. Is there any amount of this which will sway popular opinion
> to the point of view that the problem is not with the individual
> cases, but the rules themselves?
>
FWIW, I find PostgreSQL's type casting rules excessively strict and very
painful, especially when working via query generation layers and ORMs
with pseudo-text data types like "xml" and "json". I'd rather work with
direct SQL, but that's not always an option.

The fact that this works:

regress=> CREATE TABLE castdemo(x xml);
CREATE TABLE
regress=> INSERT INTO castdemo(x) VALUES ('<element/>');
INSERT 0 1

but there's no way to express it via a parameterized insert unless you
know that the field type is "xml" is frustrating. There's no "unknown"
type-placeholder in prepared statements, and we'd never get client
interfaces to use one if there was. I almost invariably create implicit
casts from text to xml and json so that this works:

regress=> PREPARE paraminsert(text) AS INSERT INTO castdemo(x) VALUES ($1);

instead of failing with:

regress=> PREPARE paraminsert(text) AS INSERT INTO castdemo(x) VALUES ($1);
ERROR: column "x" is of type xml but expression is of type text
LINE 1: ...RE paraminsert(text) AS INSERT INTO castdemo(x) VALUES ($1);
^
HINT: You will need to rewrite or cast the expression.

JDBC users in particular will find the strict refusal to convert "text"
to "xml" or "json" to be very frustrating. The JDBC driver has - AFAIK -
no way to ask the server "In the statement INSERT INTO castdemo(x)
VALUES ($1) what data type do you expect for '$1'" ... nor would it need
one if the server weren't so strict about these casts.

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