From: George Neuner <gneuner2@comcast.net>
To: pgsql-performance@postgresql.org
Subject: help: function failing
Date: Thu, 02 Oct 2014 19:00:58 -0400
Message-ID: <542DD92A.9000305@comcast.net> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,
I am using Postgresql 9.3.5 on Ubuntu and I have a sudden, unexplained
failure in a function that has been working for a long time.
--------------- code ----------------
CREATE OR REPLACE FUNCTION gen_random()
RETURNS double precision AS
$BODY$
DECLARE
num float8 := 0;
den float8 := 281474976710655; -- 0xFFFFFFFFFFFF
bytes bytea[6];
BEGIN
-- get random bytes from crypto module
bytes := ext.gen_random_bytes(6);
-- assemble a double precision value
num := num + get_byte( bytes, 0 );
FOR i IN 1..5 LOOP
num := num * 256;
num := num + get_byte( bytes, i );
END LOOP;
-- normalize value to range 0.0 .. 1.0
RETURN num / den;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
--------------- code ----------------
The error is:
ERROR: array value must start with "{" or dimension information
SQL state: 22P02
Context: PL/pgSQL function gen_random() line 8 at assignment
which, if I'm counting correctly, is
bytes := ext.gen_random_bytes(6);
If I comment out that line, it then tells me get_byte() is undefined,
which should be impossible because it's built in.
This gen_random() function is in public, the pgcrypto function
gen_random_bytes() is in a separate utility schema "ext". This is in a
test database which I am in process of modifying, but it works perfectly
when dumped and restored to a different computer. This gen_random()
function - and its environment - has been working in multiple systems
for quite a while.
I suspect that the Postgresql installation somehow has been hosed and
that I'm looking at a reinstall, but I have no idea how I managed it.
I'd like to know what happened so I can (try to) avoid it going
forward. There haven't been any recent system updates, and AFAIK there
haven't been any crashes either. Occasionally pgAdmin3 does hang up,
but that happens very infrequently and has occurred on all the working
systems as well. I have been adding new tables and functions to the
public schema on this test system, but I haven't touched anything that
was already working.
It seems like Postgresql just snapped. Any ideas? Anything in
particular I might look at for a clue?
Thanks,
George
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gneuner2@comcast.net
Subject: Re: help: function failing
In-Reply-To: <542DD92A.9000305@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox