From: Bryn Llewellyn <bryn@yugabyte.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-hackers list <pgsql-hackers@lists.postgresql.org>
Subject: Re: Have I found an interval arithmetic bug?
Date: Fri, 2 Apr 2021 17:50:59 -0700
Message-ID: <95D98B9E-CD54-4FAC-9574-D950E9741CAC@yugabyte.com> (raw)
In-Reply-To: <20210402234943.GB29126@momjian.us>
References: <BDAE4B56-3337-45A2-AC8A-30593849D6C0@yugabyte.com>
<20210402180549.GF9270@momjian.us>
<CALNJ-vRJUo62KNrQsiSsNAuPSBYpOXk8SPLh6r5jwHVzYai=HA@mail.gmail.com>
<20210402234943.GB29126@momjian.us>
bruce@momjian.us wrote:
> I have just posted a new version of the patch which I think covers all the right areas.
I found the relevant email from you to pgsql-hackers here:
https://www.postgresql.org/message-id/20210402234732.GA29125%40momjian.us
You said:
> I have modified the patch to prevent partial months from creating partial hours/minutes/seconds… Partial years keeps it in months, partial months takes it to days, and partial days take it to hours/minutes/seconds. This seems like an improvement.
I have written some PL/pgSQL code that faithfully emulates the behavior that I see in my present vanilla PostgreSQL Version 13.2 system in a wide range of tests. This is the key part:
m1 int not null := trunc(p.mo);
m_remainder numeric not null := p.mo - m1::numeric;
m int not null := trunc(p.yy*12) + m1;
d_real numeric not null := p.dd + m_remainder*30.0;
d int not null := floor(d_real);
d_remainder numeric not null := d_real - d::numeric;
s numeric not null := d_remainder*24.0*60.0*60.0 +
p.hh*60.0*60.0 +
p.mi*60.0 +
p.ss;
begin
return (m, d, s)::modeled_interval_t;
end;
These quantities:
p.yy, p.mo, p.dd, p.hh, p.mi, and p.ss
are the user’s parameterization. All are real numbers. Because non-integral values for years, months, days, hours, and minutes are allowed when you specify a value using the ::interval typecast, my reference doc must state the rules. I would have struggled to express these rules in prose—especially given the use both of trunc() and floor(). I would have struggled more to explain what requirements these rules meet.
I gather that by the time YugabyteDB has adopted your patch, my PL/pgSQL will no longer be a correct emulation. So I’ll re-write it then.
I intend to advise users always to constrain the values, when they specify an interval value explicitly, so the the years, months, days, hours, and minutes are integers. This is, after all, the discipline that the make_interval() built-in imposes. So I might recommend using only that.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bryn@yugabyte.com, bruce@momjian.us, pgsql-hackers@lists.postgresql.org
Subject: Re: Have I found an interval arithmetic bug?
In-Reply-To: <95D98B9E-CD54-4FAC-9574-D950E9741CAC@yugabyte.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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