Thread: Timestamp operator error
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
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
"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
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 thisbug, and I'mworried that there will be others that I don't catch until it's toolate. If I had the know-how, I'd fixthe operator myself, but Idon'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 implicitconversions; I just got into trouble with the differencebetweencurrent_timestamp and current_date that I would have caught muchearlier if Postgres had disallowed the implicitconversion. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"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
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'minvolved. I just wanted suggestions for a quick fix.
WhenI foundthe other issues, it was more reasonable to search-and-replace onvalues ("interval"() for interval() was
easy). I can't figure out howto pattern match on interval + timestamp, especially with variablesinvolved.
Is there a way, for example, that I could disallow the TIMESTAMP -->DATE implicit conversion in my code? That would
breakall thefunctions 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
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
"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
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 explicitcasts. Thanks anyway. Hopefully I don't have many morefunctionswhere I call INTERVAL + TIMESTAMP; I just looked at severaltime-sensitve functions and I'm pretty consistentwith the other way'round. FMI, though, how difficult is defining an operator if I paid a Cprogrammer to do it? -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"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
Tom, > Pretty trivial when it's only the commutation of an existing > operator... Well, I was thinking of funding the * and / operators for intervals aswell. But I'd need to have an idea of the difficutlybefore I solicita 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 willwork nicely for me. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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 cutthe required number of operator definitions by a third. Just a thought. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco