Les accidents ne sont pas accidentels

Est-il possible de prédire de manière fiable l'avenir, même un peu à l'avance? Parfois - tout à fait, vous avez juste besoin de beaucoup de chance ... ou d'un peu de connaissances.

Aujourd'hui, nous allons observer une séance de magie noire suivie d'une exposition, ou "Je devinerai votre aléatoire à partir de 3 lignes!" :

Un peu de magie

tst=# SELECT r random, magic(r) random_next FROM random() r;
       random       |    random_next
--------------------+--------------------
 0.3921143477755571 | 0.6377947747296489

tst=# SELECT r random, magic(r) random_next FROM random() r;
       random       |    random_next
--------------------+--------------------
 0.6377947747296489 | 0.5727554063674667

tst=# SELECT r random, magic(r) random_next FROM random() r;
       random       |    random_next
--------------------+--------------------
 0.5727554063674667 | 0.4979625995285346
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Qu'y a-t-il sous le capot?

CREATE OR REPLACE FUNCTION magic(r double precision) RETURNS double precision AS $$
  SELECT
    (
      (
        (r & x'FFFFFFFFFFFF'::bigint) * (mul & x'000000000FFF'::bigint)
      + (r & x'000FFFFFFFFF'::bigint) * (mul & x'000000FFF000'::bigint)
      + (r & x'000000FFFFFF'::bigint) * (mul & x'000FFF000000'::bigint)
      + (r & x'000000000FFF'::bigint) * (mul & x'FFF000000000'::bigint)
      + add
      ) & x'FFFFFFFFFFFF'::bigint
    )::double precision / x'1000000000000'::bigint
  FROM
    (VALUES(
      (r * x'1000000000000'::bigint)::bigint
    , x'0005deece66d'::bigint
    , x'000b'::bigint
    )) T(r, mul, add)
$$ LANGUAGE sql;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

? :

 random()



  . , ;  pgcrypto.  setseed()



  ,  random()



  .

, , " ", . " "?

github- PostgreSQL setseed



float.c:

/*
 *		setseed		- set seed for the random number generator
 */
Datum
setseed(PG_FUNCTION_ARGS)
{
	float8		seed = PG_GETARG_FLOAT8(0);
	uint64		iseed;

  // ...
  
	/* Use sign bit + 47 fractional bits to fill drandom_seed[] */
	iseed = (int64) (seed * (float8) UINT64CONST(0x7FFFFFFFFFFF));
	drandom_seed[0] = (unsigned short) iseed;
	drandom_seed[1] = (unsigned short) (iseed >> 16);
	drandom_seed[2] = (unsigned short) (iseed >> 32);
	drandom_seed_set = true;

	PG_RETURN_VOID();
}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- float8



- 48 .

:

/*
 *		drandom		- returns a random number
 */
Datum
drandom(PG_FUNCTION_ARGS)
{
	float8		result;

	/* 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 (!pg_strong_random(drandom_seed, sizeof(drandom_seed)))
		{
			TimestampTz now = GetCurrentTimestamp();
			uint64		iseed;

			/* Mix the PID with the most predictable bits of the timestamp */
			iseed = (uint64) now ^ ((uint64) MyProcPid << 32);
			drandom_seed[0] = (unsigned short) iseed;
			drandom_seed[1] = (unsigned short) (iseed >> 16);
			drandom_seed[2] = (unsigned short) (iseed >> 32);
		}
		drandom_seed_set = true;
	}
  
	/* pg_erand48 produces desired result range [0.0 - 1.0) */
	result = pg_erand48(drandom_seed);

	PG_RETURN_FLOAT8(result);
}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

pg_erand48



, - erand48.c:

/*
 * Generate a random floating-point value using caller-supplied state.
 * Values are uniformly distributed over the interval [0.0, 1.0).
 */
double
pg_erand48(unsigned short xseed[3])
{
	uint64		x = _dorand48(xseed);

	return ldexp((double) (x & UINT64CONST(0xFFFFFFFFFFFF)), -48);
}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

_dorand48:

/* These values are specified by POSIX */
#define RAND48_MULT		UINT64CONST(0x0005deece66d)
#define RAND48_ADD		UINT64CONST(0x000b)

/* POSIX specifies 0x330e's use in srand48, but the other bits are arbitrary */
#define RAND48_SEED_0	(0x330e)
#define RAND48_SEED_1	(0xabcd)
#define RAND48_SEED_2	(0x1234)

static unsigned short _rand48_seed[3] = {
	RAND48_SEED_0,
	RAND48_SEED_1,
	RAND48_SEED_2
};

/*
 * Advance the 48-bit value stored in xseed[] to the next "random" number.
 *
 * Also returns the value of that number --- without masking it to 48 bits.
 * If caller uses the result, it must mask off the bits it wants.
 */
static uint64
_dorand48(unsigned short xseed[3])
{
	/*
	 * We do the arithmetic in uint64; any type wider than 48 bits would work.
	 */
	uint64		in;
	uint64		out;

	in = (uint64) xseed[2] << 32 | (uint64) xseed[1] << 16 | (uint64) xseed[0];

	out = in * RAND48_MULT + RAND48_ADD;

	xseed[0] = out & 0xFFFF;
	xseed[1] = (out >> 16) & 0xFFFF;
	xseed[2] = (out >> 32) & 0xFFFF;

	return out;
}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

!

random()?

, , random().

""

random()



PostgreSQL- 16- (48 ) now



- "" PID :

			TimestampTz now = GetCurrentTimestamp();
			uint64		iseed;

			/* Mix the PID with the most predictable bits of the timestamp */
			iseed = (uint64) now ^ ((uint64) MyProcPid << 32);
			drandom_seed[0] = (unsigned short) iseed;
			drandom_seed[1] = (unsigned short) (iseed >> 16);
			drandom_seed[2] = (unsigned short) (iseed >> 32);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

"" setseed



.

48- , RAND48_MULT (



0x0005deece66d)



RAND48_ADD (



0x000b)



.

double



, 48 .

SQL

, random()



, , .

double precision (double) <-> bigint (uint64)

C- doubl



, uint64



- SQL . IEEE754 .

, 52 . 48- double precision



, - , , 2^48, bigint



-:

SELECT (r::double precision * x'1000000000000'::bigint)::bigint;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

SELECT r::double precision / x'1000000000000'::bigint;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

0x0005deece66d, ...

:  bigint  
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- 48- , 35- - 64 .

64 - 48! "" 12- ( 16- - ) :

         12 12 12 12
48 bit =  A  B  C  D
        * E  F  G  H
--------------------
        |AH BH CH DH = A B C D * 0 0 0 H
      AG|BG CG DG    = 0 B C D * 0 0 G 0
   AF BF|CF DF       = 0 0 C D * 0 F 0 0
AE BE CE|DE          = 0 0 0 D * E 0 0 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, 0x000b, , - .