I have been trying to find active interest in Percona for volunteer development of a GPL license style PostgreSQL extension, complete and available, on Sourceforge, PGXN and the public internet, that will support the following:
High Precision Arithmetic and Elementary Functions Support
In PostgreSQL v14 or beyond.
HPPM: High Precision PostgreSQL Mathematics.
-The introduction of Integer Z, or Rational Mixed Decimal Q, numbers support in 64 bit PostgreSQL. Via HPZ, and HPQ, original types. In this specification, they are collectively referred to as HPX types. These two types can be spelled in capital letters, or lower case. They are:
HPZ, HPQ or hpz, hpq.
HPZ(n), HPQ(n), hpz(n), or hpq(n).
HPX types can be declared like TEXT (with no associated value) or with an associated value, like varchar(n). IF HPX variables are declared with no associated value, the associated value for that variable is 20.
There should be range and multirange types corresponding to both HPX types:
HPZRANGE, hpzrange, HPZMULTIRANGE, hpzmultirange.
HPQRANGE, hpqrange, HPQMULTIRANGE, hpqmultirange.
-The extension could be based on another 3rd party library, and will be written in C, in either case. There is already some support for this kind of mathematics, in terms of its logic, and optimisation, publicly available, in C, as Free Open Source Software. That can be researched and apprehended for this extension and for all the relevant PostgreSQL OS platforms that it (HPPM) is designed for.
-Real numbers are comprised of the values of Integer, non-recurring Rational Numbers and recurring, and/or Irrational Numbers. Recurring numbers can be appropriately truncated, ultimately via another positive Integer value, always at least 1, to obtain a limited and approximating value. The approximating value can really be seen as a finite Rational number, possibly with Integer or Decimal parts, or both. These numbers may be positive or negative, or zero, scalar values, and always do exist on the one dimensional number line. These numbers may be positive, negative, or zero exactly.
-HPX âassociated valuesâ really are a number of relevant positive Integer figures (Precision), that will get stored with each HPX type variable or column type. These are specified at type or variable declaration, before further use. Or the total defaulting precision amount is applied, being 20, as already shown.
Precision can be accessed and changed by means of coded values being changed. Precision is always apprehended before calculation begins. Precision is used to control number operations, and value output, when any numeric manipulation or processing occurs, since things that may go towards an infinity need to be stopped before then, to be useful.
If an HPX value is data on its own, without any set precision, it has the corresponding precision amount. If it is inserted into a table column with a different precision, then that precision is applied, be it larger, equal, or less, resulting in no behaviour and an error being thrown.
If an HPX value, in a PostgreSQL code expression, is sent straight into a RETURN statement or a SELECT statement, without assignment or precision alteration, or is just specified in a comparison expression, then that datum will contain the highest precision value out of any of the others in its expression, by checking the largest one found as the expression is considered, from left to right, within its sub expression.
If such a precision value cannot be arrived at, since nothing has been specified, because the value is irrational or infinitely reccuring, then the default precision value, for truncation to be applied by, will be 20, here, of course.
-This whole system will uphold any precision, certainly ones within a very large range limit, controlled by the already available type for large positive integers, the BIGINT. It can thereby enumerate digits within the range of
(+/-)1 to (+/-)9,223,372,036,854,775,807. This is at least between one and positive nine quintilion digit places. More than enough for the speed and scope of today, or maybe tomorrow, and the Desktop PC, as either a client or a server.
Naturally, evaluation will slow down, or not conclude in useful time frames, before those limits, presently. That phenomenon can be allowed, and left to the programmer to deal with or curtail.
A TEXT variable or table column, or even another HPX or numeric type (if the data of the value is in range) can be used to store digit data alone. Mixed Decimals can be broken down into integers and dealt with from there using operators, and reassembled into mixed integers again, if absolutely necessary, although this will be slower and inneficient internally.
âAt the point of PostgreSQL code input and execution:
select pi(1001) as pi;
âHPX types can be declared like TEXT or
âlike varchar(n),
âWithin a table creation command:
create table example_table
(
id BIGSERIAL PRIMARY KEY,
a HPZ,
b HPQ(50)
);
ALTER TABLE example_table ALTER COLUMN b TYPE HPQ(30);
ALTER TABLE example_table ALTER COLUMN b TYPE HPQ(30);
INSERT INTO example_table(a,b) VALUES(0, 0.1);
INSERT INTO example_table(a,b) VALUES(100,1.1);
INSERT INTO example_table(a,b) VALUES(200,2.2);
INSERT INTO example_table(a,b) VALUES(300,3.3);
INSERT INTO example_table(a,b) VALUES(400,4.4);
INSERT INTO example_table(a,b) VALUES(500,5.5);
INSERT INTO example_table(a,b) VALUES(600,6.6);
INSERT INTO example_table(a,b) VALUES(700,7.7);
INSERT INTO example_table(a,b) VALUES(800,8.8);
INSERT INTO example_table(a,b) VALUES(900,9.9);
âOr as variables, in some function:
create or replace function example_function()
returns void
language plpgsql
as
$$
declare
a HPQ;
b HPQ(2);
c HPQ(3);
begin
a = 0.1;
b = 0.1;
c=a*b;
return void
end;
$$
âRange and Multirange Types or Functions or Operators.
select hpzrange(1,3) && hpzrange(3,20) AS Intersecting;
select hpqrange(1.5,3) hpqrange(3,25.5) AS Mixed_Number_Range;
-Value assignment to a typed variable by =.
-Operators. Base 10 Arithmetic and comparisons support on Base 10 HPZ and HPQ, with casting:
::,=,!=,<>,>,<,>=,<=,+,-,*,/,%,^
These include full division and integer only division (from type inference, between two HPZ integers, only), with no remainder, and a remainder only calculating operator (for all type circumstances), within all range possibilities of the involved two values under a particular operation.
##############################################################
There should be the property of value inversion equality.
Consider the following source code fragment as an example:
a = 1;
b = 7;
c = a/b;
output(c);
d = c*b;
output(d);
output(c == d); //true
##############################################################
-REIFIED SUPPORT with broader syntax and operations and phenomena within PostgreSQL. Range and Multirange types, HPX integration with Tables, the between keyword, Array types, Indexing, Variables and related phenomena, the Record type, direct compatibility with the Aggregate and Window functions, and Partitions are all parts of a larger subset that should re-interact with HPZ or HPQ successfully. HPX types should also be integrated with Range Types, Multirange Types, Operators, their Functions and their Window Functions.
##############################################################
-Ease of installation support. Particularly for Windows and Linux. *.exe, *.msi or *.rpm, *.deb, *.bin, *.sh installer prefixes for a PostgreSQL installation from one file, each. Installation, Activation and Use instructions should be included, necessary for successful use, and the uninitiated. The extension should literally just install and be applicable, with no loading command necessary (if possible). For every time the PostgreSQL database process is run, by default.
-Mathematical and Operational functions support:
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 HPZ) 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 HPZ;
abs(HPQ input) returns HPZ;
ceil(HPQ input) returns HPZ;
floor(HPQ input) returns HPZ;
round(HPQ input) returns HPZ;
factorial(HPZ input) returns HPZ;
nCr(HPZ objects, HPZ selectionSize) returns HPZ;
nPr(HPZ objects, HPZ selectionSize) returns HPZ;
reciprocal(HPQ input) returns HPQ;
pi(BIGINT precision) returns HPQ;
e(BIGINT precision) 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;
ln(HPQ input) returns HPQ;
log2(HPQ input) returns HPQ;
radtodeg(HPQ input)returns HPQ;
degtorad(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;
Can someone reply to this message expression or describing Perconaâs interest or positiong on
implementing a volunteer extension just like this one?
End of Message.