public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v24 5/5] doc: Add Collation Versions section.
12+ messages / 6 participants
[nested] [flat]
* [PATCH v24 5/5] doc: Add Collation Versions section.
@ 2020-03-11 02:01 Thomas Munro <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Thomas Munro @ 2020-03-11 02:01 UTC (permalink / raw)
Supply a brief introduction to collation version concepts.
Author: Thomas Munro
Reviewed-by: Julien Rouhaud
Discussion: https://postgr.es/m/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com
---
doc/src/sgml/charset.sgml | 35 +++++++++++++++++++++++++++++++++++
1 file changed, 35 insertions(+)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 4b4563c5b9..c537bdfc28 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -948,6 +948,41 @@ CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-tr
</tip>
</sect3>
</sect2>
+
+ <sect2 id="collation-versions">
+ <title>Collation Versions</title>
+
+ <para>
+ The ordering defined by a collation is not necessarily fixed over time.
+ If a collation changes for any reason, persistent data structures such as
+ b-trees that depend on a stable ordering of text might be corrupted.
+ <productname>PostgreSQL</productname> defends against this by recording
+ the current version of each referenced collation for any index that
+ depends on it in the
+ <link linkend="catalog-pg-depend"><structname>pg_depend</structname></link>
+ catalog, if the collation provider makes it available. If the provider
+ later begins to report a different version, a warning will be reported
+ when the index is accessed, until either the <xref linkend="sql-reindex"/>
+ or the <xref linkend="sql-alterindex"/> command is used to update the
+ version.
+ </para>
+ <para>
+ Version information is available for collations from the
+ <literal>icu</literal> provider on all operating systems. For the
+ <literal>libc</literal> provider, versions are currently only available
+ on systems using the GNU C library (most Linux systems).
+ </para>
+
+ <note>
+ <para>
+ When using the GNU C library for collations, the C library's version
+ is used as a proxy for the collation version. Many Linux distributions
+ change collation definitions only when upgrading the C library, but this
+ approach is imperfect as maintainers are free to back-port newer
+ collation definitions to older C library releases.
+ </para>
+ </note>
+ </sect2>
</sect1>
<sect1 id="multibyte">
--
2.20.1
--wac7ysb48OaltWcw--
^ permalink raw reply [nested|flat] 12+ messages in thread
* Functions to return random numbers in a given range
@ 2023-12-21 17:06 Dean Rasheed <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Dean Rasheed @ 2023-12-21 17:06 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
Attached is a patch that adds 3 SQL-callable functions to return
random integer/numeric values chosen uniformly from a given range:
random(min int, max int) returns int
random(min bigint, max bigint) returns bigint
random(min numeric, max numeric) returns numeric
The return value is in the range [min, max], and in the numeric case,
the result scale equals Max(scale(min), scale(max)), so it can be used
to generate large random integers, as well as decimals.
The goal is to provide simple, easy-to-use functions that operate
correctly over arbitrary ranges, which is trickier than it might seem
using the existing random() function. The main advantages are:
1. Support for arbitrary bounds (provided that max >= min). A SQL or
PL/pgSQL implementation based on the existing random() function can
suffer from integer overflow if the difference max-min is too large.
2. Uniform results over the full range. It's easy to overlook the fact
that in a naive implementation doing something like
"((max-min)*random()+min)::int", the endpoint values will be half as
likely as any other value, since casting to integer rounds to nearest.
3. Makes better use of the underlying PRNG, not limited to the 52-bits
of double precision values.
4. Simpler and more efficient generation of random numeric values.
This is something I have commonly wanted in the past, and have usually
resorted to hacks involving multiple calls to random() to build
strings of digits, which is horribly slow, and messy.
The implementation moves the existing random functions to a new source
file, so the new functions all share a common PRNG state with the
existing random functions, and that state is kept private to that
file.
Regards,
Dean
Attachments:
[text/x-patch] v1-0001-Add-random-number-in-range-functions.patch (40.2K, ../../CAEZATCV89Vxuq93xQdmc0t-0Y2zeeNQTdsjbmV7dyFBPykbV4Q@mail.gmail.com/2-v1-0001-Add-random-number-in-range-functions.patch)
download | inline diff:
From 0b7015668387c337114adb4b3c24fe1d8053bf9c Mon Sep 17 00:00:00 2001
From: Dean Rasheed <[email protected]>
Date: Fri, 25 Aug 2023 10:42:38 +0100
Subject: [PATCH v1] Add random-number-in-range functions.
This adds 3 functions:
random(min int, max int) returns int
random(min bigint, max bigint) returns bigint
random(min numeric, max numeric) returns numeric
Each returns a random number in the range [min, max].
In the numeric case, the result scale is Max(scale(min), scale(max)).
---
doc/src/sgml/func.sgml | 39 ++-
src/backend/utils/adt/Makefile | 1 +
src/backend/utils/adt/float.c | 95 ------
src/backend/utils/adt/meson.build | 1 +
src/backend/utils/adt/numeric.c | 219 +++++++++++++
src/backend/utils/adt/pseudorandomfuncs.c | 185 +++++++++++
src/common/pg_prng.c | 36 +++
src/include/catalog/pg_proc.dat | 12 +
src/include/common/pg_prng.h | 1 +
src/include/utils/numeric.h | 4 +
src/test/regress/expected/random.out | 360 ++++++++++++++++++++++
src/test/regress/sql/random.sql | 164 ++++++++++
12 files changed, 1017 insertions(+), 100 deletions(-)
create mode 100644 src/backend/utils/adt/pseudorandomfuncs.c
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 20da3ed033..b0b65d81dc 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1862,6 +1862,36 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>random</primary>
+ </indexterm>
+ <function>random</function> ( <parameter>min</parameter> <type>integer</type>, <parameter>max</parameter> <type>integer</type> )
+ <returnvalue>integer</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>bigint</type>, <parameter>max</parameter> <type>bigint</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>numeric</type>, <parameter>max</parameter> <type>numeric</type> )
+ <returnvalue>numeric</returnvalue>
+ </para>
+ <para>
+ Return a random value in the range
+ <parameter>min</parameter> <= x <= <parameter>max</parameter>.
+ </para>
+ <para>
+ <literal>random(1, 10)</literal>
+ <returnvalue>7</returnvalue>
+ </para>
+ <para>
+ <literal>random(-0.499, 0.499)</literal>
+ <returnvalue>0.347</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -1906,19 +1936,18 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</table>
<para>
- The <function>random()</function> function uses a deterministic
- pseudo-random number generator.
+ The random functions listed in <xref linkend="functions-math-random-table"/>
+ use a deterministic pseudo-random number generator.
It is fast but not suitable for cryptographic
applications; see the <xref linkend="pgcrypto"/> module for a more
secure alternative.
If <function>setseed()</function> is called, the series of results of
- subsequent <function>random()</function> calls in the current session
+ subsequent calls to these random functions in the current session
can be repeated by re-issuing <function>setseed()</function> with the same
argument.
Without any prior <function>setseed()</function> call in the same
- session, the first <function>random()</function> call obtains a seed
+ session, the first call to any of these random functions obtains a seed
from a platform-dependent source of random bits.
- These remarks hold equally for <function>random_normal()</function>.
</para>
<para>
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 199eae525d..610ccf2f79 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -82,6 +82,7 @@ OBJS = \
pg_lsn.o \
pg_upgrade_support.o \
pgstatfuncs.o \
+ pseudorandomfuncs.o \
pseudotypes.o \
quote.o \
rangetypes.o \
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index dfa90a04fb..635f1e228c 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -21,10 +21,8 @@
#include "catalog/pg_type.h"
#include "common/int.h"
-#include "common/pg_prng.h"
#include "common/shortest_dec.h"
#include "libpq/pqformat.h"
-#include "miscadmin.h"
#include "utils/array.h"
#include "utils/float.h"
#include "utils/fmgrprotos.h"
@@ -64,10 +62,6 @@ float8 degree_c_sixty = 60.0;
float8 degree_c_one_half = 0.5;
float8 degree_c_one = 1.0;
-/* State for drandom() and setseed() */
-static bool drandom_seed_set = false;
-static pg_prng_state drandom_seed;
-
/* Local function prototypes */
static double sind_q1(double x);
static double cosd_q1(double x);
@@ -2785,95 +2779,6 @@ derfc(PG_FUNCTION_ARGS)
}
-/* ========== RANDOM FUNCTIONS ========== */
-
-
-/*
- * initialize_drandom_seed - initialize drandom_seed if not yet done
- */
-static void
-initialize_drandom_seed(void)
-{
- /* Initialize random seed, if not done yet in this process */
- if (unlikely(!drandom_seed_set))
- {
- /*
- * If possible, initialize the seed using high-quality random bits.
- * Should that fail for some reason, we fall back on a lower-quality
- * seed based on current time and PID.
- */
- if (unlikely(!pg_prng_strong_seed(&drandom_seed)))
- {
- TimestampTz now = GetCurrentTimestamp();
- uint64 iseed;
-
- /* Mix the PID with the most predictable bits of the timestamp */
- iseed = (uint64) now ^ ((uint64) MyProcPid << 32);
- pg_prng_seed(&drandom_seed, iseed);
- }
- drandom_seed_set = true;
- }
-}
-
-/*
- * drandom - returns a random number
- */
-Datum
-drandom(PG_FUNCTION_ARGS)
-{
- float8 result;
-
- initialize_drandom_seed();
-
- /* pg_prng_double produces desired result range [0.0 - 1.0) */
- result = pg_prng_double(&drandom_seed);
-
- PG_RETURN_FLOAT8(result);
-}
-
-/*
- * drandom_normal - returns a random number from a normal distribution
- */
-Datum
-drandom_normal(PG_FUNCTION_ARGS)
-{
- float8 mean = PG_GETARG_FLOAT8(0);
- float8 stddev = PG_GETARG_FLOAT8(1);
- float8 result,
- z;
-
- initialize_drandom_seed();
-
- /* Get random value from standard normal(mean = 0.0, stddev = 1.0) */
- z = pg_prng_double_normal(&drandom_seed);
- /* Transform the normal standard variable (z) */
- /* using the target normal distribution parameters */
- result = (stddev * z) + mean;
-
- PG_RETURN_FLOAT8(result);
-}
-
-/*
- * setseed - set seed for the random number generator
- */
-Datum
-setseed(PG_FUNCTION_ARGS)
-{
- float8 seed = PG_GETARG_FLOAT8(0);
-
- if (seed < -1 || seed > 1 || isnan(seed))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("setseed parameter %g is out of allowed range [-1,1]",
- seed)));
-
- pg_prng_fseed(&drandom_seed, seed);
- drandom_seed_set = true;
-
- PG_RETURN_VOID();
-}
-
-
/*
* =========================
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index 8515cd9365..68c87fd50b 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -69,6 +69,7 @@ backend_sources += files(
'pg_lsn.c',
'pg_upgrade_support.c',
'pgstatfuncs.c',
+ 'pseudorandomfuncs.c',
'pseudotypes.c',
'quote.c',
'rangetypes.c',
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index bf61fd7dbc..6cfca64ecb 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -584,6 +584,8 @@ static void power_var(const NumericVar *base, const NumericVar *exp,
static void power_var_int(const NumericVar *base, int exp, int exp_dscale,
NumericVar *result);
static void power_ten_int(int exp, NumericVar *result);
+static void random_var(pg_prng_state *state, const NumericVar *rmin,
+ const NumericVar *rmax, NumericVar *result);
static int cmp_abs(const NumericVar *var1, const NumericVar *var2);
static int cmp_abs_common(const NumericDigit *var1digits, int var1ndigits,
@@ -4220,6 +4222,56 @@ numeric_trim_scale(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(res);
}
+/*
+ * Return a random numeric value in the range [rmin, rmax].
+ */
+Numeric
+random_numeric(pg_prng_state *state, Numeric rmin, Numeric rmax)
+{
+ NumericVar rmin_var;
+ NumericVar rmax_var;
+ NumericVar result;
+ Numeric res;
+
+ /* Range bounds must not be NaN/infinity */
+ if (NUMERIC_IS_SPECIAL(rmin))
+ {
+ if (NUMERIC_IS_NAN(rmin))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound cannot be NaN"));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound cannot be infinity"));
+ }
+ if (NUMERIC_IS_SPECIAL(rmax))
+ {
+ if (NUMERIC_IS_NAN(rmax))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("upper bound cannot be NaN"));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("upper bound cannot be infinity"));
+ }
+
+ /* Return a random value in the range [rmin, rmax] */
+ init_var_from_num(rmin, &rmin_var);
+ init_var_from_num(rmax, &rmax_var);
+
+ init_var(&result);
+
+ random_var(state, &rmin_var, &rmax_var, &result);
+
+ res = make_result(&result);
+
+ free_var(&result);
+
+ return res;
+}
+
/* ----------------------------------------------------------------------
*
@@ -11236,6 +11288,173 @@ power_ten_int(int exp, NumericVar *result)
result->digits[0] *= 10;
}
+/*
+ * random_var() - return a random value in the range [rmin, rmax].
+ */
+static void
+random_var(pg_prng_state *state, const NumericVar *rmin,
+ const NumericVar *rmax, NumericVar *result)
+{
+ int rscale;
+ NumericVar rlen;
+ int res_ndigits;
+ int n;
+ int pow10;
+ int i;
+ uint64 rlen64;
+ int rlen64_ndigits;
+
+ rscale = Max(rmin->dscale, rmax->dscale);
+
+ /* Compute rlen = rmax - rmin and check the range bounds */
+ init_var(&rlen);
+ sub_var(rmax, rmin, &rlen);
+
+ if (rlen.sign == NUMERIC_NEG)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ /* Special case for an empty range */
+ if (rlen.ndigits == 0)
+ {
+ set_var_from_var(rmin, result);
+ result->dscale = rscale;
+ free_var(&rlen);
+ return;
+ }
+
+ /*
+ * Otherwise, select a random value in the range [0, rlen = rmax - rmin],
+ * and shift it to the required range by adding rmin.
+ */
+
+ /* Required result digits */
+ res_ndigits = rlen.weight + 1 + (rscale + DEC_DIGITS - 1) / DEC_DIGITS;
+
+ /*
+ * To get the required rscale, the final result digit must be a multiple
+ * of pow10 = 10^n, where n = (-rscale) mod DEC_DIGITS.
+ */
+ n = ((rscale + DEC_DIGITS - 1) / DEC_DIGITS) * DEC_DIGITS - rscale;
+ pow10 = 1;
+ for (i = 0; i < n; i++)
+ pow10 *= 10;
+
+ /*
+ * To choose a random value uniformly from the range [0, rlen], we choose
+ * from the slightly larger range [0, rlen2], where rlen2 is formed from
+ * rlen by copying the first 4 NBASE digits, and setting all remaining
+ * decimal digits to "9".
+ *
+ * Without loss of generality, we can ignore the weight of rlen2 and treat
+ * it as a pure integer for the purposes of this discussion. The process
+ * above gives rlen2 + 1 = rlen64 * 10^N, for some integer N, where rlen64
+ * is a 64-bit integer formed from the first 4 NBASE digits copied from
+ * rlen. Since this trivially factors into smaller pieces that fit in
+ * 64-bit integers, the task of choosing a random value uniformly from the
+ * rlen2 + 1 possible values in [0, rlen2] is much simpler.
+ *
+ * If the random value selected is too large, it is rejected, and we try
+ * again until we get a result <= rlen, ensuring that the overall result
+ * is uniform (no particular value is any more likely than any other).
+ *
+ * Since rlen64 holds 4 NBASE digits from rlen, it contains at least
+ * DEC_DIGITS * 3 + 1 decimal digits (i.e., at least 13 decimal digits,
+ * when DEC_DIGITS is 4). Therefore the probability of needing to reject
+ * the value chosen and retry is less than 1e-13.
+ */
+ rlen64 = (uint64) rlen.digits[0];
+ rlen64_ndigits = 1;
+ while (rlen64_ndigits < res_ndigits && rlen64_ndigits < 4)
+ {
+ rlen64 *= NBASE;
+ if (rlen64_ndigits < rlen.ndigits)
+ rlen64 += rlen.digits[rlen64_ndigits];
+ rlen64_ndigits++;
+ }
+
+ /* Loop until we get a result <= rlen */
+ do
+ {
+ NumericDigit *res_digits;
+ uint64 rand;
+ int whole_ndigits;
+
+ alloc_var(result, res_ndigits);
+ result->sign = NUMERIC_POS;
+ result->weight = rlen.weight;
+ result->dscale = rscale;
+ res_digits = result->digits;
+
+ /*
+ * Set the first rlen64_ndigits using a random value in [0, rlen64].
+ *
+ * If this is the whole result, and rscale is not a multiple of
+ * DEC_DIGITS (pow10 from above is not 1), then we need this to be a
+ * multiple of pow10.
+ */
+ if (rlen64_ndigits == res_ndigits && pow10 != 1)
+ rand = pg_prng_uint64_range(state, 0, rlen64 / pow10) * pow10;
+ else
+ rand = pg_prng_uint64_range(state, 0, rlen64);
+
+ for (i = rlen64_ndigits - 1; i >= 0; i--)
+ {
+ res_digits[i] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ }
+
+ /*
+ * Set the remaining digits to random values in range [0, NBASE),
+ * noting that the last digit needs to be a multiple of pow10.
+ */
+ whole_ndigits = res_ndigits;
+ if (pow10 != 1)
+ whole_ndigits--;
+
+ /* Set whole digits in groups of 4 for best performance */
+ i = rlen64_ndigits;
+ while (i < whole_ndigits - 3)
+ {
+ rand = pg_prng_uint64_range(state, 0,
+ (uint64) NBASE * NBASE * NBASE * NBASE - 1);
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) rand;
+ }
+
+ /* Remaining whole digits */
+ while (i < whole_ndigits)
+ {
+ rand = pg_prng_uint64_range(state, 0, NBASE - 1);
+ res_digits[i++] = (NumericDigit) rand;
+ }
+
+ /* Final partial digit (multiple of pow10) */
+ if (i < res_ndigits)
+ {
+ rand = pg_prng_uint64_range(state, 0, NBASE / pow10 - 1) * pow10;
+ res_digits[i] = (NumericDigit) rand;
+ }
+
+ /* Remove leading/trailing zeroes */
+ strip_var(result);
+
+ /* If result > rlen, try again */
+
+ } while (cmp_var(result, &rlen) > 0);
+
+ /* Offset the result to the required range */
+ add_var(result, rmin, result);
+
+ free_var(&rlen);
+}
+
/* ----------------------------------------------------------------------
*
diff --git a/src/backend/utils/adt/pseudorandomfuncs.c b/src/backend/utils/adt/pseudorandomfuncs.c
new file mode 100644
index 0000000000..a064eb6c1a
--- /dev/null
+++ b/src/backend/utils/adt/pseudorandomfuncs.c
@@ -0,0 +1,185 @@
+/*-------------------------------------------------------------------------
+ *
+ * pseudorandomfuncs.c
+ * Functions giving SQL access to a pseudorandom number generator.
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/pseudorandomfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <math.h>
+
+#include "common/pg_prng.h"
+#include "miscadmin.h"
+#include "utils/fmgrprotos.h"
+#include "utils/numeric.h"
+#include "utils/timestamp.h"
+
+/* Shared PRNG state used by all the random functions */
+static pg_prng_state prng_state;
+static bool prng_seed_set = false;
+
+/*
+ * initialize_prng() -
+ *
+ * Initialize (seed) the PRNG, if not done yet in this process.
+ */
+static void
+initialize_prng(void)
+{
+ if (unlikely(!prng_seed_set))
+ {
+ /*
+ * If possible, seed the PRNG using high-quality random bits. Should
+ * that fail for some reason, we fall back on a lower-quality seed
+ * based on current time and PID.
+ */
+ if (unlikely(!pg_prng_strong_seed(&prng_state)))
+ {
+ TimestampTz now = GetCurrentTimestamp();
+ uint64 iseed;
+
+ /* Mix the PID with the most predictable bits of the timestamp */
+ iseed = (uint64) now ^ ((uint64) MyProcPid << 32);
+ pg_prng_seed(&prng_state, iseed);
+ }
+ prng_seed_set = true;
+ }
+}
+
+/*
+ * setseed() -
+ *
+ * Seed the PRNG from a specified value in the range [-1.0, 1.0].
+ */
+Datum
+setseed(PG_FUNCTION_ARGS)
+{
+ float8 seed = PG_GETARG_FLOAT8(0);
+
+ if (seed < -1 || seed > 1 || isnan(seed))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("setseed parameter %g is out of allowed range [-1,1]",
+ seed));
+
+ pg_prng_fseed(&prng_state, seed);
+ prng_seed_set = true;
+
+ PG_RETURN_VOID();
+}
+
+/*
+ * drandom() -
+ *
+ * Returns a random number chosen uniformly in the range [0.0, 1.0).
+ */
+Datum
+drandom(PG_FUNCTION_ARGS)
+{
+ float8 result;
+
+ initialize_prng();
+
+ /* pg_prng_double produces desired result range [0.0, 1.0) */
+ result = pg_prng_double(&prng_state);
+
+ PG_RETURN_FLOAT8(result);
+}
+
+/*
+ * drandom_normal() -
+ *
+ * Returns a random number from a normal distribution.
+ */
+Datum
+drandom_normal(PG_FUNCTION_ARGS)
+{
+ float8 mean = PG_GETARG_FLOAT8(0);
+ float8 stddev = PG_GETARG_FLOAT8(1);
+ float8 result,
+ z;
+
+ initialize_prng();
+
+ /* Get random value from standard normal(mean = 0.0, stddev = 1.0) */
+ z = pg_prng_double_normal(&prng_state);
+ /* Transform the normal standard variable (z) */
+ /* using the target normal distribution parameters */
+ result = (stddev * z) + mean;
+
+ PG_RETURN_FLOAT8(result);
+}
+
+/*
+ * int4random() -
+ *
+ * Returns a random 32-bit integer chosen uniformly in the specified range.
+ */
+Datum
+int4random(PG_FUNCTION_ARGS)
+{
+ int32 rmin = PG_GETARG_INT32(0);
+ int32 rmax = PG_GETARG_INT32(1);
+ int32 result;
+
+ if (rmin > rmax)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ initialize_prng();
+
+ result = (int32) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_INT32(result);
+}
+
+/*
+ * int8random() -
+ *
+ * Returns a random 64-bit integer chosen uniformly in the specified range.
+ */
+Datum
+int8random(PG_FUNCTION_ARGS)
+{
+ int64 rmin = PG_GETARG_INT64(0);
+ int64 rmax = PG_GETARG_INT64(1);
+ int64 result;
+
+ if (rmin > rmax)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ initialize_prng();
+
+ result = pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_INT64(result);
+}
+
+/*
+ * numeric_random() -
+ *
+ * Returns a random numeric value chosen uniformly in the specified range.
+ */
+Datum
+numeric_random(PG_FUNCTION_ARGS)
+{
+ Numeric rmin = PG_GETARG_NUMERIC(0);
+ Numeric rmax = PG_GETARG_NUMERIC(1);
+ Numeric result;
+
+ initialize_prng();
+
+ result = random_numeric(&prng_state, rmin, rmax);
+
+ PG_RETURN_NUMERIC(result);
+}
diff --git a/src/common/pg_prng.c b/src/common/pg_prng.c
index c7bb92ede3..7321914fee 100644
--- a/src/common/pg_prng.c
+++ b/src/common/pg_prng.c
@@ -184,6 +184,42 @@ pg_prng_int64p(pg_prng_state *state)
return (int64) (xoroshiro128ss(state) & UINT64CONST(0x7FFFFFFFFFFFFFFF));
}
+/*
+ * Select a random int64 uniformly from the range [rmin, rmax].
+ * If the range is empty, rmin is always produced.
+ */
+int64
+pg_prng_int64_range(pg_prng_state *state, int64 rmin, int64 rmax)
+{
+ int64 val;
+
+ if (likely(rmax > rmin))
+ {
+ uint64 uval;
+
+ /*
+ * Use pg_prng_uint64_range(). Can't simply pass it rmin and rmax,
+ * since (uint64) rmin will be larger than (uint64) rmax if rmin < 0.
+ */
+ uval = (uint64) rmin +
+ pg_prng_uint64_range(state, 0, (uint64) rmax - (uint64) rmin);
+
+ /*
+ * Safely convert back to int64, avoiding implementation-defined
+ * behavior for values larger than PG_INT64_MAX. Modern compilers
+ * will reduce this to a simple assignment.
+ */
+ if (uval > PG_INT64_MAX)
+ val = (int64) (uval - PG_INT64_MIN) + PG_INT64_MIN;
+ else
+ val = (int64) uval;
+ }
+ else
+ val = rmin;
+
+ return val;
+}
+
/*
* Select a random uint32 uniformly from the range [0, PG_UINT32_MAX].
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 916c8ec8d0..a2d1239c5b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3381,6 +3381,18 @@
proname => 'random_normal', provolatile => 'v', proparallel => 'r',
prorettype => 'float8', proargtypes => 'float8 float8',
prosrc => 'drandom_normal' },
+{ oid => '9719', descr => 'random integer in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'int4', proargtypes => 'int4 int4',
+ proargnames => '{min,max}', prosrc => 'int4random' },
+{ oid => '9720', descr => 'random bigint in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'int8', proargtypes => 'int8 int8',
+ proargnames => '{min,max}', prosrc => 'int8random' },
+{ oid => '9721', descr => 'random numeric in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'numeric', proargtypes => 'numeric numeric',
+ proargnames => '{min,max}', prosrc => 'numeric_random' },
{ oid => '1599', descr => 'set random seed',
proname => 'setseed', provolatile => 'v', proparallel => 'r',
prorettype => 'void', proargtypes => 'float8', prosrc => 'setseed' },
diff --git a/src/include/common/pg_prng.h b/src/include/common/pg_prng.h
index b5c0b8d288..371183449e 100644
--- a/src/include/common/pg_prng.h
+++ b/src/include/common/pg_prng.h
@@ -51,6 +51,7 @@ extern uint64 pg_prng_uint64(pg_prng_state *state);
extern uint64 pg_prng_uint64_range(pg_prng_state *state, uint64 rmin, uint64 rmax);
extern int64 pg_prng_int64(pg_prng_state *state);
extern int64 pg_prng_int64p(pg_prng_state *state);
+extern int64 pg_prng_int64_range(pg_prng_state *state, int64 rmin, int64 rmax);
extern uint32 pg_prng_uint32(pg_prng_state *state);
extern int32 pg_prng_int32(pg_prng_state *state);
extern int32 pg_prng_int32p(pg_prng_state *state);
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 08e4f8c217..3152a98562 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -14,6 +14,7 @@
#ifndef _PG_NUMERIC_H_
#define _PG_NUMERIC_H_
+#include "common/pg_prng.h"
#include "fmgr.h"
/*
@@ -102,4 +103,7 @@ extern Numeric numeric_mod_opt_error(Numeric num1, Numeric num2,
bool *have_error);
extern int32 numeric_int4_opt_error(Numeric num, bool *have_error);
+extern Numeric random_numeric(pg_prng_state *state,
+ Numeric rmin, Numeric rmax);
+
#endif /* _PG_NUMERIC_H_ */
diff --git a/src/test/regress/expected/random.out b/src/test/regress/expected/random.out
index 223590720c..43cf88a363 100644
--- a/src/test/regress/expected/random.out
+++ b/src/test/regress/expected/random.out
@@ -120,6 +120,229 @@ SELECT ks_test_normal_random() OR
t
(1 row)
+-- Test random(min, max)
+-- invalid range bounds
+SELECT random(1, 0);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random(1000000000001, 1000000000000);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random(-2.0, -3.0);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('NaN'::numeric, 10);
+ERROR: lower bound cannot be NaN
+SELECT random('-Inf'::numeric, 0);
+ERROR: lower bound cannot be infinity
+SELECT random(0, 'NaN'::numeric);
+ERROR: upper bound cannot be NaN
+SELECT random(0, 'Inf'::numeric);
+ERROR: upper bound cannot be infinity
+-- empty range is OK
+SELECT random(101, 101);
+ random
+--------
+ 101
+(1 row)
+
+SELECT random(1000000000001, 1000000000001);
+ random
+---------------
+ 1000000000001
+(1 row)
+
+SELECT random(3.14, 3.14);
+ random
+--------
+ 3.14
+(1 row)
+
+-- There should be no triple duplicates in 1000 full-range 32-bit random()
+-- values. (Each of the C(1000, 3) choices of triplets from the 1000 values
+-- has a probability of 1/(2^32)^2 of being a triple duplicate, so the
+-- average number of triple duplicates is 1000 * 999 * 998 / 6 / 2^64, which
+-- is roughly 9e-12.)
+SELECT r, count(*)
+FROM (SELECT random(-2147483648, 2147483647) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 2;
+ r | count
+---+-------
+(0 rows)
+
+-- There should be no duplicates in 1000 full-range 64-bit random() values.
+SELECT r, count(*)
+FROM (SELECT random_normal(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+ r | count
+---+-------
+(0 rows)
+
+-- There should be no duplicates in 1000 15-digit random() numeric values.
+SELECT r, count(*)
+FROM (SELECT random_normal(0, 1 - 1e-15) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+ r | count
+---+-------
+(0 rows)
+
+-- Expect at least one out of 2000 random values to be in the lowest and
+-- highest 1% of the range.
+SELECT (count(*) FILTER (WHERE r < -2104533975)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 2104533974)) > 0 AS has_large
+FROM (SELECT random(-2147483648, 2147483647) r FROM generate_series(1, 2000)) ss;
+ has_small | has_large
+-----------+-----------
+ t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1500000000 OR r > 1500000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000, 1500000000) r FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+SELECT (count(*) FILTER (WHERE r < -9038904596117680292)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 9038904596117680291)) > 0 AS has_large
+FROM (SELECT random(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 2000)) ss;
+ has_small | has_large
+-----------+-----------
+ t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1500000000000000 OR r > 1500000000000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000000000, 1500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1.5 OR r > 1.5) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1.47)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1.47)) > 0 AS has_large
+FROM (SELECT random(-1.500000000000000, 1.500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+-- Every possible value should occur at least once in 2500 random() values
+-- chosen from a range with 100 distinct values.
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-50, 49) r FROM generate_series(1, 2500));
+ min | max | count
+-----+-----+-------
+ -50 | 49 | 100
+(1 row)
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(123000000000, 123000000099) r
+ FROM generate_series(1, 2500));
+ min | max | count
+--------------+--------------+-------
+ 123000000000 | 123000000099 | 100
+(1 row)
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-0.5, 0.49) r FROM generate_series(1, 2500));
+ min | max | count
+-------+------+-------
+ -0.50 | 0.49 | 100
+(1 row)
+
+-- Check for uniform distribution using the Kolmogorov-Smirnov test.
+CREATE FUNCTION ks_test_uniform_random_int_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999) / 1000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() AS uniform_int;
+ uniform_int
+-------------
+ t
+(1 row)
+
+CREATE FUNCTION ks_test_uniform_random_bigint_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999999999) / 1000000000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() AS uniform_bigint;
+ uniform_bigint
+----------------
+ t
+(1 row)
+
+CREATE FUNCTION ks_test_uniform_random_numeric_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 0.999999) r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() AS uniform_numeric;
+ uniform_numeric
+-----------------
+ t
+(1 row)
+
-- setseed() should produce a reproducible series of random() values.
SELECT setseed(0.5);
setseed
@@ -176,3 +399,140 @@ SELECT random_normal(mean => 1, stddev => 0.1) r FROM generate_series(1, 10);
0.96403105557543
(10 rows)
+-- Reproducible random(min, max) values.
+SELECT random(1, 6) FROM generate_series(1, 10);
+ random
+--------
+ 5
+ 4
+ 5
+ 1
+ 6
+ 1
+ 1
+ 3
+ 6
+ 5
+(10 rows)
+
+SELECT random(-2147483648, 2147483647) FROM generate_series(1, 10);
+ random
+-------------
+ -84380014
+ 1287883594
+ -1927252904
+ 13516867
+ -1902961616
+ -1824286201
+ -871264469
+ -1225880415
+ 229836730
+ -116039023
+(10 rows)
+
+SELECT random(-9223372036854775808, 9223372036854775807) FROM generate_series(1, 10);
+ random
+----------------------
+ -6205280962992680052
+ -3583519428011353337
+ 511801786318122700
+ 4672737727839409655
+ -6674868801536280768
+ -7816052100626646489
+ -4340613370136007199
+ -5873174504107419786
+ -2249910101649817824
+ -4493828993910792325
+(10 rows)
+
+SELECT random(-1e30, 1e30) FROM generate_series(1, 10);
+ random
+---------------------------------
+ -732116469803315942112255539315
+ 794641423514877972798449289857
+ -576932746026123093304638334719
+ 420625067723533225139761854757
+ -339227806779403187811001078919
+ -77667951539418104959241732636
+ 239810941795708162629328071599
+ 820784371155896967052141946697
+ -377084684544126871150439048352
+ -979773225250716295007225086726
+(10 rows)
+
+SELECT random(-0.4, 0.4) FROM generate_series(1, 10);
+ random
+--------
+ 0.1
+ 0.0
+ 0.4
+ -0.2
+ 0.1
+ 0.2
+ 0.3
+ 0.0
+ -0.2
+ 0.2
+(10 rows)
+
+SELECT random(0, 1 - 1e-30) FROM generate_series(1, 10);
+ random
+----------------------------------
+ 0.676442053784930109917469287265
+ 0.221310454098356723569995592911
+ 0.060101338174419259555193956224
+ 0.509960354695248239243002172364
+ 0.248680813394555793693952296993
+ 0.353262552880008646603494668901
+ 0.760692600450339509843044233719
+ 0.554987655310094483449494782510
+ 0.330890988458592995280347745733
+ 0.665435298280470361228607881507
+(10 rows)
+
+SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20) n;
+ n | random
+-----+------------------------
+ -20 | 94174615760837282445
+ -19 | 6692559888531296894
+ -18 | 801114552709125931
+ -17 | 44091460959939971
+ -16 | 2956109297383113
+ -15 | 783332278684523
+ -14 | 81534303241440
+ -13 | 2892623140500
+ -12 | 269397605141
+ -11 | 13027512296
+ -10 | 9178377775
+ -9 | 323534150
+ -8 | 91897803
+ -7 | 6091383
+ -6 | 13174
+ -5 | 92714
+ -4 | 8079
+ -3 | 429
+ -2 | 30
+ -1 | 3
+ 0 | 0
+ 1 | 0.1
+ 2 | 0.69
+ 3 | 0.492
+ 4 | 0.7380
+ 5 | 0.77078
+ 6 | 0.738142
+ 7 | 0.1808815
+ 8 | 0.14908933
+ 9 | 0.222654042
+ 10 | 0.2281295170
+ 11 | 0.73655782966
+ 12 | 0.056357256884
+ 13 | 0.8998407524375
+ 14 | 0.28198400530206
+ 15 | 0.713478222805230
+ 16 | 0.0415046850936909
+ 17 | 0.45946350291315119
+ 18 | 0.310966980367873753
+ 19 | 0.4967623661709676512
+ 20 | 0.60795101234744211935
+(41 rows)
+
diff --git a/src/test/regress/sql/random.sql b/src/test/regress/sql/random.sql
index 14cc76bc3c..ebfa7539ed 100644
--- a/src/test/regress/sql/random.sql
+++ b/src/test/regress/sql/random.sql
@@ -100,6 +100,161 @@ SELECT ks_test_normal_random() OR
ks_test_normal_random() OR
ks_test_normal_random() AS standard_normal;
+-- Test random(min, max)
+
+-- invalid range bounds
+SELECT random(1, 0);
+SELECT random(1000000000001, 1000000000000);
+SELECT random(-2.0, -3.0);
+SELECT random('NaN'::numeric, 10);
+SELECT random('-Inf'::numeric, 0);
+SELECT random(0, 'NaN'::numeric);
+SELECT random(0, 'Inf'::numeric);
+
+-- empty range is OK
+SELECT random(101, 101);
+SELECT random(1000000000001, 1000000000001);
+SELECT random(3.14, 3.14);
+
+-- There should be no triple duplicates in 1000 full-range 32-bit random()
+-- values. (Each of the C(1000, 3) choices of triplets from the 1000 values
+-- has a probability of 1/(2^32)^2 of being a triple duplicate, so the
+-- average number of triple duplicates is 1000 * 999 * 998 / 6 / 2^64, which
+-- is roughly 9e-12.)
+SELECT r, count(*)
+FROM (SELECT random(-2147483648, 2147483647) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 2;
+
+-- There should be no duplicates in 1000 full-range 64-bit random() values.
+SELECT r, count(*)
+FROM (SELECT random_normal(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+
+-- There should be no duplicates in 1000 15-digit random() numeric values.
+SELECT r, count(*)
+FROM (SELECT random_normal(0, 1 - 1e-15) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+
+-- Expect at least one out of 2000 random values to be in the lowest and
+-- highest 1% of the range.
+SELECT (count(*) FILTER (WHERE r < -2104533975)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 2104533974)) > 0 AS has_large
+FROM (SELECT random(-2147483648, 2147483647) r FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1500000000 OR r > 1500000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000, 1500000000) r FROM generate_series(1, 2000)) ss;
+
+SELECT (count(*) FILTER (WHERE r < -9038904596117680292)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 9038904596117680291)) > 0 AS has_large
+FROM (SELECT random(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1500000000000000 OR r > 1500000000000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000000000, 1500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1.5 OR r > 1.5) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1.47)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1.47)) > 0 AS has_large
+FROM (SELECT random(-1.500000000000000, 1.500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+
+-- Every possible value should occur at least once in 2500 random() values
+-- chosen from a range with 100 distinct values.
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-50, 49) r FROM generate_series(1, 2500));
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(123000000000, 123000000099) r
+ FROM generate_series(1, 2500));
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-0.5, 0.49) r FROM generate_series(1, 2500));
+
+-- Check for uniform distribution using the Kolmogorov-Smirnov test.
+
+CREATE FUNCTION ks_test_uniform_random_int_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999) / 1000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() AS uniform_int;
+
+CREATE FUNCTION ks_test_uniform_random_bigint_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999999999) / 1000000000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() AS uniform_bigint;
+
+CREATE FUNCTION ks_test_uniform_random_numeric_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 0.999999) r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() AS uniform_numeric;
+
-- setseed() should produce a reproducible series of random() values.
SELECT setseed(0.5);
@@ -113,3 +268,12 @@ SET extra_float_digits = -1;
SELECT random_normal() FROM generate_series(1, 10);
SELECT random_normal(mean => 1, stddev => 0.1) r FROM generate_series(1, 10);
+
+-- Reproducible random(min, max) values.
+SELECT random(1, 6) FROM generate_series(1, 10);
+SELECT random(-2147483648, 2147483647) FROM generate_series(1, 10);
+SELECT random(-9223372036854775808, 9223372036854775807) FROM generate_series(1, 10);
+SELECT random(-1e30, 1e30) FROM generate_series(1, 10);
+SELECT random(-0.4, 0.4) FROM generate_series(1, 10);
+SELECT random(0, 1 - 1e-30) FROM generate_series(1, 10);
+SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20) n;
--
2.35.3
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2023-12-21 17:43 Pavel Stehule <[email protected]>
parent: Dean Rasheed <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Pavel Stehule @ 2023-12-21 17:43 UTC (permalink / raw)
To: Dean Rasheed <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi
čt 21. 12. 2023 v 18:06 odesílatel Dean Rasheed <[email protected]>
napsal:
> Attached is a patch that adds 3 SQL-callable functions to return
> random integer/numeric values chosen uniformly from a given range:
>
> random(min int, max int) returns int
> random(min bigint, max bigint) returns bigint
> random(min numeric, max numeric) returns numeric
>
The return value is in the range [min, max], and in the numeric case,
> the result scale equals Max(scale(min), scale(max)), so it can be used
> to generate large random integers, as well as decimals.
>
> The goal is to provide simple, easy-to-use functions that operate
> correctly over arbitrary ranges, which is trickier than it might seem
> using the existing random() function. The main advantages are:
>
> 1. Support for arbitrary bounds (provided that max >= min). A SQL or
> PL/pgSQL implementation based on the existing random() function can
> suffer from integer overflow if the difference max-min is too large.
>
> 2. Uniform results over the full range. It's easy to overlook the fact
> that in a naive implementation doing something like
> "((max-min)*random()+min)::int", the endpoint values will be half as
> likely as any other value, since casting to integer rounds to nearest.
>
> 3. Makes better use of the underlying PRNG, not limited to the 52-bits
> of double precision values.
>
> 4. Simpler and more efficient generation of random numeric values.
> This is something I have commonly wanted in the past, and have usually
> resorted to hacks involving multiple calls to random() to build
> strings of digits, which is horribly slow, and messy.
>
> The implementation moves the existing random functions to a new source
> file, so the new functions all share a common PRNG state with the
> existing random functions, and that state is kept private to that
> file.
>
+1
Regards
Pavel
> Regards,
> Dean
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-01-26 20:44 David Zhang <[email protected]>
parent: Pavel Stehule <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: David Zhang @ 2024-01-26 20:44 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; Dean Rasheed <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Thank you for the patch.
I applied this patch manually to the master branch, resolving a conflict
in `numeric.h`. It successfully passed both `make check` and `make
check-world`.
Best regards,
David
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-01-29 12:38 Dean Rasheed <[email protected]>
parent: Dean Rasheed <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Dean Rasheed @ 2024-01-29 12:38 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Thu, 28 Dec 2023 at 07:34, jian he <[email protected]> wrote:
>
> Your patch works.
> performance is the best amount for other options in [0].
> I don't have deep knowledge about which one is more random.
>
Thanks for testing.
> Currently we have to explicitly mention the lower and upper bound.
> but can we do this:
> just give me an int, int means the int data type can be represented.
> or just give me a random bigint.
> but for numeric, the full numeric values that can be represented are very big.
>
> Maybe we can use the special value null to achieve this
> like use
> select random(NULL::int,null)
> to represent a random int in the full range of integers values can be
> represented.
>
Hmm, I don't particularly like that idea. It seems pretty ugly. Now
that we support literal integers in hex, with underscores, it's
relatively easy to pass INT_MIN/MAX as arguments to these functions,
if that's what you need. I think if we were going to have a shorthand
for getting full-range random integers, it would probably be better to
introduce separate no-arg functions for that. I'm not really sure if
that's a sufficiently common use case to justify the effort though.
Regards,
Dean
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-01-29 12:42 Dean Rasheed <[email protected]>
parent: David Zhang <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Dean Rasheed @ 2024-01-29 12:42 UTC (permalink / raw)
To: David Zhang <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri, 26 Jan 2024 at 20:44, David Zhang <[email protected]> wrote:
>
> Thank you for the patch.
>
> I applied this patch manually to the master branch, resolving a conflict
> in `numeric.h`. It successfully passed both `make check` and `make
> check-world`.
>
Thanks for testing.
Interestingly, the cfbot didn't pick up on the fact that it needed
rebasing. Anyway, the copyright years in the new file's header comment
needed updating, so here is a rebase doing that.
Regards,
Dean
Attachments:
[text/x-patch] v2-0001-Add-random-number-in-range-functions.patch (40.2K, ../../CAEZATCWAL9X=6T94=C9hW3RPMVqh9EPHAYTLAL_CebBk2jgwtw@mail.gmail.com/2-v2-0001-Add-random-number-in-range-functions.patch)
download | inline diff:
From 15d0ba981ff03eca7143726fe7512adf00ee3a84 Mon Sep 17 00:00:00 2001
From: Dean Rasheed <[email protected]>
Date: Fri, 25 Aug 2023 10:42:38 +0100
Subject: [PATCH v2] Add random-number-in-range functions.
This adds 3 functions:
random(min int, max int) returns int
random(min bigint, max bigint) returns bigint
random(min numeric, max numeric) returns numeric
Each returns a random number in the range [min, max].
In the numeric case, the result scale is Max(scale(min), scale(max)).
---
doc/src/sgml/func.sgml | 39 ++-
src/backend/utils/adt/Makefile | 1 +
src/backend/utils/adt/float.c | 95 ------
src/backend/utils/adt/meson.build | 1 +
src/backend/utils/adt/numeric.c | 219 +++++++++++++
src/backend/utils/adt/pseudorandomfuncs.c | 185 +++++++++++
src/common/pg_prng.c | 36 +++
src/include/catalog/pg_proc.dat | 12 +
src/include/common/pg_prng.h | 1 +
src/include/utils/numeric.h | 4 +
src/test/regress/expected/random.out | 360 ++++++++++++++++++++++
src/test/regress/sql/random.sql | 164 ++++++++++
12 files changed, 1017 insertions(+), 100 deletions(-)
create mode 100644 src/backend/utils/adt/pseudorandomfuncs.c
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8ef4..6d76fb5853 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1862,6 +1862,36 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>random</primary>
+ </indexterm>
+ <function>random</function> ( <parameter>min</parameter> <type>integer</type>, <parameter>max</parameter> <type>integer</type> )
+ <returnvalue>integer</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>bigint</type>, <parameter>max</parameter> <type>bigint</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>numeric</type>, <parameter>max</parameter> <type>numeric</type> )
+ <returnvalue>numeric</returnvalue>
+ </para>
+ <para>
+ Return a random value in the range
+ <parameter>min</parameter> <= x <= <parameter>max</parameter>.
+ </para>
+ <para>
+ <literal>random(1, 10)</literal>
+ <returnvalue>7</returnvalue>
+ </para>
+ <para>
+ <literal>random(-0.499, 0.499)</literal>
+ <returnvalue>0.347</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -1906,19 +1936,18 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</table>
<para>
- The <function>random()</function> function uses a deterministic
- pseudo-random number generator.
+ The random functions listed in <xref linkend="functions-math-random-table"/>
+ use a deterministic pseudo-random number generator.
It is fast but not suitable for cryptographic
applications; see the <xref linkend="pgcrypto"/> module for a more
secure alternative.
If <function>setseed()</function> is called, the series of results of
- subsequent <function>random()</function> calls in the current session
+ subsequent calls to these random functions in the current session
can be repeated by re-issuing <function>setseed()</function> with the same
argument.
Without any prior <function>setseed()</function> call in the same
- session, the first <function>random()</function> call obtains a seed
+ session, the first call to any of these random functions obtains a seed
from a platform-dependent source of random bits.
- These remarks hold equally for <function>random_normal()</function>.
</para>
<para>
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 199eae525d..610ccf2f79 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -82,6 +82,7 @@ OBJS = \
pg_lsn.o \
pg_upgrade_support.o \
pgstatfuncs.o \
+ pseudorandomfuncs.o \
pseudotypes.o \
quote.o \
rangetypes.o \
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index 901edcc896..cbbb8aecaf 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -21,10 +21,8 @@
#include "catalog/pg_type.h"
#include "common/int.h"
-#include "common/pg_prng.h"
#include "common/shortest_dec.h"
#include "libpq/pqformat.h"
-#include "miscadmin.h"
#include "utils/array.h"
#include "utils/float.h"
#include "utils/fmgrprotos.h"
@@ -64,10 +62,6 @@ float8 degree_c_sixty = 60.0;
float8 degree_c_one_half = 0.5;
float8 degree_c_one = 1.0;
-/* State for drandom() and setseed() */
-static bool drandom_seed_set = false;
-static pg_prng_state drandom_seed;
-
/* Local function prototypes */
static double sind_q1(double x);
static double cosd_q1(double x);
@@ -2785,95 +2779,6 @@ derfc(PG_FUNCTION_ARGS)
}
-/* ========== RANDOM FUNCTIONS ========== */
-
-
-/*
- * initialize_drandom_seed - initialize drandom_seed if not yet done
- */
-static void
-initialize_drandom_seed(void)
-{
- /* Initialize random seed, if not done yet in this process */
- if (unlikely(!drandom_seed_set))
- {
- /*
- * If possible, initialize the seed using high-quality random bits.
- * Should that fail for some reason, we fall back on a lower-quality
- * seed based on current time and PID.
- */
- if (unlikely(!pg_prng_strong_seed(&drandom_seed)))
- {
- TimestampTz now = GetCurrentTimestamp();
- uint64 iseed;
-
- /* Mix the PID with the most predictable bits of the timestamp */
- iseed = (uint64) now ^ ((uint64) MyProcPid << 32);
- pg_prng_seed(&drandom_seed, iseed);
- }
- drandom_seed_set = true;
- }
-}
-
-/*
- * drandom - returns a random number
- */
-Datum
-drandom(PG_FUNCTION_ARGS)
-{
- float8 result;
-
- initialize_drandom_seed();
-
- /* pg_prng_double produces desired result range [0.0 - 1.0) */
- result = pg_prng_double(&drandom_seed);
-
- PG_RETURN_FLOAT8(result);
-}
-
-/*
- * drandom_normal - returns a random number from a normal distribution
- */
-Datum
-drandom_normal(PG_FUNCTION_ARGS)
-{
- float8 mean = PG_GETARG_FLOAT8(0);
- float8 stddev = PG_GETARG_FLOAT8(1);
- float8 result,
- z;
-
- initialize_drandom_seed();
-
- /* Get random value from standard normal(mean = 0.0, stddev = 1.0) */
- z = pg_prng_double_normal(&drandom_seed);
- /* Transform the normal standard variable (z) */
- /* using the target normal distribution parameters */
- result = (stddev * z) + mean;
-
- PG_RETURN_FLOAT8(result);
-}
-
-/*
- * setseed - set seed for the random number generator
- */
-Datum
-setseed(PG_FUNCTION_ARGS)
-{
- float8 seed = PG_GETARG_FLOAT8(0);
-
- if (seed < -1 || seed > 1 || isnan(seed))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("setseed parameter %g is out of allowed range [-1,1]",
- seed)));
-
- pg_prng_fseed(&drandom_seed, seed);
- drandom_seed_set = true;
-
- PG_RETURN_VOID();
-}
-
-
/*
* =========================
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index f3dfb52204..48dbcf59a5 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -69,6 +69,7 @@ backend_sources += files(
'pg_lsn.c',
'pg_upgrade_support.c',
'pgstatfuncs.c',
+ 'pseudorandomfuncs.c',
'pseudotypes.c',
'quote.c',
'rangetypes.c',
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 015a41dc56..9761d2e931 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -584,6 +584,8 @@ static void power_var(const NumericVar *base, const NumericVar *exp,
static void power_var_int(const NumericVar *base, int exp, int exp_dscale,
NumericVar *result);
static void power_ten_int(int exp, NumericVar *result);
+static void random_var(pg_prng_state *state, const NumericVar *rmin,
+ const NumericVar *rmax, NumericVar *result);
static int cmp_abs(const NumericVar *var1, const NumericVar *var2);
static int cmp_abs_common(const NumericDigit *var1digits, int var1ndigits,
@@ -4220,6 +4222,56 @@ numeric_trim_scale(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(res);
}
+/*
+ * Return a random numeric value in the range [rmin, rmax].
+ */
+Numeric
+random_numeric(pg_prng_state *state, Numeric rmin, Numeric rmax)
+{
+ NumericVar rmin_var;
+ NumericVar rmax_var;
+ NumericVar result;
+ Numeric res;
+
+ /* Range bounds must not be NaN/infinity */
+ if (NUMERIC_IS_SPECIAL(rmin))
+ {
+ if (NUMERIC_IS_NAN(rmin))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound cannot be NaN"));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound cannot be infinity"));
+ }
+ if (NUMERIC_IS_SPECIAL(rmax))
+ {
+ if (NUMERIC_IS_NAN(rmax))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("upper bound cannot be NaN"));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("upper bound cannot be infinity"));
+ }
+
+ /* Return a random value in the range [rmin, rmax] */
+ init_var_from_num(rmin, &rmin_var);
+ init_var_from_num(rmax, &rmax_var);
+
+ init_var(&result);
+
+ random_var(state, &rmin_var, &rmax_var, &result);
+
+ res = make_result(&result);
+
+ free_var(&result);
+
+ return res;
+}
+
/* ----------------------------------------------------------------------
*
@@ -11263,6 +11315,173 @@ power_ten_int(int exp, NumericVar *result)
result->digits[0] *= 10;
}
+/*
+ * random_var() - return a random value in the range [rmin, rmax].
+ */
+static void
+random_var(pg_prng_state *state, const NumericVar *rmin,
+ const NumericVar *rmax, NumericVar *result)
+{
+ int rscale;
+ NumericVar rlen;
+ int res_ndigits;
+ int n;
+ int pow10;
+ int i;
+ uint64 rlen64;
+ int rlen64_ndigits;
+
+ rscale = Max(rmin->dscale, rmax->dscale);
+
+ /* Compute rlen = rmax - rmin and check the range bounds */
+ init_var(&rlen);
+ sub_var(rmax, rmin, &rlen);
+
+ if (rlen.sign == NUMERIC_NEG)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ /* Special case for an empty range */
+ if (rlen.ndigits == 0)
+ {
+ set_var_from_var(rmin, result);
+ result->dscale = rscale;
+ free_var(&rlen);
+ return;
+ }
+
+ /*
+ * Otherwise, select a random value in the range [0, rlen = rmax - rmin],
+ * and shift it to the required range by adding rmin.
+ */
+
+ /* Required result digits */
+ res_ndigits = rlen.weight + 1 + (rscale + DEC_DIGITS - 1) / DEC_DIGITS;
+
+ /*
+ * To get the required rscale, the final result digit must be a multiple
+ * of pow10 = 10^n, where n = (-rscale) mod DEC_DIGITS.
+ */
+ n = ((rscale + DEC_DIGITS - 1) / DEC_DIGITS) * DEC_DIGITS - rscale;
+ pow10 = 1;
+ for (i = 0; i < n; i++)
+ pow10 *= 10;
+
+ /*
+ * To choose a random value uniformly from the range [0, rlen], we choose
+ * from the slightly larger range [0, rlen2], where rlen2 is formed from
+ * rlen by copying the first 4 NBASE digits, and setting all remaining
+ * decimal digits to "9".
+ *
+ * Without loss of generality, we can ignore the weight of rlen2 and treat
+ * it as a pure integer for the purposes of this discussion. The process
+ * above gives rlen2 + 1 = rlen64 * 10^N, for some integer N, where rlen64
+ * is a 64-bit integer formed from the first 4 NBASE digits copied from
+ * rlen. Since this trivially factors into smaller pieces that fit in
+ * 64-bit integers, the task of choosing a random value uniformly from the
+ * rlen2 + 1 possible values in [0, rlen2] is much simpler.
+ *
+ * If the random value selected is too large, it is rejected, and we try
+ * again until we get a result <= rlen, ensuring that the overall result
+ * is uniform (no particular value is any more likely than any other).
+ *
+ * Since rlen64 holds 4 NBASE digits from rlen, it contains at least
+ * DEC_DIGITS * 3 + 1 decimal digits (i.e., at least 13 decimal digits,
+ * when DEC_DIGITS is 4). Therefore the probability of needing to reject
+ * the value chosen and retry is less than 1e-13.
+ */
+ rlen64 = (uint64) rlen.digits[0];
+ rlen64_ndigits = 1;
+ while (rlen64_ndigits < res_ndigits && rlen64_ndigits < 4)
+ {
+ rlen64 *= NBASE;
+ if (rlen64_ndigits < rlen.ndigits)
+ rlen64 += rlen.digits[rlen64_ndigits];
+ rlen64_ndigits++;
+ }
+
+ /* Loop until we get a result <= rlen */
+ do
+ {
+ NumericDigit *res_digits;
+ uint64 rand;
+ int whole_ndigits;
+
+ alloc_var(result, res_ndigits);
+ result->sign = NUMERIC_POS;
+ result->weight = rlen.weight;
+ result->dscale = rscale;
+ res_digits = result->digits;
+
+ /*
+ * Set the first rlen64_ndigits using a random value in [0, rlen64].
+ *
+ * If this is the whole result, and rscale is not a multiple of
+ * DEC_DIGITS (pow10 from above is not 1), then we need this to be a
+ * multiple of pow10.
+ */
+ if (rlen64_ndigits == res_ndigits && pow10 != 1)
+ rand = pg_prng_uint64_range(state, 0, rlen64 / pow10) * pow10;
+ else
+ rand = pg_prng_uint64_range(state, 0, rlen64);
+
+ for (i = rlen64_ndigits - 1; i >= 0; i--)
+ {
+ res_digits[i] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ }
+
+ /*
+ * Set the remaining digits to random values in range [0, NBASE),
+ * noting that the last digit needs to be a multiple of pow10.
+ */
+ whole_ndigits = res_ndigits;
+ if (pow10 != 1)
+ whole_ndigits--;
+
+ /* Set whole digits in groups of 4 for best performance */
+ i = rlen64_ndigits;
+ while (i < whole_ndigits - 3)
+ {
+ rand = pg_prng_uint64_range(state, 0,
+ (uint64) NBASE * NBASE * NBASE * NBASE - 1);
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) rand;
+ }
+
+ /* Remaining whole digits */
+ while (i < whole_ndigits)
+ {
+ rand = pg_prng_uint64_range(state, 0, NBASE - 1);
+ res_digits[i++] = (NumericDigit) rand;
+ }
+
+ /* Final partial digit (multiple of pow10) */
+ if (i < res_ndigits)
+ {
+ rand = pg_prng_uint64_range(state, 0, NBASE / pow10 - 1) * pow10;
+ res_digits[i] = (NumericDigit) rand;
+ }
+
+ /* Remove leading/trailing zeroes */
+ strip_var(result);
+
+ /* If result > rlen, try again */
+
+ } while (cmp_var(result, &rlen) > 0);
+
+ /* Offset the result to the required range */
+ add_var(result, rmin, result);
+
+ free_var(&rlen);
+}
+
/* ----------------------------------------------------------------------
*
diff --git a/src/backend/utils/adt/pseudorandomfuncs.c b/src/backend/utils/adt/pseudorandomfuncs.c
new file mode 100644
index 0000000000..8e82c7078c
--- /dev/null
+++ b/src/backend/utils/adt/pseudorandomfuncs.c
@@ -0,0 +1,185 @@
+/*-------------------------------------------------------------------------
+ *
+ * pseudorandomfuncs.c
+ * Functions giving SQL access to a pseudorandom number generator.
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/pseudorandomfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <math.h>
+
+#include "common/pg_prng.h"
+#include "miscadmin.h"
+#include "utils/fmgrprotos.h"
+#include "utils/numeric.h"
+#include "utils/timestamp.h"
+
+/* Shared PRNG state used by all the random functions */
+static pg_prng_state prng_state;
+static bool prng_seed_set = false;
+
+/*
+ * initialize_prng() -
+ *
+ * Initialize (seed) the PRNG, if not done yet in this process.
+ */
+static void
+initialize_prng(void)
+{
+ if (unlikely(!prng_seed_set))
+ {
+ /*
+ * If possible, seed the PRNG using high-quality random bits. Should
+ * that fail for some reason, we fall back on a lower-quality seed
+ * based on current time and PID.
+ */
+ if (unlikely(!pg_prng_strong_seed(&prng_state)))
+ {
+ TimestampTz now = GetCurrentTimestamp();
+ uint64 iseed;
+
+ /* Mix the PID with the most predictable bits of the timestamp */
+ iseed = (uint64) now ^ ((uint64) MyProcPid << 32);
+ pg_prng_seed(&prng_state, iseed);
+ }
+ prng_seed_set = true;
+ }
+}
+
+/*
+ * setseed() -
+ *
+ * Seed the PRNG from a specified value in the range [-1.0, 1.0].
+ */
+Datum
+setseed(PG_FUNCTION_ARGS)
+{
+ float8 seed = PG_GETARG_FLOAT8(0);
+
+ if (seed < -1 || seed > 1 || isnan(seed))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("setseed parameter %g is out of allowed range [-1,1]",
+ seed));
+
+ pg_prng_fseed(&prng_state, seed);
+ prng_seed_set = true;
+
+ PG_RETURN_VOID();
+}
+
+/*
+ * drandom() -
+ *
+ * Returns a random number chosen uniformly in the range [0.0, 1.0).
+ */
+Datum
+drandom(PG_FUNCTION_ARGS)
+{
+ float8 result;
+
+ initialize_prng();
+
+ /* pg_prng_double produces desired result range [0.0, 1.0) */
+ result = pg_prng_double(&prng_state);
+
+ PG_RETURN_FLOAT8(result);
+}
+
+/*
+ * drandom_normal() -
+ *
+ * Returns a random number from a normal distribution.
+ */
+Datum
+drandom_normal(PG_FUNCTION_ARGS)
+{
+ float8 mean = PG_GETARG_FLOAT8(0);
+ float8 stddev = PG_GETARG_FLOAT8(1);
+ float8 result,
+ z;
+
+ initialize_prng();
+
+ /* Get random value from standard normal(mean = 0.0, stddev = 1.0) */
+ z = pg_prng_double_normal(&prng_state);
+ /* Transform the normal standard variable (z) */
+ /* using the target normal distribution parameters */
+ result = (stddev * z) + mean;
+
+ PG_RETURN_FLOAT8(result);
+}
+
+/*
+ * int4random() -
+ *
+ * Returns a random 32-bit integer chosen uniformly in the specified range.
+ */
+Datum
+int4random(PG_FUNCTION_ARGS)
+{
+ int32 rmin = PG_GETARG_INT32(0);
+ int32 rmax = PG_GETARG_INT32(1);
+ int32 result;
+
+ if (rmin > rmax)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ initialize_prng();
+
+ result = (int32) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_INT32(result);
+}
+
+/*
+ * int8random() -
+ *
+ * Returns a random 64-bit integer chosen uniformly in the specified range.
+ */
+Datum
+int8random(PG_FUNCTION_ARGS)
+{
+ int64 rmin = PG_GETARG_INT64(0);
+ int64 rmax = PG_GETARG_INT64(1);
+ int64 result;
+
+ if (rmin > rmax)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ initialize_prng();
+
+ result = pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_INT64(result);
+}
+
+/*
+ * numeric_random() -
+ *
+ * Returns a random numeric value chosen uniformly in the specified range.
+ */
+Datum
+numeric_random(PG_FUNCTION_ARGS)
+{
+ Numeric rmin = PG_GETARG_NUMERIC(0);
+ Numeric rmax = PG_GETARG_NUMERIC(1);
+ Numeric result;
+
+ initialize_prng();
+
+ result = random_numeric(&prng_state, rmin, rmax);
+
+ PG_RETURN_NUMERIC(result);
+}
diff --git a/src/common/pg_prng.c b/src/common/pg_prng.c
index c1714a02bd..15b39411a9 100644
--- a/src/common/pg_prng.c
+++ b/src/common/pg_prng.c
@@ -184,6 +184,42 @@ pg_prng_int64p(pg_prng_state *state)
return (int64) (xoroshiro128ss(state) & UINT64CONST(0x7FFFFFFFFFFFFFFF));
}
+/*
+ * Select a random int64 uniformly from the range [rmin, rmax].
+ * If the range is empty, rmin is always produced.
+ */
+int64
+pg_prng_int64_range(pg_prng_state *state, int64 rmin, int64 rmax)
+{
+ int64 val;
+
+ if (likely(rmax > rmin))
+ {
+ uint64 uval;
+
+ /*
+ * Use pg_prng_uint64_range(). Can't simply pass it rmin and rmax,
+ * since (uint64) rmin will be larger than (uint64) rmax if rmin < 0.
+ */
+ uval = (uint64) rmin +
+ pg_prng_uint64_range(state, 0, (uint64) rmax - (uint64) rmin);
+
+ /*
+ * Safely convert back to int64, avoiding implementation-defined
+ * behavior for values larger than PG_INT64_MAX. Modern compilers
+ * will reduce this to a simple assignment.
+ */
+ if (uval > PG_INT64_MAX)
+ val = (int64) (uval - PG_INT64_MIN) + PG_INT64_MIN;
+ else
+ val = (int64) uval;
+ }
+ else
+ val = rmin;
+
+ return val;
+}
+
/*
* Select a random uint32 uniformly from the range [0, PG_UINT32_MAX].
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 29af4ce65d..b1314d58f5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3381,6 +3381,18 @@
proname => 'random_normal', provolatile => 'v', proparallel => 'r',
prorettype => 'float8', proargtypes => 'float8 float8',
prosrc => 'drandom_normal' },
+{ oid => '9719', descr => 'random integer in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'int4', proargtypes => 'int4 int4',
+ proargnames => '{min,max}', prosrc => 'int4random' },
+{ oid => '9720', descr => 'random bigint in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'int8', proargtypes => 'int8 int8',
+ proargnames => '{min,max}', prosrc => 'int8random' },
+{ oid => '9721', descr => 'random numeric in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'numeric', proargtypes => 'numeric numeric',
+ proargnames => '{min,max}', prosrc => 'numeric_random' },
{ oid => '1599', descr => 'set random seed',
proname => 'setseed', provolatile => 'v', proparallel => 'r',
prorettype => 'void', proargtypes => 'float8', prosrc => 'setseed' },
diff --git a/src/include/common/pg_prng.h b/src/include/common/pg_prng.h
index e201b95686..c114c6419d 100644
--- a/src/include/common/pg_prng.h
+++ b/src/include/common/pg_prng.h
@@ -51,6 +51,7 @@ extern uint64 pg_prng_uint64(pg_prng_state *state);
extern uint64 pg_prng_uint64_range(pg_prng_state *state, uint64 rmin, uint64 rmax);
extern int64 pg_prng_int64(pg_prng_state *state);
extern int64 pg_prng_int64p(pg_prng_state *state);
+extern int64 pg_prng_int64_range(pg_prng_state *state, int64 rmin, int64 rmax);
extern uint32 pg_prng_uint32(pg_prng_state *state);
extern int32 pg_prng_int32(pg_prng_state *state);
extern int32 pg_prng_int32p(pg_prng_state *state);
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 2f7184e299..43c75c436f 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -14,6 +14,7 @@
#ifndef _PG_NUMERIC_H_
#define _PG_NUMERIC_H_
+#include "common/pg_prng.h"
#include "fmgr.h"
/*
@@ -103,4 +104,7 @@ extern Numeric numeric_mod_opt_error(Numeric num1, Numeric num2,
extern int32 numeric_int4_opt_error(Numeric num, bool *have_error);
extern int64 numeric_int8_opt_error(Numeric num, bool *have_error);
+extern Numeric random_numeric(pg_prng_state *state,
+ Numeric rmin, Numeric rmax);
+
#endif /* _PG_NUMERIC_H_ */
diff --git a/src/test/regress/expected/random.out b/src/test/regress/expected/random.out
index 223590720c..43cf88a363 100644
--- a/src/test/regress/expected/random.out
+++ b/src/test/regress/expected/random.out
@@ -120,6 +120,229 @@ SELECT ks_test_normal_random() OR
t
(1 row)
+-- Test random(min, max)
+-- invalid range bounds
+SELECT random(1, 0);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random(1000000000001, 1000000000000);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random(-2.0, -3.0);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('NaN'::numeric, 10);
+ERROR: lower bound cannot be NaN
+SELECT random('-Inf'::numeric, 0);
+ERROR: lower bound cannot be infinity
+SELECT random(0, 'NaN'::numeric);
+ERROR: upper bound cannot be NaN
+SELECT random(0, 'Inf'::numeric);
+ERROR: upper bound cannot be infinity
+-- empty range is OK
+SELECT random(101, 101);
+ random
+--------
+ 101
+(1 row)
+
+SELECT random(1000000000001, 1000000000001);
+ random
+---------------
+ 1000000000001
+(1 row)
+
+SELECT random(3.14, 3.14);
+ random
+--------
+ 3.14
+(1 row)
+
+-- There should be no triple duplicates in 1000 full-range 32-bit random()
+-- values. (Each of the C(1000, 3) choices of triplets from the 1000 values
+-- has a probability of 1/(2^32)^2 of being a triple duplicate, so the
+-- average number of triple duplicates is 1000 * 999 * 998 / 6 / 2^64, which
+-- is roughly 9e-12.)
+SELECT r, count(*)
+FROM (SELECT random(-2147483648, 2147483647) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 2;
+ r | count
+---+-------
+(0 rows)
+
+-- There should be no duplicates in 1000 full-range 64-bit random() values.
+SELECT r, count(*)
+FROM (SELECT random_normal(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+ r | count
+---+-------
+(0 rows)
+
+-- There should be no duplicates in 1000 15-digit random() numeric values.
+SELECT r, count(*)
+FROM (SELECT random_normal(0, 1 - 1e-15) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+ r | count
+---+-------
+(0 rows)
+
+-- Expect at least one out of 2000 random values to be in the lowest and
+-- highest 1% of the range.
+SELECT (count(*) FILTER (WHERE r < -2104533975)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 2104533974)) > 0 AS has_large
+FROM (SELECT random(-2147483648, 2147483647) r FROM generate_series(1, 2000)) ss;
+ has_small | has_large
+-----------+-----------
+ t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1500000000 OR r > 1500000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000, 1500000000) r FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+SELECT (count(*) FILTER (WHERE r < -9038904596117680292)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 9038904596117680291)) > 0 AS has_large
+FROM (SELECT random(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 2000)) ss;
+ has_small | has_large
+-----------+-----------
+ t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1500000000000000 OR r > 1500000000000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000000000, 1500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1.5 OR r > 1.5) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1.47)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1.47)) > 0 AS has_large
+FROM (SELECT random(-1.500000000000000, 1.500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+-- Every possible value should occur at least once in 2500 random() values
+-- chosen from a range with 100 distinct values.
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-50, 49) r FROM generate_series(1, 2500));
+ min | max | count
+-----+-----+-------
+ -50 | 49 | 100
+(1 row)
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(123000000000, 123000000099) r
+ FROM generate_series(1, 2500));
+ min | max | count
+--------------+--------------+-------
+ 123000000000 | 123000000099 | 100
+(1 row)
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-0.5, 0.49) r FROM generate_series(1, 2500));
+ min | max | count
+-------+------+-------
+ -0.50 | 0.49 | 100
+(1 row)
+
+-- Check for uniform distribution using the Kolmogorov-Smirnov test.
+CREATE FUNCTION ks_test_uniform_random_int_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999) / 1000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() AS uniform_int;
+ uniform_int
+-------------
+ t
+(1 row)
+
+CREATE FUNCTION ks_test_uniform_random_bigint_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999999999) / 1000000000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() AS uniform_bigint;
+ uniform_bigint
+----------------
+ t
+(1 row)
+
+CREATE FUNCTION ks_test_uniform_random_numeric_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 0.999999) r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() AS uniform_numeric;
+ uniform_numeric
+-----------------
+ t
+(1 row)
+
-- setseed() should produce a reproducible series of random() values.
SELECT setseed(0.5);
setseed
@@ -176,3 +399,140 @@ SELECT random_normal(mean => 1, stddev => 0.1) r FROM generate_series(1, 10);
0.96403105557543
(10 rows)
+-- Reproducible random(min, max) values.
+SELECT random(1, 6) FROM generate_series(1, 10);
+ random
+--------
+ 5
+ 4
+ 5
+ 1
+ 6
+ 1
+ 1
+ 3
+ 6
+ 5
+(10 rows)
+
+SELECT random(-2147483648, 2147483647) FROM generate_series(1, 10);
+ random
+-------------
+ -84380014
+ 1287883594
+ -1927252904
+ 13516867
+ -1902961616
+ -1824286201
+ -871264469
+ -1225880415
+ 229836730
+ -116039023
+(10 rows)
+
+SELECT random(-9223372036854775808, 9223372036854775807) FROM generate_series(1, 10);
+ random
+----------------------
+ -6205280962992680052
+ -3583519428011353337
+ 511801786318122700
+ 4672737727839409655
+ -6674868801536280768
+ -7816052100626646489
+ -4340613370136007199
+ -5873174504107419786
+ -2249910101649817824
+ -4493828993910792325
+(10 rows)
+
+SELECT random(-1e30, 1e30) FROM generate_series(1, 10);
+ random
+---------------------------------
+ -732116469803315942112255539315
+ 794641423514877972798449289857
+ -576932746026123093304638334719
+ 420625067723533225139761854757
+ -339227806779403187811001078919
+ -77667951539418104959241732636
+ 239810941795708162629328071599
+ 820784371155896967052141946697
+ -377084684544126871150439048352
+ -979773225250716295007225086726
+(10 rows)
+
+SELECT random(-0.4, 0.4) FROM generate_series(1, 10);
+ random
+--------
+ 0.1
+ 0.0
+ 0.4
+ -0.2
+ 0.1
+ 0.2
+ 0.3
+ 0.0
+ -0.2
+ 0.2
+(10 rows)
+
+SELECT random(0, 1 - 1e-30) FROM generate_series(1, 10);
+ random
+----------------------------------
+ 0.676442053784930109917469287265
+ 0.221310454098356723569995592911
+ 0.060101338174419259555193956224
+ 0.509960354695248239243002172364
+ 0.248680813394555793693952296993
+ 0.353262552880008646603494668901
+ 0.760692600450339509843044233719
+ 0.554987655310094483449494782510
+ 0.330890988458592995280347745733
+ 0.665435298280470361228607881507
+(10 rows)
+
+SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20) n;
+ n | random
+-----+------------------------
+ -20 | 94174615760837282445
+ -19 | 6692559888531296894
+ -18 | 801114552709125931
+ -17 | 44091460959939971
+ -16 | 2956109297383113
+ -15 | 783332278684523
+ -14 | 81534303241440
+ -13 | 2892623140500
+ -12 | 269397605141
+ -11 | 13027512296
+ -10 | 9178377775
+ -9 | 323534150
+ -8 | 91897803
+ -7 | 6091383
+ -6 | 13174
+ -5 | 92714
+ -4 | 8079
+ -3 | 429
+ -2 | 30
+ -1 | 3
+ 0 | 0
+ 1 | 0.1
+ 2 | 0.69
+ 3 | 0.492
+ 4 | 0.7380
+ 5 | 0.77078
+ 6 | 0.738142
+ 7 | 0.1808815
+ 8 | 0.14908933
+ 9 | 0.222654042
+ 10 | 0.2281295170
+ 11 | 0.73655782966
+ 12 | 0.056357256884
+ 13 | 0.8998407524375
+ 14 | 0.28198400530206
+ 15 | 0.713478222805230
+ 16 | 0.0415046850936909
+ 17 | 0.45946350291315119
+ 18 | 0.310966980367873753
+ 19 | 0.4967623661709676512
+ 20 | 0.60795101234744211935
+(41 rows)
+
diff --git a/src/test/regress/sql/random.sql b/src/test/regress/sql/random.sql
index 14cc76bc3c..ebfa7539ed 100644
--- a/src/test/regress/sql/random.sql
+++ b/src/test/regress/sql/random.sql
@@ -100,6 +100,161 @@ SELECT ks_test_normal_random() OR
ks_test_normal_random() OR
ks_test_normal_random() AS standard_normal;
+-- Test random(min, max)
+
+-- invalid range bounds
+SELECT random(1, 0);
+SELECT random(1000000000001, 1000000000000);
+SELECT random(-2.0, -3.0);
+SELECT random('NaN'::numeric, 10);
+SELECT random('-Inf'::numeric, 0);
+SELECT random(0, 'NaN'::numeric);
+SELECT random(0, 'Inf'::numeric);
+
+-- empty range is OK
+SELECT random(101, 101);
+SELECT random(1000000000001, 1000000000001);
+SELECT random(3.14, 3.14);
+
+-- There should be no triple duplicates in 1000 full-range 32-bit random()
+-- values. (Each of the C(1000, 3) choices of triplets from the 1000 values
+-- has a probability of 1/(2^32)^2 of being a triple duplicate, so the
+-- average number of triple duplicates is 1000 * 999 * 998 / 6 / 2^64, which
+-- is roughly 9e-12.)
+SELECT r, count(*)
+FROM (SELECT random(-2147483648, 2147483647) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 2;
+
+-- There should be no duplicates in 1000 full-range 64-bit random() values.
+SELECT r, count(*)
+FROM (SELECT random_normal(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+
+-- There should be no duplicates in 1000 15-digit random() numeric values.
+SELECT r, count(*)
+FROM (SELECT random_normal(0, 1 - 1e-15) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+
+-- Expect at least one out of 2000 random values to be in the lowest and
+-- highest 1% of the range.
+SELECT (count(*) FILTER (WHERE r < -2104533975)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 2104533974)) > 0 AS has_large
+FROM (SELECT random(-2147483648, 2147483647) r FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1500000000 OR r > 1500000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000, 1500000000) r FROM generate_series(1, 2000)) ss;
+
+SELECT (count(*) FILTER (WHERE r < -9038904596117680292)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 9038904596117680291)) > 0 AS has_large
+FROM (SELECT random(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1500000000000000 OR r > 1500000000000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000000000, 1500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1.5 OR r > 1.5) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1.47)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1.47)) > 0 AS has_large
+FROM (SELECT random(-1.500000000000000, 1.500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+
+-- Every possible value should occur at least once in 2500 random() values
+-- chosen from a range with 100 distinct values.
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-50, 49) r FROM generate_series(1, 2500));
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(123000000000, 123000000099) r
+ FROM generate_series(1, 2500));
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-0.5, 0.49) r FROM generate_series(1, 2500));
+
+-- Check for uniform distribution using the Kolmogorov-Smirnov test.
+
+CREATE FUNCTION ks_test_uniform_random_int_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999) / 1000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() AS uniform_int;
+
+CREATE FUNCTION ks_test_uniform_random_bigint_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999999999) / 1000000000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() AS uniform_bigint;
+
+CREATE FUNCTION ks_test_uniform_random_numeric_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 0.999999) r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() AS uniform_numeric;
+
-- setseed() should produce a reproducible series of random() values.
SELECT setseed(0.5);
@@ -113,3 +268,12 @@ SET extra_float_digits = -1;
SELECT random_normal() FROM generate_series(1, 10);
SELECT random_normal(mean => 1, stddev => 0.1) r FROM generate_series(1, 10);
+
+-- Reproducible random(min, max) values.
+SELECT random(1, 6) FROM generate_series(1, 10);
+SELECT random(-2147483648, 2147483647) FROM generate_series(1, 10);
+SELECT random(-9223372036854775808, 9223372036854775807) FROM generate_series(1, 10);
+SELECT random(-1e30, 1e30) FROM generate_series(1, 10);
+SELECT random(-0.4, 0.4) FROM generate_series(1, 10);
+SELECT random(0, 1 - 1e-30) FROM generate_series(1, 10);
+SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20) n;
--
2.35.3
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-01-30 12:47 Aleksander Alekseev <[email protected]>
parent: Dean Rasheed <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Aleksander Alekseev @ 2024-01-30 12:47 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; +Cc: Dean Rasheed <[email protected]>; David Zhang <[email protected]>; Pavel Stehule <[email protected]>
Hi,
> Interestingly, the cfbot didn't pick up on the fact that it needed
> rebasing. Anyway, the copyright years in the new file's header comment
> needed updating, so here is a rebase doing that.
Maybe I'm missing something but I'm not sure if I understand what this
test tests particularly:
```
-- There should be no triple duplicates in 1000 full-range 32-bit random()
-- values. (Each of the C(1000, 3) choices of triplets from the 1000 values
-- has a probability of 1/(2^32)^2 of being a triple duplicate, so the
-- average number of triple duplicates is 1000 * 999 * 998 / 6 / 2^64, which
-- is roughly 9e-12.)
SELECT r, count(*)
FROM (SELECT random(-2147483648, 2147483647) r
FROM generate_series(1, 1000)) ss
GROUP BY r HAVING count(*) > 2;
```
The intent seems to be to check the fact that random numbers are
distributed evenly. If this is the case I think the test is wrong. The
sequence of numbers 100, 100, 100, 100, 100 is as random as 99, 8, 4,
12, 45 and every particular sequence has low probability. All in all
personally I would argue that this is a meaningless test that just
fails with a low probability. Same for the tests that follow below.
The proper way of testing PRNG would be to call setseed() and compare
return values with expected ones. I don't mind testing the proposed
invariants but they should do this after calling setseed(). Currently
the patch places the tests right before the call.
--
Best regards,
Aleksander Alekseev
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-01-30 13:44 Dean Rasheed <[email protected]>
parent: Aleksander Alekseev <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Dean Rasheed @ 2024-01-30 13:44 UTC (permalink / raw)
To: Aleksander Alekseev <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; David Zhang <[email protected]>; Pavel Stehule <[email protected]>
On Tue, 30 Jan 2024 at 12:47, Aleksander Alekseev
<[email protected]> wrote:
>
> Maybe I'm missing something but I'm not sure if I understand what this
> test tests particularly:
>
> ```
> -- There should be no triple duplicates in 1000 full-range 32-bit random()
> -- values. (Each of the C(1000, 3) choices of triplets from the 1000 values
> -- has a probability of 1/(2^32)^2 of being a triple duplicate, so the
> -- average number of triple duplicates is 1000 * 999 * 998 / 6 / 2^64, which
> -- is roughly 9e-12.)
> SELECT r, count(*)
> FROM (SELECT random(-2147483648, 2147483647) r
> FROM generate_series(1, 1000)) ss
> GROUP BY r HAVING count(*) > 2;
> ```
>
> The intent seems to be to check the fact that random numbers are
> distributed evenly. If this is the case I think the test is wrong. The
> sequence of numbers 100, 100, 100, 100, 100 is as random as 99, 8, 4,
> 12, 45 and every particular sequence has low probability. All in all
> personally I would argue that this is a meaningless test that just
> fails with a low probability. Same for the tests that follow below.
>
I'm following the same approach used to test the existing random
functions, and the idea is the same. For example, this existing test:
-- There should be no duplicates in 1000 random() values.
-- (Assuming 52 random bits in the float8 results, we could
-- take as many as 3000 values and still have less than 1e-9 chance
-- of failure, per https://en.wikipedia.org/wiki/Birthday_problem)
SELECT r, count(*)
FROM (SELECT random() r FROM generate_series(1, 1000)) ss
GROUP BY r HAVING count(*) > 1;
If the underlying PRNG were non-uniform, or the method of reduction to
the required range was flawed in some way that reduced the number of
actual possible return values, then the probability of duplicates
would be increased. A non-uniform distribution would probably be
caught by the KS tests, but uniform gaps in the possible outputs might
not be, so I think this test still has value.
> The proper way of testing PRNG would be to call setseed() and compare
> return values with expected ones. I don't mind testing the proposed
> invariants but they should do this after calling setseed(). Currently
> the patch places the tests right before the call.
>
There are also new tests of that nature, following the call to
setseed(0.5). They're useful for a quick visual check of the results,
and confirming the expected number of digits after the decimal point
in the numeric case. However, I think those tests are insufficient on
their own.
Regards,
Dean
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-02-24 17:10 Tomas Vondra <[email protected]>
parent: Dean Rasheed <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Tomas Vondra @ 2024-02-24 17:10 UTC (permalink / raw)
To: Dean Rasheed <[email protected]>; David Zhang <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi Dean,
I did a quick review and a little bit of testing on the patch today. I
think it's a good/useful idea, and I think the code is ready to go (the
code is certainly much cleaner than anything I'd written ...).
I do have one minor comments regarding the docs - it refers to "random
functions" in a couple places, which sounds to me as if it was talking
about some functions arbitrarily taken from some list, although it
clearly means "functions generating random numbers". (I realize this
might be just due to me not being native speaker.)
Did you think about adding more functions generating either other types
of data distributions (now we have uniform and normal), or random data
for other data types (I often need random strings, for example)?
Of course, I'm not saying this patch needs to do that. But perhaps it
might affect how we name stuff to make it "extensible".
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-02-27 17:33 Dean Rasheed <[email protected]>
parent: Tomas Vondra <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Dean Rasheed @ 2024-02-27 17:33 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: David Zhang <[email protected]>; Pavel Stehule <[email protected]>; PostgreSQL Hackers <[email protected]>
On Sat, 24 Feb 2024 at 17:10, Tomas Vondra
<[email protected]> wrote:
>
> Hi Dean,
>
> I did a quick review and a little bit of testing on the patch today. I
> think it's a good/useful idea, and I think the code is ready to go (the
> code is certainly much cleaner than anything I'd written ...).
>
Thanks for reviewing!
> I do have one minor comments regarding the docs - it refers to "random
> functions" in a couple places, which sounds to me as if it was talking
> about some functions arbitrarily taken from some list, although it
> clearly means "functions generating random numbers". (I realize this
> might be just due to me not being native speaker.)
>
Yes, I think you're right, that wording was a bit clumsy. Attached is
an update that's hopefully a bit better.
> Did you think about adding more functions generating either other types
> of data distributions (now we have uniform and normal), or random data
> for other data types (I often need random strings, for example)?
>
> Of course, I'm not saying this patch needs to do that. But perhaps it
> might affect how we name stuff to make it "extensible".
>
I don't have any plans to add more random functions, but I did think
about it from that perspective. Currently we have "random" and
"random_normal", so the natural extension would be
"random_${distribution}" for other data distributions, with "uniform"
as the default distribution, if omitted.
For different result datatypes, it ought to be mostly possible to
determine the result type from the arguments. There might be some
exceptions, like maybe "random_bytes(length)" to generate a byte
array, but I think that would be OK.
Regards,
Dean
Attachments:
[text/x-patch] v3-0001-Add-random-number-in-range-functions.patch (40.5K, ../../CAEZATCUdw2L9n1==2e+Uspq0Lcz10dKBCVHBAAtXwz11zXWGDA@mail.gmail.com/2-v3-0001-Add-random-number-in-range-functions.patch)
download | inline diff:
From b1a63ecce667377435dc16fc262509bff2355b29 Mon Sep 17 00:00:00 2001
From: Dean Rasheed <[email protected]>
Date: Fri, 25 Aug 2023 10:42:38 +0100
Subject: [PATCH v3] Add random-number-in-range functions.
This adds 3 functions:
random(min int, max int) returns int
random(min bigint, max bigint) returns bigint
random(min numeric, max numeric) returns numeric
Each returns a random number in the range [min, max].
In the numeric case, the result scale is Max(scale(min), scale(max)).
---
doc/src/sgml/func.sgml | 43 ++-
src/backend/utils/adt/Makefile | 1 +
src/backend/utils/adt/float.c | 95 ------
src/backend/utils/adt/meson.build | 1 +
src/backend/utils/adt/numeric.c | 219 +++++++++++++
src/backend/utils/adt/pseudorandomfuncs.c | 185 +++++++++++
src/common/pg_prng.c | 36 +++
src/include/catalog/pg_proc.dat | 12 +
src/include/common/pg_prng.h | 1 +
src/include/utils/numeric.h | 4 +
src/test/regress/expected/random.out | 360 ++++++++++++++++++++++
src/test/regress/sql/random.sql | 164 ++++++++++
12 files changed, 1021 insertions(+), 100 deletions(-)
create mode 100644 src/backend/utils/adt/pseudorandomfuncs.c
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..e39e569fb6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1862,6 +1862,39 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>random</primary>
+ </indexterm>
+ <function>random</function> ( <parameter>min</parameter> <type>integer</type>, <parameter>max</parameter> <type>integer</type> )
+ <returnvalue>integer</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>bigint</type>, <parameter>max</parameter> <type>bigint</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>numeric</type>, <parameter>max</parameter> <type>numeric</type> )
+ <returnvalue>numeric</returnvalue>
+ </para>
+ <para>
+ Returns a random value in the range
+ <parameter>min</parameter> <= x <= <parameter>max</parameter>.
+ For type <type>numeric</type>, the result will have the same number of
+ fractional decimal digits as <parameter>min</parameter> or
+ <parameter>max</parameter>, whichever has more.
+ </para>
+ <para>
+ <literal>random(1, 10)</literal>
+ <returnvalue>7</returnvalue>
+ </para>
+ <para>
+ <literal>random(-0.499, 0.499)</literal>
+ <returnvalue>0.347</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -1906,19 +1939,19 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</table>
<para>
- The <function>random()</function> function uses a deterministic
- pseudo-random number generator.
+ The <function>random()</function> and <function>random_normal()</function>
+ functions listed in <xref linkend="functions-math-random-table"/> use a
+ deterministic pseudo-random number generator.
It is fast but not suitable for cryptographic
applications; see the <xref linkend="pgcrypto"/> module for a more
secure alternative.
If <function>setseed()</function> is called, the series of results of
- subsequent <function>random()</function> calls in the current session
+ subsequent calls to these functions in the current session
can be repeated by re-issuing <function>setseed()</function> with the same
argument.
Without any prior <function>setseed()</function> call in the same
- session, the first <function>random()</function> call obtains a seed
+ session, the first call to any of these functions obtains a seed
from a platform-dependent source of random bits.
- These remarks hold equally for <function>random_normal()</function>.
</para>
<para>
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 199eae525d..610ccf2f79 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -82,6 +82,7 @@ OBJS = \
pg_lsn.o \
pg_upgrade_support.o \
pgstatfuncs.o \
+ pseudorandomfuncs.o \
pseudotypes.o \
quote.o \
rangetypes.o \
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index 901edcc896..cbbb8aecaf 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -21,10 +21,8 @@
#include "catalog/pg_type.h"
#include "common/int.h"
-#include "common/pg_prng.h"
#include "common/shortest_dec.h"
#include "libpq/pqformat.h"
-#include "miscadmin.h"
#include "utils/array.h"
#include "utils/float.h"
#include "utils/fmgrprotos.h"
@@ -64,10 +62,6 @@ float8 degree_c_sixty = 60.0;
float8 degree_c_one_half = 0.5;
float8 degree_c_one = 1.0;
-/* State for drandom() and setseed() */
-static bool drandom_seed_set = false;
-static pg_prng_state drandom_seed;
-
/* Local function prototypes */
static double sind_q1(double x);
static double cosd_q1(double x);
@@ -2785,95 +2779,6 @@ derfc(PG_FUNCTION_ARGS)
}
-/* ========== RANDOM FUNCTIONS ========== */
-
-
-/*
- * initialize_drandom_seed - initialize drandom_seed if not yet done
- */
-static void
-initialize_drandom_seed(void)
-{
- /* Initialize random seed, if not done yet in this process */
- if (unlikely(!drandom_seed_set))
- {
- /*
- * If possible, initialize the seed using high-quality random bits.
- * Should that fail for some reason, we fall back on a lower-quality
- * seed based on current time and PID.
- */
- if (unlikely(!pg_prng_strong_seed(&drandom_seed)))
- {
- TimestampTz now = GetCurrentTimestamp();
- uint64 iseed;
-
- /* Mix the PID with the most predictable bits of the timestamp */
- iseed = (uint64) now ^ ((uint64) MyProcPid << 32);
- pg_prng_seed(&drandom_seed, iseed);
- }
- drandom_seed_set = true;
- }
-}
-
-/*
- * drandom - returns a random number
- */
-Datum
-drandom(PG_FUNCTION_ARGS)
-{
- float8 result;
-
- initialize_drandom_seed();
-
- /* pg_prng_double produces desired result range [0.0 - 1.0) */
- result = pg_prng_double(&drandom_seed);
-
- PG_RETURN_FLOAT8(result);
-}
-
-/*
- * drandom_normal - returns a random number from a normal distribution
- */
-Datum
-drandom_normal(PG_FUNCTION_ARGS)
-{
- float8 mean = PG_GETARG_FLOAT8(0);
- float8 stddev = PG_GETARG_FLOAT8(1);
- float8 result,
- z;
-
- initialize_drandom_seed();
-
- /* Get random value from standard normal(mean = 0.0, stddev = 1.0) */
- z = pg_prng_double_normal(&drandom_seed);
- /* Transform the normal standard variable (z) */
- /* using the target normal distribution parameters */
- result = (stddev * z) + mean;
-
- PG_RETURN_FLOAT8(result);
-}
-
-/*
- * setseed - set seed for the random number generator
- */
-Datum
-setseed(PG_FUNCTION_ARGS)
-{
- float8 seed = PG_GETARG_FLOAT8(0);
-
- if (seed < -1 || seed > 1 || isnan(seed))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("setseed parameter %g is out of allowed range [-1,1]",
- seed)));
-
- pg_prng_fseed(&drandom_seed, seed);
- drandom_seed_set = true;
-
- PG_RETURN_VOID();
-}
-
-
/*
* =========================
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index f3dfb52204..48dbcf59a5 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -69,6 +69,7 @@ backend_sources += files(
'pg_lsn.c',
'pg_upgrade_support.c',
'pgstatfuncs.c',
+ 'pseudorandomfuncs.c',
'pseudotypes.c',
'quote.c',
'rangetypes.c',
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 015a41dc56..9761d2e931 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -584,6 +584,8 @@ static void power_var(const NumericVar *base, const NumericVar *exp,
static void power_var_int(const NumericVar *base, int exp, int exp_dscale,
NumericVar *result);
static void power_ten_int(int exp, NumericVar *result);
+static void random_var(pg_prng_state *state, const NumericVar *rmin,
+ const NumericVar *rmax, NumericVar *result);
static int cmp_abs(const NumericVar *var1, const NumericVar *var2);
static int cmp_abs_common(const NumericDigit *var1digits, int var1ndigits,
@@ -4220,6 +4222,56 @@ numeric_trim_scale(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(res);
}
+/*
+ * Return a random numeric value in the range [rmin, rmax].
+ */
+Numeric
+random_numeric(pg_prng_state *state, Numeric rmin, Numeric rmax)
+{
+ NumericVar rmin_var;
+ NumericVar rmax_var;
+ NumericVar result;
+ Numeric res;
+
+ /* Range bounds must not be NaN/infinity */
+ if (NUMERIC_IS_SPECIAL(rmin))
+ {
+ if (NUMERIC_IS_NAN(rmin))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound cannot be NaN"));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound cannot be infinity"));
+ }
+ if (NUMERIC_IS_SPECIAL(rmax))
+ {
+ if (NUMERIC_IS_NAN(rmax))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("upper bound cannot be NaN"));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("upper bound cannot be infinity"));
+ }
+
+ /* Return a random value in the range [rmin, rmax] */
+ init_var_from_num(rmin, &rmin_var);
+ init_var_from_num(rmax, &rmax_var);
+
+ init_var(&result);
+
+ random_var(state, &rmin_var, &rmax_var, &result);
+
+ res = make_result(&result);
+
+ free_var(&result);
+
+ return res;
+}
+
/* ----------------------------------------------------------------------
*
@@ -11263,6 +11315,173 @@ power_ten_int(int exp, NumericVar *result)
result->digits[0] *= 10;
}
+/*
+ * random_var() - return a random value in the range [rmin, rmax].
+ */
+static void
+random_var(pg_prng_state *state, const NumericVar *rmin,
+ const NumericVar *rmax, NumericVar *result)
+{
+ int rscale;
+ NumericVar rlen;
+ int res_ndigits;
+ int n;
+ int pow10;
+ int i;
+ uint64 rlen64;
+ int rlen64_ndigits;
+
+ rscale = Max(rmin->dscale, rmax->dscale);
+
+ /* Compute rlen = rmax - rmin and check the range bounds */
+ init_var(&rlen);
+ sub_var(rmax, rmin, &rlen);
+
+ if (rlen.sign == NUMERIC_NEG)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ /* Special case for an empty range */
+ if (rlen.ndigits == 0)
+ {
+ set_var_from_var(rmin, result);
+ result->dscale = rscale;
+ free_var(&rlen);
+ return;
+ }
+
+ /*
+ * Otherwise, select a random value in the range [0, rlen = rmax - rmin],
+ * and shift it to the required range by adding rmin.
+ */
+
+ /* Required result digits */
+ res_ndigits = rlen.weight + 1 + (rscale + DEC_DIGITS - 1) / DEC_DIGITS;
+
+ /*
+ * To get the required rscale, the final result digit must be a multiple
+ * of pow10 = 10^n, where n = (-rscale) mod DEC_DIGITS.
+ */
+ n = ((rscale + DEC_DIGITS - 1) / DEC_DIGITS) * DEC_DIGITS - rscale;
+ pow10 = 1;
+ for (i = 0; i < n; i++)
+ pow10 *= 10;
+
+ /*
+ * To choose a random value uniformly from the range [0, rlen], we choose
+ * from the slightly larger range [0, rlen2], where rlen2 is formed from
+ * rlen by copying the first 4 NBASE digits, and setting all remaining
+ * decimal digits to "9".
+ *
+ * Without loss of generality, we can ignore the weight of rlen2 and treat
+ * it as a pure integer for the purposes of this discussion. The process
+ * above gives rlen2 + 1 = rlen64 * 10^N, for some integer N, where rlen64
+ * is a 64-bit integer formed from the first 4 NBASE digits copied from
+ * rlen. Since this trivially factors into smaller pieces that fit in
+ * 64-bit integers, the task of choosing a random value uniformly from the
+ * rlen2 + 1 possible values in [0, rlen2] is much simpler.
+ *
+ * If the random value selected is too large, it is rejected, and we try
+ * again until we get a result <= rlen, ensuring that the overall result
+ * is uniform (no particular value is any more likely than any other).
+ *
+ * Since rlen64 holds 4 NBASE digits from rlen, it contains at least
+ * DEC_DIGITS * 3 + 1 decimal digits (i.e., at least 13 decimal digits,
+ * when DEC_DIGITS is 4). Therefore the probability of needing to reject
+ * the value chosen and retry is less than 1e-13.
+ */
+ rlen64 = (uint64) rlen.digits[0];
+ rlen64_ndigits = 1;
+ while (rlen64_ndigits < res_ndigits && rlen64_ndigits < 4)
+ {
+ rlen64 *= NBASE;
+ if (rlen64_ndigits < rlen.ndigits)
+ rlen64 += rlen.digits[rlen64_ndigits];
+ rlen64_ndigits++;
+ }
+
+ /* Loop until we get a result <= rlen */
+ do
+ {
+ NumericDigit *res_digits;
+ uint64 rand;
+ int whole_ndigits;
+
+ alloc_var(result, res_ndigits);
+ result->sign = NUMERIC_POS;
+ result->weight = rlen.weight;
+ result->dscale = rscale;
+ res_digits = result->digits;
+
+ /*
+ * Set the first rlen64_ndigits using a random value in [0, rlen64].
+ *
+ * If this is the whole result, and rscale is not a multiple of
+ * DEC_DIGITS (pow10 from above is not 1), then we need this to be a
+ * multiple of pow10.
+ */
+ if (rlen64_ndigits == res_ndigits && pow10 != 1)
+ rand = pg_prng_uint64_range(state, 0, rlen64 / pow10) * pow10;
+ else
+ rand = pg_prng_uint64_range(state, 0, rlen64);
+
+ for (i = rlen64_ndigits - 1; i >= 0; i--)
+ {
+ res_digits[i] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ }
+
+ /*
+ * Set the remaining digits to random values in range [0, NBASE),
+ * noting that the last digit needs to be a multiple of pow10.
+ */
+ whole_ndigits = res_ndigits;
+ if (pow10 != 1)
+ whole_ndigits--;
+
+ /* Set whole digits in groups of 4 for best performance */
+ i = rlen64_ndigits;
+ while (i < whole_ndigits - 3)
+ {
+ rand = pg_prng_uint64_range(state, 0,
+ (uint64) NBASE * NBASE * NBASE * NBASE - 1);
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) (rand % NBASE);
+ rand = rand / NBASE;
+ res_digits[i++] = (NumericDigit) rand;
+ }
+
+ /* Remaining whole digits */
+ while (i < whole_ndigits)
+ {
+ rand = pg_prng_uint64_range(state, 0, NBASE - 1);
+ res_digits[i++] = (NumericDigit) rand;
+ }
+
+ /* Final partial digit (multiple of pow10) */
+ if (i < res_ndigits)
+ {
+ rand = pg_prng_uint64_range(state, 0, NBASE / pow10 - 1) * pow10;
+ res_digits[i] = (NumericDigit) rand;
+ }
+
+ /* Remove leading/trailing zeroes */
+ strip_var(result);
+
+ /* If result > rlen, try again */
+
+ } while (cmp_var(result, &rlen) > 0);
+
+ /* Offset the result to the required range */
+ add_var(result, rmin, result);
+
+ free_var(&rlen);
+}
+
/* ----------------------------------------------------------------------
*
diff --git a/src/backend/utils/adt/pseudorandomfuncs.c b/src/backend/utils/adt/pseudorandomfuncs.c
new file mode 100644
index 0000000000..8e82c7078c
--- /dev/null
+++ b/src/backend/utils/adt/pseudorandomfuncs.c
@@ -0,0 +1,185 @@
+/*-------------------------------------------------------------------------
+ *
+ * pseudorandomfuncs.c
+ * Functions giving SQL access to a pseudorandom number generator.
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/pseudorandomfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <math.h>
+
+#include "common/pg_prng.h"
+#include "miscadmin.h"
+#include "utils/fmgrprotos.h"
+#include "utils/numeric.h"
+#include "utils/timestamp.h"
+
+/* Shared PRNG state used by all the random functions */
+static pg_prng_state prng_state;
+static bool prng_seed_set = false;
+
+/*
+ * initialize_prng() -
+ *
+ * Initialize (seed) the PRNG, if not done yet in this process.
+ */
+static void
+initialize_prng(void)
+{
+ if (unlikely(!prng_seed_set))
+ {
+ /*
+ * If possible, seed the PRNG using high-quality random bits. Should
+ * that fail for some reason, we fall back on a lower-quality seed
+ * based on current time and PID.
+ */
+ if (unlikely(!pg_prng_strong_seed(&prng_state)))
+ {
+ TimestampTz now = GetCurrentTimestamp();
+ uint64 iseed;
+
+ /* Mix the PID with the most predictable bits of the timestamp */
+ iseed = (uint64) now ^ ((uint64) MyProcPid << 32);
+ pg_prng_seed(&prng_state, iseed);
+ }
+ prng_seed_set = true;
+ }
+}
+
+/*
+ * setseed() -
+ *
+ * Seed the PRNG from a specified value in the range [-1.0, 1.0].
+ */
+Datum
+setseed(PG_FUNCTION_ARGS)
+{
+ float8 seed = PG_GETARG_FLOAT8(0);
+
+ if (seed < -1 || seed > 1 || isnan(seed))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("setseed parameter %g is out of allowed range [-1,1]",
+ seed));
+
+ pg_prng_fseed(&prng_state, seed);
+ prng_seed_set = true;
+
+ PG_RETURN_VOID();
+}
+
+/*
+ * drandom() -
+ *
+ * Returns a random number chosen uniformly in the range [0.0, 1.0).
+ */
+Datum
+drandom(PG_FUNCTION_ARGS)
+{
+ float8 result;
+
+ initialize_prng();
+
+ /* pg_prng_double produces desired result range [0.0, 1.0) */
+ result = pg_prng_double(&prng_state);
+
+ PG_RETURN_FLOAT8(result);
+}
+
+/*
+ * drandom_normal() -
+ *
+ * Returns a random number from a normal distribution.
+ */
+Datum
+drandom_normal(PG_FUNCTION_ARGS)
+{
+ float8 mean = PG_GETARG_FLOAT8(0);
+ float8 stddev = PG_GETARG_FLOAT8(1);
+ float8 result,
+ z;
+
+ initialize_prng();
+
+ /* Get random value from standard normal(mean = 0.0, stddev = 1.0) */
+ z = pg_prng_double_normal(&prng_state);
+ /* Transform the normal standard variable (z) */
+ /* using the target normal distribution parameters */
+ result = (stddev * z) + mean;
+
+ PG_RETURN_FLOAT8(result);
+}
+
+/*
+ * int4random() -
+ *
+ * Returns a random 32-bit integer chosen uniformly in the specified range.
+ */
+Datum
+int4random(PG_FUNCTION_ARGS)
+{
+ int32 rmin = PG_GETARG_INT32(0);
+ int32 rmax = PG_GETARG_INT32(1);
+ int32 result;
+
+ if (rmin > rmax)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ initialize_prng();
+
+ result = (int32) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_INT32(result);
+}
+
+/*
+ * int8random() -
+ *
+ * Returns a random 64-bit integer chosen uniformly in the specified range.
+ */
+Datum
+int8random(PG_FUNCTION_ARGS)
+{
+ int64 rmin = PG_GETARG_INT64(0);
+ int64 rmax = PG_GETARG_INT64(1);
+ int64 result;
+
+ if (rmin > rmax)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+
+ initialize_prng();
+
+ result = pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_INT64(result);
+}
+
+/*
+ * numeric_random() -
+ *
+ * Returns a random numeric value chosen uniformly in the specified range.
+ */
+Datum
+numeric_random(PG_FUNCTION_ARGS)
+{
+ Numeric rmin = PG_GETARG_NUMERIC(0);
+ Numeric rmax = PG_GETARG_NUMERIC(1);
+ Numeric result;
+
+ initialize_prng();
+
+ result = random_numeric(&prng_state, rmin, rmax);
+
+ PG_RETURN_NUMERIC(result);
+}
diff --git a/src/common/pg_prng.c b/src/common/pg_prng.c
index c1714a02bd..15b39411a9 100644
--- a/src/common/pg_prng.c
+++ b/src/common/pg_prng.c
@@ -184,6 +184,42 @@ pg_prng_int64p(pg_prng_state *state)
return (int64) (xoroshiro128ss(state) & UINT64CONST(0x7FFFFFFFFFFFFFFF));
}
+/*
+ * Select a random int64 uniformly from the range [rmin, rmax].
+ * If the range is empty, rmin is always produced.
+ */
+int64
+pg_prng_int64_range(pg_prng_state *state, int64 rmin, int64 rmax)
+{
+ int64 val;
+
+ if (likely(rmax > rmin))
+ {
+ uint64 uval;
+
+ /*
+ * Use pg_prng_uint64_range(). Can't simply pass it rmin and rmax,
+ * since (uint64) rmin will be larger than (uint64) rmax if rmin < 0.
+ */
+ uval = (uint64) rmin +
+ pg_prng_uint64_range(state, 0, (uint64) rmax - (uint64) rmin);
+
+ /*
+ * Safely convert back to int64, avoiding implementation-defined
+ * behavior for values larger than PG_INT64_MAX. Modern compilers
+ * will reduce this to a simple assignment.
+ */
+ if (uval > PG_INT64_MAX)
+ val = (int64) (uval - PG_INT64_MIN) + PG_INT64_MIN;
+ else
+ val = (int64) uval;
+ }
+ else
+ val = rmin;
+
+ return val;
+}
+
/*
* Select a random uint32 uniformly from the range [0, PG_UINT32_MAX].
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9c120fc2b7..0d2b993e38 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3381,6 +3381,18 @@
proname => 'random_normal', provolatile => 'v', proparallel => 'r',
prorettype => 'float8', proargtypes => 'float8 float8',
prosrc => 'drandom_normal' },
+{ oid => '9719', descr => 'random integer in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'int4', proargtypes => 'int4 int4',
+ proargnames => '{min,max}', prosrc => 'int4random' },
+{ oid => '9720', descr => 'random bigint in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'int8', proargtypes => 'int8 int8',
+ proargnames => '{min,max}', prosrc => 'int8random' },
+{ oid => '9721', descr => 'random numeric in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'numeric', proargtypes => 'numeric numeric',
+ proargnames => '{min,max}', prosrc => 'numeric_random' },
{ oid => '1599', descr => 'set random seed',
proname => 'setseed', provolatile => 'v', proparallel => 'r',
prorettype => 'void', proargtypes => 'float8', prosrc => 'setseed' },
diff --git a/src/include/common/pg_prng.h b/src/include/common/pg_prng.h
index e201b95686..c114c6419d 100644
--- a/src/include/common/pg_prng.h
+++ b/src/include/common/pg_prng.h
@@ -51,6 +51,7 @@ extern uint64 pg_prng_uint64(pg_prng_state *state);
extern uint64 pg_prng_uint64_range(pg_prng_state *state, uint64 rmin, uint64 rmax);
extern int64 pg_prng_int64(pg_prng_state *state);
extern int64 pg_prng_int64p(pg_prng_state *state);
+extern int64 pg_prng_int64_range(pg_prng_state *state, int64 rmin, int64 rmax);
extern uint32 pg_prng_uint32(pg_prng_state *state);
extern int32 pg_prng_int32(pg_prng_state *state);
extern int32 pg_prng_int32p(pg_prng_state *state);
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 2f7184e299..43c75c436f 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -14,6 +14,7 @@
#ifndef _PG_NUMERIC_H_
#define _PG_NUMERIC_H_
+#include "common/pg_prng.h"
#include "fmgr.h"
/*
@@ -103,4 +104,7 @@ extern Numeric numeric_mod_opt_error(Numeric num1, Numeric num2,
extern int32 numeric_int4_opt_error(Numeric num, bool *have_error);
extern int64 numeric_int8_opt_error(Numeric num, bool *have_error);
+extern Numeric random_numeric(pg_prng_state *state,
+ Numeric rmin, Numeric rmax);
+
#endif /* _PG_NUMERIC_H_ */
diff --git a/src/test/regress/expected/random.out b/src/test/regress/expected/random.out
index 223590720c..43cf88a363 100644
--- a/src/test/regress/expected/random.out
+++ b/src/test/regress/expected/random.out
@@ -120,6 +120,229 @@ SELECT ks_test_normal_random() OR
t
(1 row)
+-- Test random(min, max)
+-- invalid range bounds
+SELECT random(1, 0);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random(1000000000001, 1000000000000);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random(-2.0, -3.0);
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('NaN'::numeric, 10);
+ERROR: lower bound cannot be NaN
+SELECT random('-Inf'::numeric, 0);
+ERROR: lower bound cannot be infinity
+SELECT random(0, 'NaN'::numeric);
+ERROR: upper bound cannot be NaN
+SELECT random(0, 'Inf'::numeric);
+ERROR: upper bound cannot be infinity
+-- empty range is OK
+SELECT random(101, 101);
+ random
+--------
+ 101
+(1 row)
+
+SELECT random(1000000000001, 1000000000001);
+ random
+---------------
+ 1000000000001
+(1 row)
+
+SELECT random(3.14, 3.14);
+ random
+--------
+ 3.14
+(1 row)
+
+-- There should be no triple duplicates in 1000 full-range 32-bit random()
+-- values. (Each of the C(1000, 3) choices of triplets from the 1000 values
+-- has a probability of 1/(2^32)^2 of being a triple duplicate, so the
+-- average number of triple duplicates is 1000 * 999 * 998 / 6 / 2^64, which
+-- is roughly 9e-12.)
+SELECT r, count(*)
+FROM (SELECT random(-2147483648, 2147483647) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 2;
+ r | count
+---+-------
+(0 rows)
+
+-- There should be no duplicates in 1000 full-range 64-bit random() values.
+SELECT r, count(*)
+FROM (SELECT random_normal(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+ r | count
+---+-------
+(0 rows)
+
+-- There should be no duplicates in 1000 15-digit random() numeric values.
+SELECT r, count(*)
+FROM (SELECT random_normal(0, 1 - 1e-15) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+ r | count
+---+-------
+(0 rows)
+
+-- Expect at least one out of 2000 random values to be in the lowest and
+-- highest 1% of the range.
+SELECT (count(*) FILTER (WHERE r < -2104533975)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 2104533974)) > 0 AS has_large
+FROM (SELECT random(-2147483648, 2147483647) r FROM generate_series(1, 2000)) ss;
+ has_small | has_large
+-----------+-----------
+ t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1500000000 OR r > 1500000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000, 1500000000) r FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+SELECT (count(*) FILTER (WHERE r < -9038904596117680292)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 9038904596117680291)) > 0 AS has_large
+FROM (SELECT random(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 2000)) ss;
+ has_small | has_large
+-----------+-----------
+ t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1500000000000000 OR r > 1500000000000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000000000, 1500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+SELECT count(*) FILTER (WHERE r < -1.5 OR r > 1.5) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1.47)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1.47)) > 0 AS has_large
+FROM (SELECT random(-1.500000000000000, 1.500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+ out_of_range | has_small | has_large
+--------------+-----------+-----------
+ 0 | t | t
+(1 row)
+
+-- Every possible value should occur at least once in 2500 random() values
+-- chosen from a range with 100 distinct values.
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-50, 49) r FROM generate_series(1, 2500));
+ min | max | count
+-----+-----+-------
+ -50 | 49 | 100
+(1 row)
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(123000000000, 123000000099) r
+ FROM generate_series(1, 2500));
+ min | max | count
+--------------+--------------+-------
+ 123000000000 | 123000000099 | 100
+(1 row)
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-0.5, 0.49) r FROM generate_series(1, 2500));
+ min | max | count
+-------+------+-------
+ -0.50 | 0.49 | 100
+(1 row)
+
+-- Check for uniform distribution using the Kolmogorov-Smirnov test.
+CREATE FUNCTION ks_test_uniform_random_int_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999) / 1000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() AS uniform_int;
+ uniform_int
+-------------
+ t
+(1 row)
+
+CREATE FUNCTION ks_test_uniform_random_bigint_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999999999) / 1000000000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() AS uniform_bigint;
+ uniform_bigint
+----------------
+ t
+(1 row)
+
+CREATE FUNCTION ks_test_uniform_random_numeric_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 0.999999) r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+SELECT ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() AS uniform_numeric;
+ uniform_numeric
+-----------------
+ t
+(1 row)
+
-- setseed() should produce a reproducible series of random() values.
SELECT setseed(0.5);
setseed
@@ -176,3 +399,140 @@ SELECT random_normal(mean => 1, stddev => 0.1) r FROM generate_series(1, 10);
0.96403105557543
(10 rows)
+-- Reproducible random(min, max) values.
+SELECT random(1, 6) FROM generate_series(1, 10);
+ random
+--------
+ 5
+ 4
+ 5
+ 1
+ 6
+ 1
+ 1
+ 3
+ 6
+ 5
+(10 rows)
+
+SELECT random(-2147483648, 2147483647) FROM generate_series(1, 10);
+ random
+-------------
+ -84380014
+ 1287883594
+ -1927252904
+ 13516867
+ -1902961616
+ -1824286201
+ -871264469
+ -1225880415
+ 229836730
+ -116039023
+(10 rows)
+
+SELECT random(-9223372036854775808, 9223372036854775807) FROM generate_series(1, 10);
+ random
+----------------------
+ -6205280962992680052
+ -3583519428011353337
+ 511801786318122700
+ 4672737727839409655
+ -6674868801536280768
+ -7816052100626646489
+ -4340613370136007199
+ -5873174504107419786
+ -2249910101649817824
+ -4493828993910792325
+(10 rows)
+
+SELECT random(-1e30, 1e30) FROM generate_series(1, 10);
+ random
+---------------------------------
+ -732116469803315942112255539315
+ 794641423514877972798449289857
+ -576932746026123093304638334719
+ 420625067723533225139761854757
+ -339227806779403187811001078919
+ -77667951539418104959241732636
+ 239810941795708162629328071599
+ 820784371155896967052141946697
+ -377084684544126871150439048352
+ -979773225250716295007225086726
+(10 rows)
+
+SELECT random(-0.4, 0.4) FROM generate_series(1, 10);
+ random
+--------
+ 0.1
+ 0.0
+ 0.4
+ -0.2
+ 0.1
+ 0.2
+ 0.3
+ 0.0
+ -0.2
+ 0.2
+(10 rows)
+
+SELECT random(0, 1 - 1e-30) FROM generate_series(1, 10);
+ random
+----------------------------------
+ 0.676442053784930109917469287265
+ 0.221310454098356723569995592911
+ 0.060101338174419259555193956224
+ 0.509960354695248239243002172364
+ 0.248680813394555793693952296993
+ 0.353262552880008646603494668901
+ 0.760692600450339509843044233719
+ 0.554987655310094483449494782510
+ 0.330890988458592995280347745733
+ 0.665435298280470361228607881507
+(10 rows)
+
+SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20) n;
+ n | random
+-----+------------------------
+ -20 | 94174615760837282445
+ -19 | 6692559888531296894
+ -18 | 801114552709125931
+ -17 | 44091460959939971
+ -16 | 2956109297383113
+ -15 | 783332278684523
+ -14 | 81534303241440
+ -13 | 2892623140500
+ -12 | 269397605141
+ -11 | 13027512296
+ -10 | 9178377775
+ -9 | 323534150
+ -8 | 91897803
+ -7 | 6091383
+ -6 | 13174
+ -5 | 92714
+ -4 | 8079
+ -3 | 429
+ -2 | 30
+ -1 | 3
+ 0 | 0
+ 1 | 0.1
+ 2 | 0.69
+ 3 | 0.492
+ 4 | 0.7380
+ 5 | 0.77078
+ 6 | 0.738142
+ 7 | 0.1808815
+ 8 | 0.14908933
+ 9 | 0.222654042
+ 10 | 0.2281295170
+ 11 | 0.73655782966
+ 12 | 0.056357256884
+ 13 | 0.8998407524375
+ 14 | 0.28198400530206
+ 15 | 0.713478222805230
+ 16 | 0.0415046850936909
+ 17 | 0.45946350291315119
+ 18 | 0.310966980367873753
+ 19 | 0.4967623661709676512
+ 20 | 0.60795101234744211935
+(41 rows)
+
diff --git a/src/test/regress/sql/random.sql b/src/test/regress/sql/random.sql
index 14cc76bc3c..ebfa7539ed 100644
--- a/src/test/regress/sql/random.sql
+++ b/src/test/regress/sql/random.sql
@@ -100,6 +100,161 @@ SELECT ks_test_normal_random() OR
ks_test_normal_random() OR
ks_test_normal_random() AS standard_normal;
+-- Test random(min, max)
+
+-- invalid range bounds
+SELECT random(1, 0);
+SELECT random(1000000000001, 1000000000000);
+SELECT random(-2.0, -3.0);
+SELECT random('NaN'::numeric, 10);
+SELECT random('-Inf'::numeric, 0);
+SELECT random(0, 'NaN'::numeric);
+SELECT random(0, 'Inf'::numeric);
+
+-- empty range is OK
+SELECT random(101, 101);
+SELECT random(1000000000001, 1000000000001);
+SELECT random(3.14, 3.14);
+
+-- There should be no triple duplicates in 1000 full-range 32-bit random()
+-- values. (Each of the C(1000, 3) choices of triplets from the 1000 values
+-- has a probability of 1/(2^32)^2 of being a triple duplicate, so the
+-- average number of triple duplicates is 1000 * 999 * 998 / 6 / 2^64, which
+-- is roughly 9e-12.)
+SELECT r, count(*)
+FROM (SELECT random(-2147483648, 2147483647) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 2;
+
+-- There should be no duplicates in 1000 full-range 64-bit random() values.
+SELECT r, count(*)
+FROM (SELECT random_normal(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+
+-- There should be no duplicates in 1000 15-digit random() numeric values.
+SELECT r, count(*)
+FROM (SELECT random_normal(0, 1 - 1e-15) r
+ FROM generate_series(1, 1000)) ss
+GROUP BY r HAVING count(*) > 1;
+
+-- Expect at least one out of 2000 random values to be in the lowest and
+-- highest 1% of the range.
+SELECT (count(*) FILTER (WHERE r < -2104533975)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 2104533974)) > 0 AS has_large
+FROM (SELECT random(-2147483648, 2147483647) r FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1500000000 OR r > 1500000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000, 1500000000) r FROM generate_series(1, 2000)) ss;
+
+SELECT (count(*) FILTER (WHERE r < -9038904596117680292)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 9038904596117680291)) > 0 AS has_large
+FROM (SELECT random(-9223372036854775808, 9223372036854775807) r
+ FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1500000000000000 OR r > 1500000000000000) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1470000000000000)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1470000000000000)) > 0 AS has_large
+FROM (SELECT random(-1500000000000000, 1500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+
+SELECT count(*) FILTER (WHERE r < -1.5 OR r > 1.5) AS out_of_range,
+ (count(*) FILTER (WHERE r < -1.47)) > 0 AS has_small,
+ (count(*) FILTER (WHERE r > 1.47)) > 0 AS has_large
+FROM (SELECT random(-1.500000000000000, 1.500000000000000) r
+ FROM generate_series(1, 2000)) ss;
+
+-- Every possible value should occur at least once in 2500 random() values
+-- chosen from a range with 100 distinct values.
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-50, 49) r FROM generate_series(1, 2500));
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(123000000000, 123000000099) r
+ FROM generate_series(1, 2500));
+
+SELECT min(r), max(r), count(r) FROM (
+ SELECT DISTINCT random(-0.5, 0.49) r FROM generate_series(1, 2500));
+
+-- Check for uniform distribution using the Kolmogorov-Smirnov test.
+
+CREATE FUNCTION ks_test_uniform_random_int_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999) / 1000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() OR
+ ks_test_uniform_random_int_in_range() AS uniform_int;
+
+CREATE FUNCTION ks_test_uniform_random_bigint_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 999999999999) / 1000000000000.0 r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() OR
+ ks_test_uniform_random_bigint_in_range() AS uniform_bigint;
+
+CREATE FUNCTION ks_test_uniform_random_numeric_in_range()
+RETURNS boolean AS
+$$
+DECLARE
+ n int := 1000; -- Number of samples
+ c float8 := 1.94947; -- Critical value for 99.9% confidence
+ ok boolean;
+BEGIN
+ ok := (
+ WITH samples AS (
+ SELECT random(0, 0.999999) r FROM generate_series(1, n) ORDER BY 1
+ ), indexed_samples AS (
+ SELECT (row_number() OVER())-1.0 i, r FROM samples
+ )
+ SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
+ );
+ RETURN ok;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() OR
+ ks_test_uniform_random_numeric_in_range() AS uniform_numeric;
+
-- setseed() should produce a reproducible series of random() values.
SELECT setseed(0.5);
@@ -113,3 +268,12 @@ SET extra_float_digits = -1;
SELECT random_normal() FROM generate_series(1, 10);
SELECT random_normal(mean => 1, stddev => 0.1) r FROM generate_series(1, 10);
+
+-- Reproducible random(min, max) values.
+SELECT random(1, 6) FROM generate_series(1, 10);
+SELECT random(-2147483648, 2147483647) FROM generate_series(1, 10);
+SELECT random(-9223372036854775808, 9223372036854775807) FROM generate_series(1, 10);
+SELECT random(-1e30, 1e30) FROM generate_series(1, 10);
+SELECT random(-0.4, 0.4) FROM generate_series(1, 10);
+SELECT random(0, 1 - 1e-30) FROM generate_series(1, 10);
+SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20) n;
--
2.35.3
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-03-26 06:57 Dean Rasheed <[email protected]>
parent: Dean Rasheed <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Dean Rasheed @ 2024-03-26 06:57 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: David Zhang <[email protected]>; Pavel Stehule <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, 27 Feb 2024 at 17:33, Dean Rasheed <[email protected]> wrote:
>
> On Sat, 24 Feb 2024 at 17:10, Tomas Vondra
> >
> > I did a quick review and a little bit of testing on the patch today. I
> > think it's a good/useful idea, and I think the code is ready to go (the
> > code is certainly much cleaner than anything I'd written ...).
>
Based on the reviews so far, I think this is ready for commit, so
unless anyone objects, I will do so in a day or so.
As a quick summary, this adds a new file:
src/backend/utils/adt/pseudorandomfuncs.c
which contains SQL-callable functions that access a single shared
pseudorandom number generator, whose state is private to that file.
Currently the functions are:
random() returns double precision [moved from float.c]
random(min integer, max integer) returns integer [new]
random(min bigint, max bigint) returns bigint [new]
random(min numeric, max numeric) returns numeric [new]
random_normal() returns double precision [moved from float.c]
setseed(seed double precision) returns void [moved from float.c]
It's possible that functions to return other random distributions or
other datatypes might get added in the future, but I have no plans to
do so at the moment.
Regards,
Dean
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions to return random numbers in a given range
@ 2024-03-27 10:32 Dean Rasheed <[email protected]>
parent: Dean Rasheed <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Dean Rasheed @ 2024-03-27 10:32 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: David Zhang <[email protected]>; Pavel Stehule <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, 26 Mar 2024 at 06:57, Dean Rasheed <[email protected]> wrote:
>
> Based on the reviews so far, I think this is ready for commit, so
> unless anyone objects, I will do so in a day or so.
>
Committed. Thanks for the reviews.
Regards,
Dean
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2024-03-27 10:32 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-11 02:01 [PATCH v24 5/5] doc: Add Collation Versions section. Thomas Munro <[email protected]>
2023-12-21 17:06 Functions to return random numbers in a given range Dean Rasheed <[email protected]>
2023-12-21 17:43 ` Re: Functions to return random numbers in a given range Pavel Stehule <[email protected]>
2024-01-26 20:44 ` Re: Functions to return random numbers in a given range David Zhang <[email protected]>
2024-01-29 12:42 ` Re: Functions to return random numbers in a given range Dean Rasheed <[email protected]>
2024-01-30 12:47 ` Re: Functions to return random numbers in a given range Aleksander Alekseev <[email protected]>
2024-01-30 13:44 ` Re: Functions to return random numbers in a given range Dean Rasheed <[email protected]>
2024-02-24 17:10 ` Re: Functions to return random numbers in a given range Tomas Vondra <[email protected]>
2024-02-27 17:33 ` Re: Functions to return random numbers in a given range Dean Rasheed <[email protected]>
2024-03-26 06:57 ` Re: Functions to return random numbers in a given range Dean Rasheed <[email protected]>
2024-03-27 10:32 ` Re: Functions to return random numbers in a given range Dean Rasheed <[email protected]>
2024-01-29 12:38 ` Re: Functions to return random numbers in a given range Dean Rasheed <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox