

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] 
> Sent: 12 September 2002 00:53
> To: Dave Page
> Cc: Oliver Elphick; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS]
> 
> 
> OK, I am going to add these items to the open items list 
> because I am having trouble keeping track of all the 
> compatibility changes for pg_dump.
> 
> I have:
> 
> 	Loading 7.2 pg_dumps 
>         	opaque language handler no longer recognized  
> 
> What else is there?  
> 
> Do cast problems related to pg_dump loading or to working 
> with the data after the load?  Is it casts in user functions?

Oliver reported:

2.  The dump produced:
     CREATE TABLE cust_alloc_history (
         ...
        "year" integer DEFAULT date_part('year'::text,
                 ('now'::text)::timestamp(6) with time zone) NOT NULL,
         ...
    ERROR:  Column "year" is of type integer but default expression is
of type double precision
            You will need to rewrite or cast the expression

For an original definition of:

     year         INTEGER      DEFAULT
date_part('year',CURRENT_TIMESTAMP)

Regards, Dave.

Thu, 12 Sep 2002 08:23:36 +0100
"Dave Page" <dpage@vale-housing.co.uk>
Re: 
Dave Page wrote:
> Oliver reported:
> 
> 2.  The dump produced:
>      CREATE TABLE cust_alloc_history (
>          ...
>         "year" integer DEFAULT date_part('year'::text,
>                  ('now'::text)::timestamp(6) with time zone) NOT NULL,
>          ...
>     ERROR:  Column "year" is of type integer but default expression is
> of type double precision
>             You will need to rewrite or cast the expression
> 
> For an original definition of:
> 
>      year         INTEGER      DEFAULT
> date_part('year',CURRENT_TIMESTAMP)

Wow.  That is clear.  Why are we returning "year" as a double?  Yes, I
see now:

	test=> \df date_part
	                               List of functions
	 Result data type |   Schema   |   Name    |        Argument data types        
	------------------+------------+-----------+-----------------------------------
	 double precision | pg_catalog | date_part | text, abstime
	 double precision | pg_catalog | date_part | text, date
	 double precision | pg_catalog | date_part | text, interval
	 double precision | pg_catalog | date_part | text, reltime
	 double precision | pg_catalog | date_part | text, time with time zone
	 double precision | pg_catalog | date_part | text, time without time zone
	 double precision | pg_catalog | date_part | text, timestamp with time zone
	 double precision | pg_catalog | date_part | text, timestamp without time zone

I would love to say that this is related to change in casts, but that
isn't the case.  It is the new double-precision handling of dates;  and
I see no easy way to fix this, and you can't fix this after the data
load because the table wasn't created. Yuck.

I have to ask, why are we using a double here rather than a 64-bit
value, if available?

-- 
  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

Thu, 12 Sep 2002 12:15:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I would love to say that this is related to change in casts, but that
> isn't the case.  It is the new double-precision handling of dates;

You've got that exactly backwards: date_part has always returned double.

			regards, tom lane

Thu, 12 Sep 2002 15:07:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 
Bruce Momjian writes:

> Wow.  That is clear.  Why are we returning "year" as a double?

Because we've been doing that for many years.

> I would love to say that this is related to change in casts, but that
> isn't the case.

Sure it is.  The float=>int casts need to be made implicit, or we'll have
tons of problems like this.

-- 
Peter Eisentraut   peter_e@gmx.net


Fri, 13 Sep 2002 00:08:53 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: 
Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
>> I would love to say that this is related to change in casts, but that
>> isn't the case.

> Sure it is.  The float=>int casts need to be made implicit, or we'll have
> tons of problems like this.

Well, yeah.  That did not seem to bother anyone last spring, when we
were discussing tightening the implicit-casting rules.  Shall we
abandon all that work and go back to "any available cast can be applied
implicitly"?

My vote is "tough, time to fix your SQL code".

			regards, tom lane

Fri, 13 Sep 2002 00:46:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Bruce Momjian writes:
> >> I would love to say that this is related to change in casts, but that
> >> isn't the case.
> 
> > Sure it is.  The float=>int casts need to be made implicit, or 
> we'll have
> > tons of problems like this.
> 
> Well, yeah.  That did not seem to bother anyone last spring, when we
> were discussing tightening the implicit-casting rules.  Shall we
> abandon all that work and go back to "any available cast can be applied
> implicitly"?
> 
> My vote is "tough, time to fix your SQL code".

Wasn't the resolution back then to "wait until beta and see who complains"?

Chris


Fri, 13 Sep 2002 12:56:36 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: 
En Fri, 13 Sep 2002 00:46:00 -0400
Tom Lane <tgl@sss.pgh.pa.us> escribiï¿½:

> Peter Eisentraut <peter_e@gmx.net> writes:
> 
> > Sure it is.  The float=>int casts need to be made implicit, or we'll have
> > tons of problems like this.
> 
> Well, yeah.  That did not seem to bother anyone last spring, when we
> were discussing tightening the implicit-casting rules.  Shall we
> abandon all that work and go back to "any available cast can be applied
> implicitly"?

Implicit float to int loses precision, so it shouldn't be implicit,
should it?

Maybe the solution is to make 7.3 pg_dump smart enough to add explicit
casts where default values demand them...  Is this possible?  Are there
other cases where tightening implicit casts is going to bit users?

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso. (Ernesto Hernï¿½ndez-Novich)

Fri, 13 Sep 2002 00:59:59 -0400
Alvaro Herrera <alvherre@atentus.com>
Re: 
>
> My vote is "tough, time to fix your SQL code".
>

Sounds good to me, but please document it in the "migration" notes. No need 
for a surprise.

Regards,
	Jeff

Thu, 12 Sep 2002 23:58:00 -0700
Jeff Davis <list-pgsql-hackers@empires.org>
Re: 
Tom Lane writes:

> Shall we abandon all that work and go back to "any available cast can be
> applied implicitly"?
>
> My vote is "tough, time to fix your SQL code".

That would be a OK if the current behavior conformed to the SQL standard,
which it doesn't.  The standard says that all numerical types are mutually
assignable, which in my mind translates directly as implicitly castable.
Additionally, your stance breaks the following SQL compatible and probably
quite common code:

create table test ( a int extract(year from current_date) );

We aren't abandoning "all that work".  Plenty of casts should not be
implicit because they are structurally guaranteed to lose information. But
for casts between numerical types it depends on the content at run time.
Therefore the SQL standard says that the check needs to be at run time.
We do that already, so I don't see a reason to be more strict here.

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 14 Sep 2002 00:37:16 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: 
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Shall we abandon all that work and go back to "any available cast can be
>> applied implicitly"?
>> 
>> My vote is "tough, time to fix your SQL code".

> That would be a OK if the current behavior conformed to the SQL standard,
> which it doesn't.  The standard says that all numerical types are mutually
> assignable, which in my mind translates directly as implicitly castable.

If we take that stance then we will never make any progress at all on
fixing our problems with poor choices of numeric operators and inability
to choose an appropriate operator.  We can *not* adopt the attitude that
all numeric casts are equal; some have got to be more equal than others,
or the parser will be unable to choose desirable interpretations over
undesirable ones.

As an example, current code does the right thing with
	select * from foo where numeric_col = 10.1
whereas 7.2 failed with
	ERROR:  Unable to identify an operator '=' for types 'numeric' and 'double precision'
This improvement comes precisely because the numeric->float8 cast
pathway is not treated on an even footing with the other direction.

> Additionally, your stance breaks the following SQL compatible and probably
> quite common code:

> create table test ( a int extract(year from current_date) );

I previously suggested that it might be okay to allow non-implicit casts
to be used when assigning a value to a target column in INSERT and
UPDATE (including the case where the value is a default value).  If we
do that, then the above will work, and we haven't abandoned all hope of
choosing sensible cast pathways within expressions.

Alternatively we could think about a three-level scheme where pg_cast
can declare different "strengths" of implicit castability for a cast
pathway; then it'd be possible to allow or disallow implicit coercion
to a target column type on a cast-by-cast basis.  Dunno if we need that
much complexity here...

			regards, tom lane

Sat, 14 Sep 2002 14:47:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Casting rules (was: an untitled thread)
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I would love to say that this is related to change in casts, but that
> > isn't the case.  It is the new double-precision handling of dates;
> 
> You've got that exactly backwards: date_part has always returned double.

Well, at least I was _exact_ about something.  :-)

(I am back from the retreat. I actually was back Saturday afternoon but
my connection to my ISP was down until today.)

-- 
  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

Sun, 15 Sep 2002 21:03:58 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 

Can someone remind me why date_part() returns a double rather than an
int4?  It is just for partial seconds?

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Shall we abandon all that work and go back to "any available cast can be
> > applied implicitly"?
> >
> > My vote is "tough, time to fix your SQL code".
> 
> That would be a OK if the current behavior conformed to the SQL standard,
> which it doesn't.  The standard says that all numerical types are mutually
> assignable, which in my mind translates directly as implicitly castable.
> Additionally, your stance breaks the following SQL compatible and probably
> quite common code:
> 
> create table test ( a int extract(year from current_date) );
> 
> We aren't abandoning "all that work".  Plenty of casts should not be
> implicit because they are structurally guaranteed to lose information. But
> for casts between numerical types it depends on the content at run time.
> Therefore the SQL standard says that the check needs to be at run time.
> We do that already, so I don't see a reason to be more strict here.
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  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

Sun, 15 Sep 2002 21:22:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 