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

Sun, 15 Sep 2002 13:09:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Proposal for resolving casting issues
> > 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


Mon, 16 Sep 2002 10:10:05 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Proposal for resolving casting issues
"Christopher Kings-Lynne" <chriskl@familyhealth.com.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

Sun, 15 Sep 2002 23:48:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
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

Mon, 16 Sep 2002 12:44:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
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@gmx.net


Mon, 16 Sep 2002 19:26:08 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Proposal for resolving casting issues
Peter Eisentraut <peter_e@gmx.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

Mon, 16 Sep 2002 13:42:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 17 Sep 2002 02:05:39 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal for resolving casting issues
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

Tue, 17 Sep 2002 11:36:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 17 Sep 2002 14:40:24 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal for resolving casting issues
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

Wed, 18 Sep 2002 16:00:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
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@gmx.net


Wed, 18 Sep 2002 22:09:20 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Proposal for resolving casting issues
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@gmx.net


Wed, 18 Sep 2002 22:10:21 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Proposal for resolving casting issues 
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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 18 Sep 2002 17:13:26 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal for resolving casting issues
Bruce Momjian <pgman@candle.pha.pa.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

Wed, 18 Sep 2002 17:37:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
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



Thu, 19 Sep 2002 06:32:47 +0800 (PHT)
Manuel Cabido <manny@tinago.msuiit.edu.ph>
Re: BLOB
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


18 Sep 2002 20:12:27 -0400
Rod Taylor <rbt@rbt.ca>
Re: BLOB
>     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


Thu, 19 Sep 2002 10:42:14 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BLOB
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@gmx.net


Thu, 19 Sep 2002 23:38:07 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: BLOB