Development of a Mathematics Extension to PostgreSQL

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.

1 Like

Hello @Z1234,
I cannot speak for everyone at Percona, but officially we can take your request under consideration for future enhancements to Percona’s PostgreSQL Distribution. We have many coders and tinkerers here at Percona, so perhaps one of them might be able to assist you, however your description above looks incredibly daunting and could take 6-12 months for an engineer to complete in their spare time.

1 Like

Dear Matthew,

-is there any possibility that my outlined extension could be done for the general version
of the PostgreSQL DBMS, and not just the Percona one?

-Could development theoretically be made faster if more than one volunteer developer
sets to work on this?

Just asking at this stage!

Z1234.

1 Like

Percona does not have a version of PGSQL. We have our own distribution which includes additional utilities and other tools, including the same core community version of PGSQL.

I’m going to be straight up honest, this is a HUGE undertaking. It is unlikely to be picked up by a volunteer. This is easily 6+ months of work for a full-time dedicated engineer to accomplish, let alone asking for multiple volunteers to assist on.

I will still share your ask with our community, but this is a HUGE HUGE ask.

1 Like

I do agree, it does get to be much larger, and the area that I think does it is the area of REIFIED SUPPORT.

I was wondering, does building two new types that have the properties of being a larger range integer, and the other a larger range decimal (rational), do or can such properties automatically inherit the ability to be indices and size limits for arrays, and so forth? Is it possible for my stipulated HPZ and HPQ types to just automatically inherit and facilitate abilities to broader PostgreSQL phenomena, or not?

For example, if I want HPZ to be applicable for indexing as a column type on one table with lots of data in it, can that just be “inherited” somehow from PostgreSQL, already coded, or do properties like this have to be implemented from nothing, from the ground up?

I am still hoping that my plugin can be done for the broader PostgreSQL DBMS. Is the Percona DBMS
automatically compatible with PostgreSQL?

1 Like

As I said above, Percona’s PostgreSQL is the same as community. Anything that works for Percona will work on community and anything that works on community will work on Percona since they are the same thing.

1 Like

-If it can all be done for free, comes with multiplatform, independent offline installers, and has all the properties outlined in the specification, I would be thrilled if Percona would be willing to go ahead with development and release of my HPPM plugin.

-Can someone please reply back to me about my question on new extension number types and reified
support? Will additional use properties get inherited to new integer numbers and decimal numbers,
or does that have to be specifically built on and implemented additionally, separately?

1 Like

Dear Matthew, and Percona,

this email pertains to my request towards a PostgreSQL Enhanced Mathematics Plugin, as per

https://forums.percona.com/t/development-of-a-mathematics-extension-to-postgresql/15025.

-If it can all be done for free, comes with multiplatform, independent offline installers, and has all the

properties outlined in the specification, I would be thrilled if Percona would be willing to go ahead with

development and release of my HPPM plugin.

-Can someone please reply back to me about my question on new extension number types and reified
support? Will additional use properties get inherited to new integer numbers and decimal numbers,
or does that have to be specifically built on and implemented additionally, separately?

Does Percona have anything more to say about this request? Is there any active interest within Percona?

Yours sincerely,

S.M.

1 Like

Hello @Z1234,
As I stated above, this is an enormous task. If there is more interest from the PGSQL community, we can add this feature request to our roadmap for consideration (absolutely no guarantee it will be accepted). Right now, you are the only one requesting this. I’ve marked this question solved but will leave it here for others to comment on. If I were you, I would not be getting my hopes up due to the massive effort of your request.

1 Like

Dear Percona and matthewb,

I am resubmitting this here, in case you and any Percona people havn’t read what I have most recently put to the forums.

At the moment, PostgreSQL has floating point denormal and pronormal errors to be contended with. If you want to use the Decimal type, and do trigonometry, the only available trigonometry functions are not for the Decimal type. So presently, you have to change type beforehand, and/or afterwards, to say nothing of any range or value extent problems, and you can still get stuck with denormal and pronormal values. My suggested extension avoids all that, while attempting to create a “near-to-no-limits” situation for the extent, the precision, of an integer or rational decimal value, certainly for as far as a modern desktop PC or Server can fit in memory, or load into memory, finish computing, and then store, either in a variable, a file or in a database table column. Since it would be released for free, including source and resource code, networking PCs, supercomputer and mainframe people can change it for their needs if they will.

Yours Sincerely,

Z1234.

1 Like

Hi Z1234,

is your code already accessible on github or another revision tool, so we can actually take a look at your code?

I would like to forward your thoughts to the right people and want to make sure they get all the information that is needed and also available at that point in time.

Thanks a lot,
Kai

1 Like

What’s happened is that my developer has deviated from the path a bit in terms of what I requested, and then has had to totally drop the project. Any milestone code from him that I have won’t be useful for you, because I am hoping that if the extension does go ahead through someone involved with Percona, that
they will stick to the specifications, this time. The specification is attached here:

PostgreSQL Extension.txt (10.7 KB)

1 Like

Due to the nature of how the previous attempt at this project has concluded, certainly for now, there isn’t exactly any pre-developed useful code that strictly applies to the specification for it. Is there any news for me about discussion around this suggested PostgreSQL extension?

1 Like

There is no news at this time, and I would not expect any for at least a month or two. Percona engineers are currently focusing on the release of the Percona Platform and handling repacking of recently released versions of both PGSQL and MySQL.

1 Like

To matthewb and Percona,
given the how busy Percona has been, and that about one or two months, thereabouts, has past,
is there any more news about Percona in relation to my HPPM mathematics extension/database update
for original PostgreSQL getting aprehended and implemented for real?

1 Like

Only 22 days have past since your last post; not even 1 month. I have shared this post with our entire PGSQL community. If anyone wants to volunteer they will post an update here.

1 Like

My information tells me that the last time I contacted matthewb was on the 19/04/2022. The point of 19/06/2022 has passed by 3 days now, so if I am right, it has been two months. Matthew, is there any more to be said or done on the subject of my PostgreSQL extension request proposition, without being rude or applying any undue pressure?

1 Like

I don’t think there is any more to be said. This post is open for anyone to comment on if they have the time to help. I’ve shared this post with our entire PGSQL team as well as with our SvP of Engineering. If there is anything more to be said from them, they will post here.

1 Like

Aside from exposure to the Percona team, is there anyone such as yourself inside Percona who could recommend where else a volunteer project like this could be sent?

1 Like

https://www.postgresql.org/community/

1 Like