Обсуждение: Type cast removal - proposed exceptions for xml,enum

Hi all

I'm finding a few areas where PostgreSQL's refusal to implicitly cast
from 'text' to another type is causing real problems, particularly when
using the PgJDBC driver. I'd like to propose a couple of relaxations of
the implicit cast rules for certain text-like types:

- user-defined enums; and
- xml

('json' should also be castable from text when introduced).

Why? Because client interfaces don't always know about the Pg-specific
types. They can use text-like types just fine if they can use text-typed
parameters when inserting/updating them. I feel that PostgreSQL is
overzealous to the point of being counterproductive by refusing to
implicitly cast these types, as they are subject to validation by the
input function in any case.

Can anybody show me a case where permitting implicit casts from text for
enums, xml or json types might introduce an error or cause SQL with a
mistake in it to execute instead of failing when it should?

This is driving me nuts when working with PgJDBC via various ORM layers
(I know, I know, but they're life at this point) that would work happily
with these types if they were implicitly castable to/from strings, but
don't understand how to explicitly specify these postgresql-specific
types when talking to the JDBC layer.

--
System & Network Administrator
POST Newspapers

On Mon, Dec 6, 2010 at 7:49 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Hi all
>
> I'm finding a few areas where PostgreSQL's refusal to implicitly cast
> from 'text' to another type is causing real problems, particularly when
> using the PgJDBC driver. I'd like to propose a couple of relaxations of
> the implicit cast rules for certain text-like types:
>
> - user-defined enums; and
> - xml
>
> ('json' should also be castable from text when introduced).
>
> Why? Because client interfaces don't always know about the Pg-specific
> types. They can use text-like types just fine if they can use text-typed
> parameters when inserting/updating them. I feel that PostgreSQL is
> overzealous to the point of being counterproductive by refusing to
> implicitly cast these types, as they are subject to validation by the
> input function in any case.
>
> Can anybody show me a case where permitting implicit casts from text for
> enums, xml or json types might introduce an error or cause SQL with a
> mistake in it to execute instead of failing when it should?
>
> This is driving me nuts when working with PgJDBC via various ORM layers
> (I know, I know, but they're life at this point) that would work happily
> with these types if they were implicitly castable to/from strings, but
> don't understand how to explicitly specify these postgresql-specific
> types when talking to the JDBC layer.

I totally must +1 for this idea, especially for ENUMs.  It was a
complete nightmare getting nHibernate to map a Postgres ENUM to a C#
Enum automatically.  I had to hack around all sorts of things. mostly
because the SQL syntax would assume the data was a string.

Craig Ringer <craig@postnewspapers.com.au> writes:
> I'm finding a few areas where PostgreSQL's refusal to implicitly cast
> from 'text' to another type is causing real problems, particularly when
> using the PgJDBC driver. I'd like to propose a couple of relaxations of
> the implicit cast rules for certain text-like types:

> - user-defined enums; and
> - xml

In my mind, enums are not even remotely text-like.  I'm not sure about
the xml case.

> Can anybody show me a case where permitting implicit casts from text for
> enums, xml or json types might introduce an error or cause SQL with a
> mistake in it to execute instead of failing when it should?

We got rid of the implicit casts in the other direction for extremely
good reasons.  Consult the archives from a few years back and note the
frequency of questions like "why is timestamp comparison behaving so
oddly", to which the answer was always "well, you constructed your query
in such a way that you are getting textual comparison, even though an
error would have been a lot saner".  I'm very unexcited about
introducing a similar risk in this direction.  It wouldn't manifest in
that particular way of course, but implicit cross-type-category casts
are a dangerous foot-gun.  In general the problem is that you have a
fifty-fifty chance of guessing which set of semantics the user thought
he would get for an operation involving two incompatible datatypes.
In the long run it's less pain all around if you throw an error and
make the user insert a cast to clarify.

This might be justifiable for xml, but only because it's got such a
paucity of interesting operators to begin with, thanks to the SQL
committee's weird ideas about preferable syntax.  I think it'd be an
exceedingly bad idea for enums.

This isn't the right list for discussing how to use the JDBC driver,
but I had the idea that it is possible to pass a parameter through
JDBC without specifying any particular type for it.  That would work
the way you want thanks to the rules for resolving unknown-type
literals/parameters.

            regards, tom lane

On tis, 2010-12-07 at 11:49 +0800, Craig Ringer wrote:
> This is driving me nuts when working with PgJDBC via various ORM
> layers (I know, I know, but they're life at this point) that would
> work happily with these types if they were implicitly castable to/from
> strings, but don't understand how to explicitly specify these
> postgresql-specific types when talking to the JDBC layer.

Since that is a quite specific use case, why don't you add the casts
yourself?