pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
integer square root function proposed
5+ messages / 3 participants
[nested] [flat]

* integer square root function proposed
@ 2022-12-18 02:39  Martin L. Buchanan <martinlbuchanan@gmail.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Martin L. Buchanan @ 2022-12-18 02:39 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Dear PostgreSQL colleagues:

I have just joined this, my first PG mailing list.

Reading the documentation I found no built-in function for integer square
root, requiring a sequence of:

floor(sqrt(foo))::integer

to go from an integer to the integer square root as an integer.

If PG leaders smile on this suggestion, could we have *isqrt*(foo) where
foo has a numeric type and the result type is the same numeric type, with
the result type being the integer square root for foo >= 0 and producing an
error if negative? (And with infinities and NaNs processed as described in
8.1 Numeric Types).

I did not see an explicit enhancement request list in the PG mailing lists.
Recommendations for a more specific list to post this to are welcome.


Sincerely,

Martin L. Buchanan
software developer
Laramie, WY, USA

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

* Re: integer square root function proposed
@ 2022-12-18 03:20  Rob Sargent <robjsargent@gmail.com>
  parent: Martin L. Buchanan <martinlbuchanan@gmail.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Rob Sargent @ 2022-12-18 03:20 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 12/17/22 19:39, Martin L. Buchanan wrote:
> Dear PostgreSQL colleagues:
>
> I have just joined this, my first PG mailing list.
>
> Reading the documentation I found no built-in function for integer 
> square root, requiring a sequence of:
>
> floor(sqrt(foo))::integer
>
> to go from an integer to the integer square root as an integer.
>
> If PG leaders smile on this suggestion, could we have *isqrt*(foo) 
> where foo has a numeric type and the result type is the same numeric 
> type, with the result type being the integer square root for foo >= 0 
> and producing an error if negative? (And with infinities and NaNs 
> processed as described in 8.1 Numeric Types).
>
> I did not see an explicit enhancement request list in the PG mailing 
> lists. Recommendations for a more specific list to post this to are 
> welcome.
>
>

I suspect the majority are comfortable getting a non-integer result for 
the square root of a number.  What environment have you which needs 
(sqrt(a) * sqrt(a)) < a-epsilon?

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

* Re: integer square root function proposed
@ 2022-12-18 03:40  Martin L. Buchanan <martinlbuchanan@gmail.com>
  parent: Rob Sargent <robjsargent@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Martin L. Buchanan @ 2022-12-18 03:40 UTC (permalink / raw)
  To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Dear Rob and all readers:

Generating prime numbers is one example where you use integer square root
in the inner loop, going from integer to integer.

Calculating an integer square root from an integer input may have a more
efficient algorithm than doing so in floating-point, with the caveat that
an underlying processor architecture may provide floating-point square root
instructions but not integer square root instructions. In that particular
case an implementation could use the floating-point instructions internally.

Some but not all programming languages provide isqrt directly, math.isqrt
in Python or isqrt in Common Lisp for example.

It would be a useful and convenient function and would not, I believe,
impair the other features of PostgreSQL in any way.

That said, as a PG novice (2+ years now), I completely defer to the greater
wisdom of those much more involved in PostgreSQL. So something for you all
to think about.

Best wishes, Happy Channukah, and Merry Christmas,

Martin L. Buchanan
software developer and writer since 1976
Laramie, WY

On Sat, Dec 17, 2022 at 8:20 PM Rob Sargent <robjsargent@gmail.com> wrote:

> On 12/17/22 19:39, Martin L. Buchanan wrote:
>
> Dear PostgreSQL colleagues:
>
> I have just joined this, my first PG mailing list.
>
> Reading the documentation I found no built-in function for integer square
> root, requiring a sequence of:
>
> floor(sqrt(foo))::integer
>
> to go from an integer to the integer square root as an integer.
>
> If PG leaders smile on this suggestion, could we have *isqrt*(foo) where
> foo has a numeric type and the result type is the same numeric type, with
> the result type being the integer square root for foo >= 0 and producing an
> error if negative? (And with infinities and NaNs processed as described in
> 8.1 Numeric Types).
>
> I did not see an explicit enhancement request list in the PG mailing
> lists. Recommendations for a more specific list to post this to are welcome.
>
>
>
> I suspect the majority are comfortable getting a non-integer result for
> the square root of a number.  What environment have you which needs
> (sqrt(a) * sqrt(a)) < a-epsilon?
>
>

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

* Re: integer square root function proposed
@ 2022-12-18 03:56  Rob Sargent <robjsargent@gmail.com>
  parent: Martin L. Buchanan <martinlbuchanan@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Rob Sargent @ 2022-12-18 03:56 UTC (permalink / raw)
  To: Martin L. Buchanan <martinlbuchanan@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On 12/17/22 20:40, Martin L. Buchanan wrote:
> Dear Rob and all readers:
>
> Generating prime numbers is one example where you use integer square 
> root in the inner loop, going from integer to integer.
>
> Calculating an integer square root from an integer input may have a 
> more efficient algorithm than doing so in floating-point, with the 
> caveat that an underlying processor architecture may provide 
> floating-point square root instructions but not integer square root 
> instructions. In that particular case an implementation could use the 
> floating-point instructions internally.
>
> Some but not all programming languages provide isqrt 
> directly, math.isqrt in Python or isqrt in Common Lisp for example.
>
> It would be a useful and convenient function and would not, I believe, 
> impair the other features of PostgreSQL in any way.
>
> That said, as a PG novice (2+ years now), I completely defer to the 
> greater wisdom of those much more involved in PostgreSQL. So something 
> for you all to think about.
>

Seasons greetings to you and welcome to the list.  Here we generally 
bottom post.

I have zero say in whether or not isqrt might get added to postgres but 
in the meantime you might craft your own function (sql or plpgsql) which 
does the necessary casting.  Or are you specifically after a faster 
function?  And would a plpythonu function using their isqrt be fast 
enough.  (Note the "u" in plpythonu means "untrusted")

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

* Re: integer square root function proposed
@ 2022-12-18 04:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Martin L. Buchanan <martinlbuchanan@gmail.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2022-12-18 04:20 UTC (permalink / raw)
  To: Martin L. Buchanan <martinlbuchanan@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

"Martin L. Buchanan" <martinlbuchanan@gmail.com> writes:
> Reading the documentation I found no built-in function for integer square
> root, requiring a sequence of:
> floor(sqrt(foo))::integer
> to go from an integer to the integer square root as an integer.

I'm a little down on this idea, mainly because I doubt there is any
principled argument as to whether the rounding ought to be down, up,
or to nearest.  Specific applications probably want a specific one
of those, but how likely is it that a built-in choice would satisfy
many users?

I'm not buying the performance angle too much either.  Even granting
that your platform has an integer square root instruction that's faster
than its floating-point one, and that we can get at that from C, the
difference would likely be swamped by the overhead of the SQL function
call.

> (And with infinities and NaNs processed as described in
> 8.1 Numeric Types).

Our integers do not have infinities, nor NaNs.

Anyway, I don't mean to slam the door on this idea completely,
but I think you need to make a much better-supported argument
for it.

			regards, tom lane





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


end of thread, other threads:[~2022-12-18 04:20 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-18 02:39 integer square root function proposed Martin L. Buchanan <martinlbuchanan@gmail.com>
2022-12-18 03:20 ` Rob Sargent <robjsargent@gmail.com>
2022-12-18 03:40   ` Martin L. Buchanan <martinlbuchanan@gmail.com>
2022-12-18 03:56     ` Rob Sargent <robjsargent@gmail.com>
2022-12-18 04:20 ` Tom Lane <tgl@sss.pgh.pa.us>

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