integer square root function proposed
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
Re: integer square root function proposed
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 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? > >
Re: Intervals and ISO 8601 duration
On Fri, Jan 13, 2023 at 5:03 PM Bryn Llewellyn wrote: > ken.tan...@gmail.com wrote: > > Here's an example. Note that they come out formatted differently with > to_char, but evaluate as equal. The explanation(1) was that they were > Equal but not Identical. I was thinking getting the raw data about how they > are stored would get at the identicality issue: > > WITH inters AS ( > SELECT > '1 day 2 hours'::interval AS i1, > '26 hours'::interval AS i2 > ) > SELECT > *, > to_char(i1,'HH24:MM:SS') AS i1_char, > to_char(i2,'HH24:MM:SS') AS i2_char, > i1=i2 AS "Equal?" > FROM inters; > >i1 |i2| i1_char | i2_char | Equal? > +--+--+--+ > 1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t > > > I struggled to understand this whole murky area when I was writing the > “Date and time data types and functionality” section for the YugabyteDB > doc. (YugabyteDB uses the Postgres SQL processing code “as is” on top of > its own distributed storage layer. All the examples in my doc work > identically in vanilla PG.) > > The implied question here is this: is the interval “1 day 2 hours” the > same as the interval “26 hours”? It might seem that the answer is “yes”—as > it surely must be. But, sorry to say, that the answer is actually “no”. > Confused? You will be. Most people are until they’ve wrapped their head in > a towel and puzzled it through for a few days. This shows you what I mean: > > set timezone = 'America/Los_Angeles'; > with c as ( > select '2023-03-11 20:00 America/Los_Angeles'::timestamptz as > original_appointment) > select > original_appointment::text as "original appointment", > (original_appointment + '1 day 2 hours'::interval)::text as "postponed > by '1_day 2 hours'", > (original_appointment + '26 hours'::interval)::text as "postponed by > '24_hours'" > from c; > > This is the result: > > original appointment | postponed by '1_day 2 hours' | postponed by > '24_hours' > > +--+- > 2023-03-11 20:00:00-08 | 2023-03-12 22:00:00-07 | 2023-03-12 > 23:00:00-07 > > Two different answers! The “trick” here is that the time of the original > appointment and the postponed times straddle the 2023 “spring forward” > moment (at least as it happens in the America/Los_Angeles timezone). And > the resolution of what at first might seem to be a bug come when you > realized that you must make a distinction between clock time and calendar > time. > > This query sheds a bit more light on the matter: > > > > > > > > > *with c(i1, i2) as ( select '1 day 2 hours'::interval, '26 > hours'::interval)select interval_mm_dd_ss(i1)::text as i1, > interval_mm_dd_ss(i2)::text as i2, (i1 = i2)::text as "i1 = i2", > (i1==i2)::text as "i1 == i2"from c;* > > I defined the “interval_mm_dd_ss()” function and the “==” operator. (I > called it the “strict equality operator for interval values”.) > > I believe that your question implies that you want > my “interval_mm_dd_ss()” function. I can’t be sure what you want. But I > dare to speculate that you might find it helpful to read (at least) the > references that I’ve listed below. Start with the informal treatment in my > blog post. > > Tom, Adrian, and David might remember my endless questions in this general > space in March 2021. This, from Tom, answers the present question: > > > https://www.postgresql.org/message-id/DAABCBE6-6DFF-4C28-9CED-0AA8053FE12C%40yugabyte.com > > > p.s. Some other posts came in while I was writing this. My advice on > “justify_interval()” is to avoid it. > > > PostgreSQL Timestamps and Timezones: How to Navigate the Interval Minefield > > https://www.yugabyte.com/blog/postgresql-timestamps-timezones-interval-minefield/ > > Two ways of conceiving of time: calendar-time and clock-time > > https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/conceptual-background/#two-ways-of-conceiving-of-time-calendar-time-and-clock-time > > type interval_mm_dd_ss_t as (mm, dd, ss) > > https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#type-interval-mm-dd-ss-t-as-mm-dd-ss > > The user-defined "strict equals" interval-interval "==“ operator > > https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#the-user-defined-strict-equals-interval-interval-operator > * Just tried casting interval to bytea to see the binary layout, but that direct cast is not allowed. Sincerely, Martin L Buchanan postgreSQL database developer (for about 2.5 years now) (and not knowledgeable about administering PG or the internals of PG) Laramie, WY, USA
Boyer-Moore string searching in LIKE, ILIKE, and/or POSITION?
In the PostgreSQL Todo wiki, Boyer-Moore string searching for LIKE is
mentioned as an outstanding item.
For the common and simple cases of find this string anywhere in another
string:
str LIKE '%foo%'
str ILIKE '%foo%'
position('foo' in str) > 0
Is Boyer-Moore string searching now used by any of these three?
I checked the PG documentation and found no info about this other than what
was in the Todo wiki, https://wiki.postgresql.org/wiki/Todo, under
Functions. Tom Lane gave a thumbs down to the idea back in 2008, but that
was a long time ago:
https://www.postgresql.org/message-id/27645.1220635...@sss.pgh.pa.us .
Sincerely,
Martin L Buchanan
senior software engineer
Laramie, WY, USA
Re: RLS without leakproof restrictions?
>> On Tue, Feb 21, 2023 at 5:57 PM Tom Dunstan wrote: > Hi all > > I'm currently researching different strategies for retrofitting some > multi-tenant functionality into our existing Postgres-backed application. >> > One of the options is using RLS policies to do row filtering. This is > quite attractive as I dread the maintenance and auditing burden of >> adding > filtering clauses to the majority of our queries. I'm somewhat concerned > though about getting unexpected query plans based on the planner avoiding > non-leakproof functions until row filtering has occurred - warning about > this seems common in articles on RLS. > > Our application is the only "user" of the database, and we do not pass > database errors through to the user interface, so for our case leakproof > plans are overkill - we'd just like the implicit filtering clauses added > based on some session GUCs that we set. > > Is there any way to get what we're looking for here? I don't see anything > documented on CREATE POLICY, ALTER TABLE or any GUCs. > > Alternatively, are the concerns about changed plans unfounded? For example > we don't use many expression indexes or exotic types, it's mostly btrees on > text and ints. We do use tsearch a certain amount, but constructing > tsvectors and tsqueries manually rather than through stemmers etc. > > Thanks > > Tom > << OK, I don't have that PG >> look quite right. Anyway, Tom if it is feasible to put each tenant into its own database on the same server instance, that is what I recommend. Even with row level security, a tenant ID on each row in each table and each view and each function or procedure that deals with tenant-specific data, is a headache that can also complicate queries and query plans. (Am speaking for myself as a PG developer for the last 2.5 years, not for my employer.) Sincerely, Martin L Buchanan Laramie, WY, USA
