Proposal for resolving casting issues

We've been discussing this stuff in fits and starts for months now, but
nothing satisfactory has been arrived at. I've concluded that part of
the problem is that we are trying to force the system's behavior into
a model that is too limiting: we need more than an implicit/explicit cast
distinction. Accordingly, I suggest we bite the bullet and make it happen.
(Note that I've resigned myself to having to do an initdb for 7.3beta2.)

I think we must extend pg_cast's castimplicit column to a three-way value:
* okay as implicit cast in expression (or in assignment)
* okay as implicit cast in assignment only
* okay only as explicit cast

"In expression" refers to cases where we have (or potentially have) multiple
possible interpretations; essentially, anytime a value is being fed to a
function or operator, there can be ambiguity due to overloading, and so we
need to restrict the set of possible implicit casts to limit ambiguity and
ensure a reasonable choice of function is made.

"In assignment only" actually means any case where the destination datatype
is known with certainty. For example CoerceTargetExpr is currently used to
coerce an array subscript expression to integer, and I think it's okay to
treat that context like store assignment.

Question: what shall we call these alternatives in CREATE CAST? The SQL99
phrase AS ASSIGNMENT looks like it should mean the second, but I think
the spec semantics require it to mean the first. Ugh. Perhaps AS
ASSIGNMENT ONLY for the second case?

Also, I think we should allow cast functions to take an optional boolean
second argument "isExplicit", so that explicit casts can be distinguished
from implicit at runtime. We'll use this to get spec-compliant semantics
for char/varchar truncation (there shouldn't be an error if you explicitly
cast to a shorter length).

We'll need to add fields to Func and RelabelType nodes so that we can tell
whether a node was generated due to an explicit function call, implicit
cast, or explicit cast; we'll use these for better reverse-listing. (In
particular this will let us hide the boolean second argument from being
reverse-listed, when present.)

Now, as to just what to do with it --- Peter posted a list of questions
awhile back that weren't ever resolved, but I think we can make some
progress with this scheme in mind:

> From looking at the set of implicit or not casts, I think there are two
> major issues to discuss:
>
> 1. Should truncating/rounding casts be implicit? (e.g., float4 -> int4)
>
> I think there's a good argument for "no", but for some reason SQL99 says
> "yes", at least for the family of numerical types.

We can make this work cleanly if "down" casts are assignment-only while
"up" casts are fully implicit. I think that the spec requires implicit
casting only in the context of store assignment.

> 2. Should casts from non-character types to text be implicit? (e.g., date
> -> text)
>
> I think this should be "no", for the same reason that the other direction
> is already disallowed. It's just sloppy programming.

I agree with this in principle, but in practice we probably have to allow
implicit casts to text, at least for awhile yet. Seems that too many
people depend on stuff like
SELECT 'Meeting time is ' || timestamp_var
Since this is an expression context we don't get any help from the notion
of store assignment :-(

> I also have a few individual cases that look worthy of consideration:
>
> abstime <-> int4: I think these should not be implicit because they
> represent different "kinds" of data. (These are binary compatible casts,
> so changing them to not implicit probably won't have any effect. I'd have
> to check this.)

I believe that as of current sources we can mark a binary cast non-implicit,
and I agree with marking these two explicit-only.

> date -> timestamp[tz]: I'm suspicious of this one, but it's hard to
> explain. The definition to fill in the time component with zeros is
> reasonable, but it's not the same thing as casting integers to floats
> because dates really represent a time span of 24 hours and timestamps an
> indivisible point in time. I suggest making this non-implicit, for
> conformance with SQL and for general consistency between the date/time
> types.

I disagree here; promoting date to timestamp seems perfectly reasonable,
and I think it's something a lot of people rely on.

> time -> interval: I'm not even sure this cast should exist at all.
> Proper arithmetic would be IntervalValue = TimeValue - TIME 'midnight'.
> At least make it non-implicit.

I'd go along with marking it assignment-only.

> timestamp -> abstime: This can be implicit AFAICS.

This is lossy (abstime doesn't preserve fractional seconds) so I'd vote
for making it assignment-only.

In a later message Peter wrote:

> Since almost every cast to "text" is implicit, then I believe so should
> inet -> text
> macaddr -> text
> int4 -> varchar
> int8 -> varchar
> which are currently not.

I'd go along with making the inet->text and macaddr->text cases implicit,
since as you note all the other casts to text are. However, those two
casts to varchar must not be implicit (or at most assignment-only) else
they will create ambiguity against the implicit casts to text for the same
source datatype.

In summary: I haven't yet gone through the existing casts in detail, but
I propose the following general rules for deciding how to mark casts:

* Casts across datatype categories should be explicit-only, with the
exception of casts to text, which we will allow implicitly for backward
compatibility's sake.

* Within a category, "up" (lossless) conversions are implicit, "down"
(potentially lossy) conversions should be assignment-only.

Comments?

regards, tom lane

> > abstime <-> int4: I think these should not be implicit because they
> > represent different "kinds" of data. (These are binary
> compatible casts,
> > so changing them to not implicit probably won't have any
> effect. I'd have
> > to check this.)
>
> I believe that as of current sources we can mark a binary cast
> non-implicit,
> and I agree with marking these two explicit-only.

Everything in this proposal looks pretty good. With regards to the above
abstime<->int4 thing - what about the 'magic' values in that conversion.
(eg. -infinity, etc.)

Chris

"Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> writes:
> Everything in this proposal looks pretty good. With regards to the above
> abstime<->int4 thing - what about the 'magic' values in that conversion.
> (eg. -infinity, etc.)

They map to some magic int4 values, same as it ever was. I'm not
interested in trying to improve the semantics of any specific conversion
at the moment...

regards, tom lane

I said:
> Also, I think we should allow cast functions to take an optional boolean
> second argument "isExplicit", so that explicit casts can be distinguished
> from implicit at runtime. We'll use this to get spec-compliant semantics
> for char/varchar truncation (there shouldn't be an error if you explicitly
> cast to a shorter length).

After looking closely at SQL92 sections 6.10 (cast specification) and
9.2 (store assignment), it seems that the only places where the spec
demands different behavior for an explicit cast than for an implicit
assignment cast are for length coercions of char, varchar, bit, and
varbit types.

Accordingly, the places where we actually *need* the extra isExplicit
argument are not in the type-coercion functions per se, but in the
length-coercion functions associated with these four datatypes.

While we could still add the extra argument for the type-coercion
functions, I'm inclined not to do so; there is no need for it for spec
compliance of the standard types, and I don't think we should encourage
user-defined types to behave differently for explicit and implicit
casts.

What I will do instead is adjust parse_coerce.c so that a
length-coercion function can have either of the signatures
foo(foo,int4) returns foo
or
foo(foo,int4,bool) returns foo
and then modify the above-mentioned length coercion functions to provide
the desired behavior. This has no direct impact on pg_cast because we
do not use pg_cast for length-coercion functions.

regards, tom lane

Tom Lane writes:

> I think we must extend pg_cast's castimplicit column to a three-way value:
> * okay as implicit cast in expression (or in assignment)
> * okay as implicit cast in assignment only
> * okay only as explicit cast

Viewed in isolation this looks entirely reasonable, but I think we would
be adding a lot of infrastructure for the benefit of a relatively small
number of cases.

As the writer of a cast, this presents me with at least one more option
than I can really manage.

As the user of a cast, these options make the whole system nearly
unpredictable because in any non-trivial expression each of these
behaviors could take effect somehow (possibly even depending on how the
inner expressions turned out).

I am not aware of any programming language that has more than three
castability levels (never/explicit/implicit).

Finally, I believe this paints over the real problems, namely the
inadequate and hardcoded type category preferences and the inadequate
handling of numerical constants. Both of these issues have had adequate
approaches proposed in the past and would solve this an a number of other
issues.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> I think we must extend pg_cast's castimplicit column to a three-way value:
>> * okay as implicit cast in expression (or in assignment)
>> * okay as implicit cast in assignment only
>> * okay only as explicit cast

> As the user of a cast, these options make the whole system nearly
> unpredictable because in any non-trivial expression each of these
> behaviors could take effect somehow (possibly even depending on how the
> inner expressions turned out).

How so? Only the first set of casts applies inside an expression.

It seems to me that this proposal actually *reduces* the number of casts
that might apply in any given context, and thus makes the behavior more
predictable not less so. Certainly it is more predictable than
any-cast-can-be-applied-implicitly, which I seem to remember you arguing
for (at least for the numeric types).

> I am not aware of any programming language that has more than three
> castability levels (never/explicit/implicit).

Actually I think that this scheme would allow us to model typical
programming-language behavior quite accurately. C for example will let
you assign a float to an integer (with appropriate runtime behavior) ---
but if you add a float and an integer, you get a float addition; there's
no possibility that the system will choose to coerce the float to int
and do an int addition. So the set of available implicit casts is
different in an assignment context than it is in an expression context.
Seems pretty close to what I'm suggesting.

> Finally, I believe this paints over the real problems, namely the
> inadequate and hardcoded type category preferences and the inadequate
> handling of numerical constants. Both of these issues have had adequate
> approaches proposed in the past and would solve this an a number of other
> issues.

If they were adequate they would have gotten implemented; we had issues
with all the proposals so far. See my later response to Andreas for a
possible solution to the numerical-constant issue based on this
mechanism.

regards, tom lane

Tom Lane wrote:
> We've been discussing this stuff in fits and starts for months now, but
> nothing satisfactory has been arrived at. I've concluded that part of
> the problem is that we are trying to force the system's behavior into
> a model that is too limiting: we need more than an implicit/explicit cast
> distinction. Accordingly, I suggest we bite the bullet and make it happen.
> (Note that I've resigned myself to having to do an initdb for 7.3beta2.)

I was reading my backlog of email and thinking, "Oh, things are shaping
up well", then I hit this message. Let me try to collect open items
tomorrow and get a plan together. I have caught up on my email. I am
heading to bed.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I wrote:
> I think we must extend pg_cast's castimplicit column to a three-way value:
> * okay as implicit cast in expression (or in assignment)
> * okay as implicit cast in assignment only
> * okay only as explicit cast

> Question: what shall we call these alternatives in CREATE CAST? The SQL99
> phrase AS ASSIGNMENT looks like it should mean the second, but I think
> the spec semantics require it to mean the first. Ugh. Perhaps AS
> ASSIGNMENT ONLY for the second case?

On looking more closely, SQL99 appears to define user-defined casts as
invocable *only* in explicit cast and assignment contexts. Part 2 sez:

4.13 Data conversions

Explicit data conversions can be specified by a CAST operator.
A CAST operator defines how values of a source data type are
converted into a value of a target data type according to
the Syntax Rules and General Rules of Subclause 6.22, "<cast
specification>". Data conversions between predefined data types
and between constructed types are defined by the rules of this part
of ISO/IEC 9075. Data conversions between one or more user-defined
types are defined by a user-defined cast.

A user-defined cast identifies an SQL-invoked function, called the
cast function, that has one SQL parameter whose declared type is
the same as the source data type and a result data type that is the
target data type. A cast function may optionally be specified to
be implicitly invoked whenever values are assigned to targets of
its result data type. Such a cast function is called an implicitly
invocable cast function.

This seems to mean that we can get away with defining AS ASSIGNMENT to
mean my second category (implicit in assignment only), and then picking
some more natural term for my first category (implicit anywhere).

I favor using IMPLICIT, which would make the syntax of CREATE CAST be

CREATE CAST (sourcetype AS targettype)
WITH FUNCTION funcname (argtype)
[ AS ASSIGNMENT | IMPLICIT ]

CREATE CAST (sourcetype AS targettype)
WITHOUT FUNCTION
[ AS ASSIGNMENT | IMPLICIT ]

Or possibly it should be AS IMPLICIT?

Comments?

regards, tom lane

Tom Lane wrote:
> I favor using IMPLICIT, which would make the syntax of CREATE CAST be
>
> CREATE CAST (sourcetype AS targettype)
> WITH FUNCTION funcname (argtype)
> [ AS ASSIGNMENT | IMPLICIT ]
>
> CREATE CAST (sourcetype AS targettype)
> WITHOUT FUNCTION
> [ AS ASSIGNMENT | IMPLICIT ]
>
> Or possibly it should be AS IMPLICIT?

I think AS IMPLICIT would be better because we have other AS [var]
clauses.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I wrote:
> [Peter wrote:]
>> time -> interval: I'm not even sure this cast should exist at all.
>> Proper arithmetic would be IntervalValue = TimeValue - TIME 'midnight'.
>> At least make it non-implicit.

> I'd go along with marking it assignment-only.

I started to make this change, but have momentarily backed off after
observing that it causes a failure in the regression tests:

*** ./expected/horology-no-DST-before-1970.out Wed Sep 18 13:56:41 2002
--- ./results/horology.out Wed Sep 18 15:45:54 2002
***************
*** 277,287 ****

-- subtract time from date should not make sense; use interval instead
SELECT date '1991-02-03' - time '04:05:06' AS "Subtract Time";
! Subtract Time
! --------------------------
! Sat Feb 02 19:54:54 1991
! (1 row)
!
SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time UTC";
ERROR: Unable to identify an operator '-' for types 'date' and 'time with time zone'
You will have to retype this query using an explicit cast
--- 277,284 ----

-- subtract time from date should not make sense; use interval instead
SELECT date '1991-02-03' - time '04:05:06' AS "Subtract Time";
! ERROR: Unable to identify an operator '-' for types 'date' and 'time without time zone'
! You will have to retype this query using an explicit cast
SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time UTC";
ERROR: Unable to identify an operator '-' for types 'date' and 'time with time zone'
You will have to retype this query using an explicit cast

The regression test is evidently relying on the implicit cast from time
to interval to allow the date - interval operator to be used for this
query.

Now, given that the regression test itself observes that 'date - time'
is wrong, and should be 'date - interval', maybe this behavioral change
is a Good Thing. Or maybe it will just break applications. Comments?

I'm going to commit my pg_cast changes without this change later today,
but we can still go back and add this change if we decide it's good.

regards, tom lane

Bruce Momjian writes:

> > Or possibly it should be AS IMPLICIT?
>
> I think AS IMPLICIT would be better because we have other AS [var]
> clauses.

But IMPLICIT is not a variable.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Tom Lane writes:

> On looking more closely, SQL99 appears to define user-defined casts as
> invocable *only* in explicit cast and assignment contexts.

> This seems to mean that we can get away with defining AS ASSIGNMENT to
> mean my second category (implicit in assignment only), and then picking
> some more natural term for my first category (implicit anywhere).

Sounds good.

Have you seen 9.4 "Subject routine determination" and 9.5 "Type
precedence list determination"? In essence, the SQL standard has a
hard-coded precedence list much like we have. Since we support the
creation of non-structured user-defined types, the additional castability
level effectively gives us a way to override the built-in precedence
lists. In fact, now that we have given up in the numeric/float8
precedence, the other hard-coded categories should be easy to eliminate.

> CREATE CAST (sourcetype AS targettype)
> WITH FUNCTION funcname (argtype)
> [ AS ASSIGNMENT | IMPLICIT ]

Fine with me.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > > Or possibly it should be AS IMPLICIT?
> >
> > I think AS IMPLICIT would be better because we have other AS [var]
> > clauses.
>
> But IMPLICIT is not a variable.

I meant we have cases where we do AS [ keyword1 | keyword2 ].

CREATE OPERATOR CLASS any_name opt_default FOR TYPE_P Typename
USING access_method AS opclass_item_list

What I am saying is that is better to do AS [ keyword | keyword ] rather
than [ AS keyword | keyword ].

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> What I am saying is that is better to do AS [ keyword | keyword ] rather
> than [ AS keyword | keyword ].

Yeah, I thought the same after looking at it a little. Committed that
way (of course it's still open to adjustment...)

regards, tom lane

Hi PostgreSQL Folks,

I would like to inquire how is the BLOB support in PostgreSQL is doing
now? Had there been some improvements? Can I have the blob support like in
this manner?

create table myblob (
blobid serial not null primary key,
name varchar(50),
image blob));

for some doc,xls, and ppt files, can i do this operations?

Insert into myblob (name,image) values (' personal data','personal.doc');
Insert into myblob (name,image) values (' business data','business.xls');
Insert into myblob (name,image) values (' presentation data','present.ppt');

I would appreciate it very much for whatever comments you can give me
on this.

Thank you and MORE POWER TO THE BEST OPENSOURCE DBMS!

Mr. Manny Cabido
Philippines

On Wed, 2002-09-18 at 18:32, Manuel Cabido wrote:
> Hi PostgreSQL Folks,
>
> I would like to inquire how is the BLOB support in PostgreSQL is doing
> now? Had there been some improvements? Can I have the blob support like in

I'm unsure about blob (didn't know we had a blob type), but bytea works
perfectly fine for that.

--
Rod Taylor

> I would like to inquire how is the BLOB support in
> PostgreSQL is doing
> > now? Had there been some improvements? Can I have the blob
> support like in
>
> I'm unsure about blob (didn't know we had a blob type), but bytea works
> perfectly fine for that.

Is there some reason why we didn't call text 'clob' and bytea 'blob'? or at
least add aliases?

Chris

Christopher Kings-Lynne writes:

> Is there some reason why we didn't call text 'clob' and bytea 'blob'?

At the time our types were created there was no standard defining the
other types.

> or at least add aliases?

Mapping clob to text might be OK, but blob and bytea have totally
different input formats.

--
Peter Eisentraut peter_e(at)gmx(dot)net