Thread: PostgreSQL High Precision Support Extension.
I have been trying to get a reply or interest in either updating PostgreSQL to support the following,
or for there to be a public, free for any use Extension put out there, that will support the following.
Can someone able and interested please respond to me about the following project specification,
which I am very keen to see happen:
###################################################
# High Precision Numeric and Elementary Functions Support. #
###################################################
-Integer (HPZ) Z, or Rational Decimal Q (HPQ) numbers support.
-A library like GMP, written in C, is an appropriate basis to start from and to include, for all OS platforms involved.
-Real numbers include the values of both Recurring Rational Numbers and recurring Irrational Numbers. Those two can be appropriately truncated, by a precision value, to obtain an approximating value. The latter phenomenon is a finite Rational value, possibly with integer and/or decimal parts at the same time. These also may be positive or negative, or zero, standard number line, values.
-Forward and Inverse operations accuracy, withstanding truncation, can be maintained by storing and normalising the expression behind a value, (or by just including pointers to value(s)) and displaying that value. This system will uphold any precision, certainly within a very large range limit.
-A defaulting number of significant figures, (precision), in a field in memory that exists as one copy per connection, that is updated, as a filter, for all relevant HPZ and HPQ numbers. For example, 20 significant figures, as a default, would be sensible to start with.
-A function that varies the precision filter for every HPZ and HPQ number at once.
-Value assignment to a typed variable by =.
-Operators. Base 10 Arithmetic and comparisons support on Base 10 Integer and Rational Decimal numbers, and casting:
+,-,*,/,%,^,=,!=,<>,>,<,>=,<=, ::
These include full finite division and integer only division, with no remainder, and remainder division. The defaulting ability of values within the two new types to automatically be cast up to HPZ or HPQ, where specified and appropriate in PostgreSQL code.
-Reified support with broader syntax and operations within PostgreSQL, in all the obvious and less than obvious places. Tables and related phenomena, HPZ arrays, Indexing, the Window type, Record type, direct compatability with Aggregate and Window Functions, the Recursive keyword, are all parts of a larger subset that should re-interact with HPZ or HPQ.
-Ease of installation support. Particularly for Windows and Linux. *.exe, *.msi or *.rpm, *.deb, *.bin installers.
Upon a PostgreSQL standard installation. Installation and Activation instructions included. That is, presuming the HPZ and HPQ support is an Extension, and simply not added as native, default types into PostgreSQL Baseline.
##############################################################
-Mathematical and Operational functions support:
precision(BIGINT input)
cast(HPZ as HPQ) returns HPQ;
cast(HPQ as HPZ) returns HPZ;
cast(TEXT as HPZ) returns HPZ;
cast(TEXT as HPQ) returns HPQ;
cast(HPQ as TEXT) returns TEXT;
cast(HPZ as TEXT) returns TEXT;
cast(HPZ as SMALLINT) returns SMALLINT;
cast(SMALLINT as HPQ) returns HPZ;
cast(HPZ as INTEGER) returns INTEGER;
cast(INTEGER as HPZ) returns HPZ;
cast(HPZ as BIGINT) returns BIGINT;
cast(BIGINT as HPZ) returns HPZ;
cast(HPQ as REAL) returns REAL;
cast(REAL as HPQ) returns HPQ
cast(DOUBLE PRECISION as HPQ) returns HPQ;
cast(HPQ as DOUBLE PRECISION) returns DOUBLE PRECISION;
cast(HPQ as DECIMAL) returns DECIMAL;
cast(DECIMAL as HPQ) returns HPQ;
cast(HPQ as NUMERIC) returns NUMERIC;
cast(NUMERIC as HPQ) returns HPQ;
sign(HPQ input) returns HPQ;
abs(HPQ input) returns HPQ;
ceil(HPQ input) returns HPQ;
floor(HPQ input) returns HPQ;
round(HPQ input) returns HPZ;
recip(HPQ input) returns HPQ;
pi() returns HPQ;
e() returns HPQ;
power(HPQ base, HPQ exponent) returns HPQ;
sqrt(HPQ input) returns HPQ
nroot(HPZ theroot, HPQ input) returns HPQ;
log10(HPQ input) returns HPQ;
loge(HPQ input) returns HPQ;
log2(HPQ input) returns HPQ;
factorial(HPZ input) returns HPZ;
nCr(HPZ objects, HPZ selectionSize) returns HPZ
nPr(HPZ objects, HPZ selectionSize) returns HPZ
degrees(HPQ input) returns HPQ;
radians(HPQ input) returns HPQ;
sind(HPQ input) returns HPQ;
cosd(HPQ input) returns HPQ;
tand(HPQ input) returns HPQ;
asind(HPQ input) returns HPQ;
acosd(HPQ input) returns HPQ;
atand(HPQ input) returns HPQ;
sinr(HPQ input) returns HPQ;
cosr(HPQ input) returns HPQ;
tanr(HPQ input) returns HPQ;
asinr(HPQ input) returns HPQ;
acosr(HPQ input) returns HPQ;
atanr(HPQ input) returns HPQ;
##############################################################
-Informative articles on all these things exist at:
Comparison Operators: https://en.wikipedia.org/wiki/Relational_operator
Floor and Ceiling Functions: https://en.wikipedia.org/wiki/Floor_and_ceiling_functions
Arithmetic Operations: https://en.wikipedia.org/wiki/Arithmetic
Integer Division: https://en.wikipedia.org/wiki/Division_(mathematics)#Of_integers
Modulus Operation: https://en.wikipedia.org/wiki/Modulo_operation
Rounding (Commercial Rounding): https://en.wikipedia.org/wiki/Rounding
Factorial Operation: https://en.wikipedia.org/wiki/Factorial
Degrees: https://en.wikipedia.org/wiki/Degree_(angle)
Radians: https://en.wikipedia.org/wiki/Radian
Elementary Functions: https://en.wikipedia.org/wiki/Elementary_function
The following chart could be used to help test trigonometry outputs, under
Further Condsideration of the Unit Circle:
https://courses.lumenlearning.com/boundless-algebra/chapter/trigonometric-functions-and-the-unit-circle/
##############################################################On Tue, Sep 21, 2021 at 1:30 PM A Z <poweruserm@live.com.au> wrote: > -A library like GMP, written in C, is an appropriate basis to start from and to include, for all OS platforms involved. Are you aware of Daniele Varrazzo's extension https://github.com/dvarrazzo/pgmp/ ? (Never looked into it myself, but this seems like the sort of thing you might be looking for?)
On 9/20/21 9:29 PM, A Z wrote: > I have been trying to get a reply or interest in either updating > PostgreSQL to support the following, > or for there to be a public, free for any use Extension put out there, > that will support the following. > Can someone able and interested please respond to me about the > following project specification, > which I am very keen to see happen: Please stop posting the same thing over and over. It doesn't help you, in fact it's likely to put off anyone who might be interested in your project. If you haven't got an answer by now you should conclude that nobody here is interested. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Tue, Sep 21, 2021 at 2:58 PM Thomas Munro <thomas.munro@gmail.com> wrote: > On Tue, Sep 21, 2021 at 1:30 PM A Z <poweruserm@live.com.au> wrote: > > -A library like GMP, written in C, is an appropriate basis to start from and to include, for all OS platforms involved. > > Are you aware of Daniele Varrazzo's extension > https://github.com/dvarrazzo/pgmp/ ? (Never looked into it myself, > but this seems like the sort of thing you might be looking for?) [A Z replied off-list and mentioned areas where pgmp falls short, but I'll reply on-list to try to increase the chance of useful discussion here...] It seems to me that there are 3 or 4 different topics here: 1. Can you find the functions GMP lacks in some other library? For example, if I understand correctly, the library "mpfr" provides a bunch of transcendental functions for libgmp's types. Are there other libraries? Can you share what you already know about the landscape of relevant libraries and what's good or lacking from your perspective? Or are you proposing writing entirely new numeric code (in which case that's getting pretty far away from the topics we're likely to discuss here...). 2. Supposing there are suitable libraries that build on top of GMP, would it be reasonable to make a separate extension that extends pgmp? That is, users install the pgmp extension to get the basic types and functions, and then install a second, new extension "pmpfr" (or whatever) to get access to more functions? 3. You mentioned wanting portability and packages for platforms X, Y, Z. Packaging is something to worry about later, and not typically something that the author of an extension has to do personally. Once you produce a good extension, it seems very likely that you could convince the various package maintainers to pick it up (as they've done for pgmp and many other extensions). The only question to worry about initially is how portable the libraries you depend on are. For what it's worth, I'd personally start by setting up a CI system for a bunch of relevant OSes with all the relevant libraries installed, for exploration; I could provide some pointers on how to do that if you think that would be interesting. 4. You talked about whether such types could be in PostgreSQL "core". In my humble opinion (1) this is a textbook example of something that belongs in an extension, and (2) things built on GNU/GPL libraries are complicated and probably couldn't ever be included in core in our BSD-licensed project anyway. (In contrast, the SQL:2016 DECFLOAT(n) types really should be included in the core system, because they're in the SQL standard and we are a SQL implementation, and AFAIK the only real thing stopping us from doing that is deciding which library to use to do it, which is complicated.)
On 9/21/21 6:20 PM, Thomas Munro wrote: > On Tue, Sep 21, 2021 at 2:58 PM Thomas Munro <thomas.munro@gmail.com> wrote: >> On Tue, Sep 21, 2021 at 1:30 PM A Z <poweruserm@live.com.au> wrote: >> > -A library like GMP, written in C, is an appropriate basis to start from and to include, for all OS platforms involved. >> >> Are you aware of Daniele Varrazzo's extension >> https://github.com/dvarrazzo/pgmp/ ? (Never looked into it myself, >> but this seems like the sort of thing you might be looking for?) > > [A Z replied off-list and mentioned areas where pgmp falls short, but > I'll reply on-list to try to increase the chance of useful discussion > here...] This seems to become a common pattern to open source communities. Not just PostgreSQL, I have seen it elsewhere. People make vague or just specification level "proposals" and as soon as anyone replies, try to drag it into private and off-list/off-forum conversations. Most of the time this is because they aren't really proposing anything, but are just looking for someone else to implement what they need for their own, paying customer. They are not willing or able to contribute anything but requirements. As the original author of the NUMERIC data type I am definitely interested in this sort of stuff. And I would love contributing in the design of the on-disk and in-memory structures of these new data types created as an EXTENSION. However, so far I only see a request for someone else to create this extension. What exactly is "A Z" (poweruserm) going to contribute to this effort? Regards, Jan -- Jan Wieck