Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit d5d5741

Browse filesBrowse files
deanrasheeddeanrasheed
committed
Add support for the error functions erf() and erfc().
Expose the standard error functions as SQL-callable functions. These are expected to be useful to people working with normal distributions, and we use them here to test the distribution from random_normal(). Since these functions are defined in the POSIX and C99 standards, they should in theory be available on all supported platforms. If that turns out not to be the case, more work will be needed. On all platforms tested so far, using extra_float_digits = -1 in the regression tests is sufficient to allow for variations between implementations. However, past experience has shown that there are almost certainly going to be additional unexpected portability issues, so these tests may well need further adjustments, based on the buildfarm results. Dean Rasheed, reviewed by Nathan Bossart and Thomas Munro. Discussion: https://postgr.es/m/CAEZATCXv5fi7+Vu-POiyai+ucF95+YMcCMafxV+eZuN1B-=MkQ@mail.gmail.com
1 parent 3a465cc commit d5d5741
Copy full SHA for d5d5741

File tree

8 files changed

+205
-1
lines changed
Filter options

8 files changed

+205
-1
lines changed

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+35Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1286,6 +1286,41 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
12861286
</para></entry>
12871287
</row>
12881288

1289+
<row>
1290+
<entry role="func_table_entry"><para role="func_signature">
1291+
<indexterm>
1292+
<primary>erf</primary>
1293+
</indexterm>
1294+
<function>erf</function> ( <type>double precision</type> )
1295+
<returnvalue>double precision</returnvalue>
1296+
</para>
1297+
<para>
1298+
Error function
1299+
</para>
1300+
<para>
1301+
<literal>erf(1.0)</literal>
1302+
<returnvalue>0.8427007929497149</returnvalue>
1303+
</para></entry>
1304+
</row>
1305+
1306+
<row>
1307+
<entry role="func_table_entry"><para role="func_signature">
1308+
<indexterm>
1309+
<primary>erfc</primary>
1310+
</indexterm>
1311+
<function>erfc</function> ( <type>double precision</type> )
1312+
<returnvalue>double precision</returnvalue>
1313+
</para>
1314+
<para>
1315+
Complementary error function (<literal>1 - erf(x)</literal>, without
1316+
loss of precision for large inputs)
1317+
</para>
1318+
<para>
1319+
<literal>erfc(1.0)</literal>
1320+
<returnvalue>0.15729920705028513</returnvalue>
1321+
</para></entry>
1322+
</row>
1323+
12891324
<row>
12901325
<entry role="func_table_entry"><para role="func_signature">
12911326
<indexterm>

‎src/backend/utils/adt/float.c

Copy file name to clipboardExpand all lines: src/backend/utils/adt/float.c
+47Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2742,6 +2742,53 @@ datanh(PG_FUNCTION_ARGS)
27422742
}
27432743

27442744

2745+
/* ========== ERROR FUNCTIONS ========== */
2746+
2747+
2748+
/*
2749+
* derf - returns the error function: erf(arg1)
2750+
*/
2751+
Datum
2752+
derf(PG_FUNCTION_ARGS)
2753+
{
2754+
float8 arg1 = PG_GETARG_FLOAT8(0);
2755+
float8 result;
2756+
2757+
/*
2758+
* For erf, we don't need an errno check because it never overflows.
2759+
*/
2760+
result = erf(arg1);
2761+
2762+
if (unlikely(isinf(result)))
2763+
float_overflow_error();
2764+
2765+
PG_RETURN_FLOAT8(result);
2766+
}
2767+
2768+
/*
2769+
* derfc - returns the complementary error function: 1 - erf(arg1)
2770+
*/
2771+
Datum
2772+
derfc(PG_FUNCTION_ARGS)
2773+
{
2774+
float8 arg1 = PG_GETARG_FLOAT8(0);
2775+
float8 result;
2776+
2777+
/*
2778+
* For erfc, we don't need an errno check because it never overflows.
2779+
*/
2780+
result = erfc(arg1);
2781+
2782+
if (unlikely(isinf(result)))
2783+
float_overflow_error();
2784+
2785+
PG_RETURN_FLOAT8(result);
2786+
}
2787+
2788+
2789+
/* ========== RANDOM FUNCTIONS ========== */
2790+
2791+
27452792
/*
27462793
* initialize_drandom_seed - initialize drandom_seed if not yet done
27472794
*/

‎src/include/catalog/catversion.h

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202303131
60+
#define CATALOG_VERSION_NO 202303141
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Copy file name to clipboardExpand all lines: src/include/catalog/pg_proc.dat
+7Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3465,6 +3465,13 @@
34653465
proname => 'atanh', prorettype => 'float8', proargtypes => 'float8',
34663466
prosrc => 'datanh' },
34673467

3468+
{ oid => '8788', descr => 'error function',
3469+
proname => 'erf', prorettype => 'float8', proargtypes => 'float8',
3470+
prosrc => 'derf' },
3471+
{ oid => '8789', descr => 'complementary error function',
3472+
proname => 'erfc', prorettype => 'float8', proargtypes => 'float8',
3473+
prosrc => 'derfc' },
3474+
34683475
{ oid => '1618',
34693476
proname => 'interval_mul', prorettype => 'interval',
34703477
proargtypes => 'interval float8', prosrc => 'interval_mul' },

‎src/test/regress/expected/float8.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/float8.out
+39Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -790,6 +790,45 @@ SELECT atanh(float8 'nan');
790790
NaN
791791
(1 row)
792792

793+
-- error functions
794+
-- we run these with extra_float_digits = -1, to get consistently rounded
795+
-- results on all platforms.
796+
SET extra_float_digits = -1;
797+
SELECT x,
798+
erf(x),
799+
erfc(x)
800+
FROM (VALUES (float8 '-infinity'),
801+
(-28), (-6), (-3.4), (-2.1), (-1.1), (-0.45),
802+
(-1.2e-9), (-2.3e-13), (-1.2e-17), (0),
803+
(1.2e-17), (2.3e-13), (1.2e-9),
804+
(0.45), (1.1), (2.1), (3.4), (6), (28),
805+
(float8 'infinity'), (float8 'nan')) AS t(x);
806+
x | erf | erfc
807+
-----------+----------------------+---------------------
808+
-Infinity | -1 | 2
809+
-28 | -1 | 2
810+
-6 | -1 | 2
811+
-3.4 | -0.99999847800664 | 1.9999984780066
812+
-2.1 | -0.99702053334367 | 1.9970205333437
813+
-1.1 | -0.88020506957408 | 1.8802050695741
814+
-0.45 | -0.47548171978692 | 1.4754817197869
815+
-1.2e-09 | -1.3540550005146e-09 | 1.0000000013541
816+
-2.3e-13 | -2.5952720843197e-13 | 1.0000000000003
817+
-1.2e-17 | -1.3540550005146e-17 | 1
818+
0 | 0 | 1
819+
1.2e-17 | 1.3540550005146e-17 | 1
820+
2.3e-13 | 2.5952720843197e-13 | 0.99999999999974
821+
1.2e-09 | 1.3540550005146e-09 | 0.99999999864595
822+
0.45 | 0.47548171978692 | 0.52451828021308
823+
1.1 | 0.88020506957408 | 0.11979493042592
824+
2.1 | 0.99702053334367 | 0.002979466656333
825+
3.4 | 0.99999847800664 | 1.5219933628623e-06
826+
6 | 1 | 2.1519736712499e-17
827+
28 | 1 | 0
828+
Infinity | 1 | 0
829+
NaN | NaN | NaN
830+
(22 rows)
831+
793832
RESET extra_float_digits;
794833
-- test for over- and underflow
795834
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');

‎src/test/regress/expected/random.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/random.out
+32Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,38 @@ GROUP BY r;
8888
-10 | 100
8989
(1 row)
9090

91+
-- Check standard normal distribution using the Kolmogorov-Smirnov test.
92+
CREATE FUNCTION ks_test_normal_random()
93+
RETURNS boolean AS
94+
$$
95+
DECLARE
96+
n int := 1000; -- Number of samples
97+
c float8 := 1.94947; -- Critical value for 99.9% confidence
98+
ok boolean;
99+
BEGIN
100+
ok := (
101+
WITH samples AS (
102+
SELECT random_normal() r FROM generate_series(1, n) ORDER BY 1
103+
), indexed_samples AS (
104+
SELECT (row_number() OVER())-1.0 i, r FROM samples
105+
)
106+
SELECT max(abs((1+erf(r/sqrt(2)))/2 - i/n)) < c / sqrt(n)
107+
FROM indexed_samples
108+
);
109+
RETURN ok;
110+
END
111+
$$
112+
LANGUAGE plpgsql;
113+
-- As above, ks_test_normal_random() returns true about 99.9%
114+
-- of the time, so try it 3 times and accept if any test passes.
115+
SELECT ks_test_normal_random() OR
116+
ks_test_normal_random() OR
117+
ks_test_normal_random() AS standard_normal;
118+
standard_normal
119+
-----------------
120+
t
121+
(1 row)
122+
91123
-- setseed() should produce a reproducible series of random() values.
92124
SELECT setseed(0.5);
93125
setseed

‎src/test/regress/sql/float8.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/float8.sql
+14Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -229,6 +229,20 @@ SELECT atanh(float8 'infinity');
229229
SELECT atanh(float8 '-infinity');
230230
SELECT atanh(float8 'nan');
231231

232+
-- error functions
233+
-- we run these with extra_float_digits = -1, to get consistently rounded
234+
-- results on all platforms.
235+
SET extra_float_digits = -1;
236+
SELECT x,
237+
erf(x),
238+
erfc(x)
239+
FROM (VALUES (float8 '-infinity'),
240+
(-28), (-6), (-3.4), (-2.1), (-1.1), (-0.45),
241+
(-1.2e-9), (-2.3e-13), (-1.2e-17), (0),
242+
(1.2e-17), (2.3e-13), (1.2e-9),
243+
(0.45), (1.1), (2.1), (3.4), (6), (28),
244+
(float8 'infinity'), (float8 'nan')) AS t(x);
245+
232246
RESET extra_float_digits;
233247

234248
-- test for over- and underflow

‎src/test/regress/sql/random.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/random.sql
+30Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,36 @@ SELECT r, count(*)
7070
FROM (SELECT random_normal(-10, 0) r FROM generate_series(1, 100)) ss
7171
GROUP BY r;
7272

73+
-- Check standard normal distribution using the Kolmogorov-Smirnov test.
74+
75+
CREATE FUNCTION ks_test_normal_random()
76+
RETURNS boolean AS
77+
$$
78+
DECLARE
79+
n int := 1000; -- Number of samples
80+
c float8 := 1.94947; -- Critical value for 99.9% confidence
81+
ok boolean;
82+
BEGIN
83+
ok := (
84+
WITH samples AS (
85+
SELECT random_normal() r FROM generate_series(1, n) ORDER BY 1
86+
), indexed_samples AS (
87+
SELECT (row_number() OVER())-1.0 i, r FROM samples
88+
)
89+
SELECT max(abs((1+erf(r/sqrt(2)))/2 - i/n)) < c / sqrt(n)
90+
FROM indexed_samples
91+
);
92+
RETURN ok;
93+
END
94+
$$
95+
LANGUAGE plpgsql;
96+
97+
-- As above, ks_test_normal_random() returns true about 99.9%
98+
-- of the time, so try it 3 times and accept if any test passes.
99+
SELECT ks_test_normal_random() OR
100+
ks_test_normal_random() OR
101+
ks_test_normal_random() AS standard_normal;
102+
73103
-- setseed() should produce a reproducible series of random() values.
74104

75105
SELECT setseed(0.5);

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.