Thread: Exponantial Function (exp) with bigger argument ?
Hi,
Is there any possibility to overcome the restriction to not use a higher number than appr. 100 as an argument for the exponential function in postgres ? Currently, I try to shift a data related part of our business logic into the database to save some computation time. Unfortunately, this involves the exponential function with arguments which become bigger than 100. I had a look on the pgmp extension, but it seems it does not provide any exponential function. In our ruby project we are using the mpc and mpfr modules which are linked to the corresponding c-libraries. Would be recompilation of Postgres with mpc/mpfr work ? If yes, how would I do this ? Is there any ready-to-use extension which enables me to use the provided types of mpc and mpfr ? If yes, where can I find them ? Is there any other solution to my problem, which I did not thought of ?
I’m looking forward to your answers,
Regards,
Gregor
"Gregor GT. Trefs" <gregor.trefs@delphit.com> writes:
> Is there any possibility to overcome the restriction to not use a
> higher number than appr. 100 as an argument for the exponential
> function in postgres ?
What restriction?
regression=# select exp(1000.0);
exp
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
197007112172228764285408431116443651047300981094802008755853985828350443654498948052236544085754035611119684706809582853182955874910502440327439392806215139501177952207939837125875818468684009900351395991300432715505235653014871192637790968152087501139107799418773107714238073871162818125586289629983090134192022542188366296411429692950563401460360277794420292466575635467954804734395417151344955106563797211636884598324669752528955502.2
(1 row)
Mind you, it would be unwise to assume that that answer is *exact*
... exactly what is the "business logic" that requires such large
exponentials, and are you sure you're getting non-garbage results on any
other platform?
regards, tom lane
Thanks for the answer Tom. As you suggested, I converted the integer to a numeric type and found out, that the
correspondingexp function can handle numbers up to 6999.0. But this is still not enough. Currently, I'm trying to
extendPostgres with a custom C-Function. But, I am running into troubles there, too.
This could also be due to my nescience concerning C and Linux. My code looks as follows:
/*
** Filename: exp_c.c
*/
#include "postgres.h"
#include "fmgr.h"
// Magic block
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
#include "mpfr.h"
// We're using the version-1 calling method
PG_FUNCTION_INFO_V1(exp_c);
Datum exp_c(PG_FUNCTION_ARGS){
if (PG_ARGISNULL(0) || PG_ARGISNULL(1)){
PG_RETURN_NULL();
}
float8 exponent = PG_GETARG_FLOAT8(0);
int32 precision = PG_GETARG_INT32(0);
// The result
mpfr_t x;
// Initialize variable with given precision
mpfr_init2(x, precision);
// Set to given exponent
mpfr_set_ld(x, exponent, MPFR_RNDN);
// Calculate
mpfr_exp(x,x, MPFR_RNDN);
// Get return value
long double rs = mpfr_get_ld(x,MPFR_RNDN);
// Clear x
mpfr_clear(x);
// Return
PG_RETURN_FLOAT8(rs);
}
I compiled the program with "gcc -c -fpic exp_c.c -lmpfr -lgmp" and created a shared library with this one "gcc -shared
-oexp_c.so exp_c.o".
This worked fine. Btw. we are using Ubuntu 10.10 Server and postgres 8.4 (version() function output: "PostgreSQL 8.4.8
onx86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 64-bit").
I copied the exp_c.so tot he $libdir directory. In postgres the following commands were executed:
CREATE OR REPLACE FUNCTION exp(exponent double precision, prec integer) RETURNS double precision AS
'$libdir/exp_c','exp_c'LANGUAGE c;
SELECT exp(1::double precision, 2);
Unfortunately this does not work. The output is (Translated into english):
FAILURE: Library »/usr/lib/postgresql/8.4/lib/exp_c.so« could not be loaded: /usr/lib/postgresql/8.4/lib/exp_c.so:
undefinedsymbol: mpfr_get_ld
********** FAILURE **********
FAILURE: Library »/usr/lib/postgresql/8.4/lib/exp_c.so« could not be loaded: /usr/lib/postgresql/8.4/lib/exp_c.so:
undefinedsymbol: mpfr_get_ld
SQL Status:58P01
I think this has something to do with the binder/linker and the mpfr header file, which is not available during
linking.Am I correct ? How can I overcome this ?
Kind regards,
Gregor
-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Dienstag, 3. Mai 2011 20:01
An: Gregor GT. Trefs
Cc: pgsql-novice@postgresql.org
Betreff: Re: [NOVICE] Exponantial Function (exp) with bigger argument ?
"Gregor GT. Trefs" <gregor.trefs@delphit.com> writes:
> Is there any possibility to overcome the restriction to not use a
> higher number than appr. 100 as an argument for the exponential
> function in postgres ?
What restriction?
regression=# select exp(1000.0);
exp
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
197007112172228764285408431116443651047300981094802008755853985828350443654498948052236544085754035611119684706809582853182955874910502440327439392806215139501177952207939837125875818468684009900351395991300432715505235653014871192637790968152087501139107799418773107714238073871162818125586289629983090134192022542188366296411429692950563401460360277794420292466575635467954804734395417151344955106563797211636884598324669752528955502.2
(1 row)
Mind you, it would be unwise to assume that that answer is *exact* ... exactly what is the "business logic" that
requiressuch large exponentials, and are you sure you're getting non-garbage results on any other platform?
regards, tom lane