How can I generate a random bytea

Question

I would like to be able to generate random bytea fields of arbitrary length (<1Gb) for populating test data.

What is the best way of doing this?

Answer 1

Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use:

CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
    SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
    FROM generate_series(1, $1);
$body$
LANGUAGE 'sql'
VOLATILE
SET search_path = 'pg_catalog';

It's a simple SQL function that's cheaper to call than PL/PgSQL.

The difference in performance due to the changed aggregation method is immense for larger bytea values. Though the original function is actually up to 3x faster for sizes < 50 bytes, this one scales much better for larger values.

Or use a C extension function:

I've implemented a random bytea generator as a simple C extension function. It's in my scrapcode repository on GitHub. See the README there.

It nukes the performance of the above SQL version:

regress=# \a
regress=# \o /dev/null
regress=# \timing on
regress=# select random_bytea(2000000);
Time: 895.972 ms
regress=# drop function random_bytea(integer);
regress=# create extension random_bytea;
regress=# select random_bytea(2000000);
Time: 24.126 ms

Answer 2

testbed:

begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;

function to return random binary strings:

create function random_bytea(p_length in integer)
       returns bytea language plpgsql set search_path to 'stack' as $$
declare
  o bytea := '';
begin 
  for i in 1..p_length loop
    o := o||decode(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0'), 'hex');
  end loop;
  return o;
end;$$;

output examples:

select random_bytea(2);
/*
 random_bytea
--------------
 \xc61a
*/
select random_bytea(10);
/*
      random_bytea
------------------------
 \x8ce6359d2a30c75f0c0a

*/
select random_bytea(100);
/*
                                                                                                random_bytea
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 \x6333f8ea12b0990ac0c2d86ae64e5fa01af57ce01008c745a6f2756e518278b5b6709fc82138d3e1fbab4ce1faac8114a1fef5b107bcf7adae6d1b00f094b5a604546e268d410888ed546ae700ecfca2eaf154f2ae4b9f5db8bb5da84f134e5468bd7bf5
*/

remove testbed:

rollback;