pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Timestamp operator error
10+ messages / 2 participants
[nested] [flat]

* Timestamp operator error
@ 2002-02-26 04:10 Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Josh Berkus @ 2002-02-26 04:10 UTC (permalink / raw)
  To: pgsql-sql

Folks,

Potentially a real problem bug in 7.2:

staffos=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)

staffos=# select ('6 days'::INTERVAL + current_timestamp);
      ?column?
---------------------
 2002-02-25 00:00:00
(1 row)

staffos=# select (current_timestamp + '6 days'::INTERVAL);
           ?column?
-------------------------------
 2002-03-03 20:07:52.105254-08
(1 row)

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
@ 2002-02-26 04:28 ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 04:34   ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2002-02-26 04:28 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> Potentially a real problem bug in 7.2:

> staffos=# select ('6 days'::INTERVAL + current_timestamp);
>       ?column?
> ---------------------
>  2002-02-25 00:00:00
> (1 row)

Well, 7.1's not materially better:

test71=# select ('6 days'::INTERVAL + current_timestamp);
 ?column?
----------
 23:15:27
(1 row)

AFAICT, what's happening in 7.2 is that the closest-match operator
is "time + date", so it casts the interval to time (yielding 0 hours),
and casts current_timestamp to date (yielding midnight today)
and adds.

7.1 evidently found some different, but equally unintuitive conversion
path.  Probably the difference in behavior is not the result of any
algorithm change, but the addition or subtraction of a type-conversion
operator.

I suspect this is good ammunition for the argument I've made from time
to time that we have too many implicit conversions, not too few.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-26 04:34   ` Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:47     ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Josh Berkus @ 2002-02-26 04:34 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,

> AFAICT, what's happening in 7.2 is that the closest-match operator
> is "time + date", so it casts the interval to time (yielding 0
>  hours),
> and casts current_timestamp to date (yielding midnight today)
> and adds.

Any suggestions on an emergency fix for my (production) database?
  We've already seen a couple of major problems from this bug, and I'm
 worried that there will be others that I don't catch until it's too
 late.  If I had the know-how, I'd fix the operator myself, but I
 don't.

> I suspect this is good ammunition for the argument I've made from
>  time
> to time that we have too many implicit conversions, not too few.

Yes, definitely.  Frankly, I'd prefer a large reduction in implicit
 conversions; I just got into trouble with the difference between
 current_timestamp and current_date that I would have caught much
 earlier if Postgres had disallowed the implicit conversion. 

-Josh 


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 04:34   ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
@ 2002-02-26 04:47     ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:03       ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2002-02-26 04:47 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> Any suggestions on an emergency fix for my (production) database?

Emergency fix?  This operator didn't behave reasonably in 7.1 either
(at least not by my definition of reasonable).  What exactly would
you have us do?

>> I suspect this is good ammunition for the argument I've made from
>> time to time that we have too many implicit conversions, not too few.

> Yes, definitely.  Frankly, I'd prefer a large reduction in implicit
>  conversions; I just got into trouble with the difference between
>  current_timestamp and current_date that I would have caught much
>  earlier if Postgres had disallowed the implicit conversion. 

Yah.  Offhand I'd argue that no information-discarding conversion
should be implicitly invokable.  date->timestamp is fine;
timestamp->date should require an explicit cast.  I've already proposed
that we add a flag to pg_proc to distinguish implicit from explicit
conversion operations, and no one complained.  But we have not yet
begun to argue about exactly which conversions should be allowed
implicitly...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 04:34   ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:47     ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-26 05:03       ` Josh Berkus <josh@agliodbs.com>
  2002-02-26 05:10         ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Josh Berkus @ 2002-02-26 05:03 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,

> Emergency fix?  This operator didn't behave reasonably in 7.1 either
> (at least not by my definition of reasonable).  What exactly would
> you have us do?

No, not you!  For me to fix.  You're a volunteer, as far as I'm
 involved.  I just wanted suggestions for a quick fix.  When I found
 the other issues, it was more reasonable to search-and-replace on
 values ("interval"() for interval() was easy).  I can't figure out how
 to pattern match on interval + timestamp, especially with variables
 involved.

Is there a way, for example, that I could disallow the TIMESTAMP -->
 DATE implicit conversion in my code?  That would break all the
 functions and views with this problem, and then I could identify them.

> Yah.  Offhand I'd argue that no information-discarding conversion
> should be implicitly invokable.  date->timestamp is fine;
> timestamp->date should require an explicit cast.  I've already
>  proposed
> that we add a flag to pg_proc to distinguish implicit from explicit
> conversion operations, and no one complained.  But we have not yet
> begun to argue about exactly which conversions should be allowed
> implicitly...

Hey, feel free to take up the argument here, too!  It is a SQL topic,
 after all ...

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 04:34   ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:47     ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:03       ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
@ 2002-02-26 05:10         ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:20           ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2002-02-26 05:10 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> Is there a way, for example, that I could disallow the TIMESTAMP -->
>  DATE implicit conversion in my code?

Well, you could delete the date(timestamp) function from pg_proc.
However, that would prevent getting from timestamp to date even
with an explicit cast --- so you might want to put it back after
you've found all the trouble spots.

My approach would be to rename rather than delete the pg_proc entry (say
update "date" to "datexxx" in proname); then you could reverse the
rename after finding your problems.  On the other hand, if you're doing
the testing in a deletable temporary database, this isn't a problem.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 04:34   ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:47     ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:03       ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 05:10         ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-26 05:20           ` Josh Berkus <josh@agliodbs.com>
  2002-02-26 05:31             ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Josh Berkus @ 2002-02-26 05:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,
 
> Well, you could delete the date(timestamp) function from pg_proc.
> However, that would prevent getting from timestamp to date even
> with an explicit cast --- so you might want to put it back after
> you've found all the trouble spots.

Hmmmm, no, that won't work, as lots of my functions have explicit
 casts.  Thanks anyway.  Hopefully I don't have many more functions
 where I call INTERVAL + TIMESTAMP; I just looked at several
 time-sensitve functions and I'm pretty consistent with the other way
 'round.

FMI, though, how difficult is defining an operator if I paid a C
 programmer to do it?  

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 04:34   ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:47     ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:03       ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 05:10         ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:20           ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
@ 2002-02-26 05:31             ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:42               ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 06:13               ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 10+ messages in thread

From: Tom Lane @ 2002-02-26 05:31 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> FMI, though, how difficult is defining an operator if I paid a C
>  programmer to do it?  

Pretty trivial when it's only the commutation of an existing operator...

Actually you don't even need any C code.  You could limp along at some
sacrifice of performance with an SQL function definition.  Say

regression=# create function interval_plus_timestamp(interval,timestamp)
regression-# returns timestamp as '
regression'# select $2 + $1;
regression'# ' language 'sql';
CREATE

regression=# create operator + (procedure = interval_plus_timestamp,
regression(# leftarg = interval,
regression(# rightarg = timestamp);
CREATE

et voila:

regression=# select ('6 days'::INTERVAL + current_timestamp);
           ?column?
-------------------------------
 2002-03-04 00:28:30.230026-05
(1 row)

I'd not want to make an index depend on this operator, but for
occasional query use it should do fine ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 04:34   ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:47     ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:03       ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 05:10         ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:20           ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 05:31             ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-26 05:42               ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Josh Berkus @ 2002-02-26 05:42 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,

> Pretty trivial when it's only the commutation of an existing
>  operator...

Well, I was thinking of funding the * and / operators for intervals as
 well.  But I'd need to have an idea of the difficutly before I solicit
 a programmer, since they'd be able to tell me anything.

> Actually you don't even need any C code.  You could limp along at
>  some
> sacrifice of performance with an SQL function definition.  Say

Tante Grazie!  This is exactly what I meant by "quick fix".  It will
 work nicely for me.

-Josh 

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Timestamp operator error
  2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:28 ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 04:34   ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 04:47     ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:03       ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 05:10         ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-26 05:20           ` Re: Timestamp operator error Josh Berkus <josh@agliodbs.com>
  2002-02-26 05:31             ` Re: Timestamp operator error Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-26 06:13               ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Josh Berkus @ 2002-02-26 06:13 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,

> Pretty trivial when it's only the commutation of an existing
>  operator...

Which then makes me think of a proposal for Postgres 7.3 or 7.4:

If you enabled delaration of operators as "commutative", you could cut
 the required number of operator definitions by a third.

Just a thought.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2002-02-26 06:13 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-26 04:10 Timestamp operator error Josh Berkus <josh@agliodbs.com>
2002-02-26 04:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 04:34   ` Josh Berkus <josh@agliodbs.com>
2002-02-26 04:47     ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 05:03       ` Josh Berkus <josh@agliodbs.com>
2002-02-26 05:10         ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 05:20           ` Josh Berkus <josh@agliodbs.com>
2002-02-26 05:31             ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 05:42               ` Josh Berkus <josh@agliodbs.com>
2002-02-26 06:13               ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox