On 1/5/17, Tom Lane <t...@sss.pgh.pa.us> wrote: > Vitaly Burovoy <vitaly.buro...@gmail.com> writes: >> On 1/5/17, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> We could think about replacing interval2tm's output format with some >>> other struct that uses a TimeOffset for hours and so cannot overflow. >>> I'm not sure though how far the effects would propagate; it might be >>> more work than we want to put into this. > >> If values with overflow are already in a database, what do you expect >> a new output function should fix? > > My point is that ideally, any value that can physically fit into struct > Interval ought to be considered valid. The fact that interval_out can't > cope is a bug in interval_out, which ideally we would fix without > artificially restricting the range of the datatype. > > Now, the problem with that of course is that it's not only interval_out > but multiple other places. But your proposed patch also requires touching > nearly everything interval-related, so I'm not sure it has any advantage > that way over the less restrictive answer.
OK, I try to limit values from 9223372036854775807/3600000000/24/365=292471.2 years to 7730941132800000000/3600000000/24/365=245146.5 years i.e. cut 47325 years or ~16%, but it is only for interval's part measured in seconds. Am I correct that we are still limited by ECPG which is limited by the system "tm" struct? Also users who use a binary protocol can also use the "tm" struct and can not expect overflow. The docs say the highest value of the interval type is 178_000_000 years which is significantly bigger than both of values (current and limited) measured in seconds. I don't think applying that limitation is a big deal. I tried to find functions should be touched and there are not so many of them: -- internal functions: interval_check_overflow(Interval *i) tm2interval(struct pg_tm * tm, fsec_t fsec, Interval *span) interval2tm(Interval span, struct pg_tm * tm, fsec_t *fsec) intervaltypmodleastfield(int32 typmod) -- output functions: interval_out(PG_FUNCTION_ARGS) -- skip output interval_send(PG_FUNCTION_ARGS) -- skip output -- main input/computing functions: interval_in(PG_FUNCTION_ARGS) -- forgot to cover interval_recv(PG_FUNCTION_ARGS) -- covered make_interval(PG_FUNCTION_ARGS) -- covered AdjustIntervalForTypmod(Interval *interval, int32 typmod) -- can lead to overflow interval_justify_interval(PG_FUNCTION_ARGS) -- can lead to overflow interval_justify_hours(PG_FUNCTION_ARGS) -- can lead to overflow interval_justify_days(PG_FUNCTION_ARGS) -- can lead to overflow interval_um(PG_FUNCTION_ARGS) -- covered interval_pl(PG_FUNCTION_ARGS) -- covered interval_mi(PG_FUNCTION_ARGS) -- covered interval_mul(PG_FUNCTION_ARGS) -- covered -- can not lead to overflow interval_transform(PG_FUNCTION_ARGS) interval_div(PG_FUNCTION_ARGS) interval_trunc(PG_FUNCTION_ARGS) interval_part(PG_FUNCTION_ARGS) -- based on other functions interval_scale(PG_FUNCTION_ARGS) -- based on AdjustIntervalForTypmod interval_accum(PG_FUNCTION_ARGS) -- based on interval_pl interval_accum_inv(PG_FUNCTION_ARGS) -- based on interval_mi interval_avg(PG_FUNCTION_ARGS) -- based on interval_pl interval_combine(PG_FUNCTION_ARGS) -- based on interval_pl mul_d_interval(PG_FUNCTION_ARGS) -- based on interval_mul -- checking, not changing: interval_finite(PG_FUNCTION_ARGS) interval_smaller(PG_FUNCTION_ARGS) interval_larger(PG_FUNCTION_ARGS) interval_cmp_value(const Interval *interval) interval_cmp_internal(Interval *interval1, Interval *interval2) interval_eq(PG_FUNCTION_ARGS) interval_ne(PG_FUNCTION_ARGS) interval_lt(PG_FUNCTION_ARGS) interval_gt(PG_FUNCTION_ARGS) interval_le(PG_FUNCTION_ARGS) interval_ge(PG_FUNCTION_ARGS) interval_cmp(PG_FUNCTION_ARGS) interval_hash(PG_FUNCTION_ARGS) -- not applicable: intervaltypmodin(PG_FUNCTION_ARGS) intervaltypmodout(PG_FUNCTION_ARGS) timestamp_pl_interval(PG_FUNCTION_ARGS) timestamp_mi_interval(PG_FUNCTION_ARGS) timestamptz_pl_interval(PG_FUNCTION_ARGS) timestamptz_mi_interval(PG_FUNCTION_ARGS) ===== In fact, only six of them (not "touching nearly everything interval-related") should be covered: * interval_in * interval_out (yes, the SAMESIGN there is useless) * AdjustIntervalForTypmod * interval_justify_interval * interval_justify_hours * interval_justify_days https://www.postgresql.org/docs/current/static/datatype-datetime.html -- Best regards, Vitaly Burovoy -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers