Обсуждение: help: function failing
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
On Thu, Oct 2, 2014 at 4:00 PM, George Neuner <gneuner2@comcast.net> wrote: > --------------- 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); Guessing on the name of ext.gen_random_bytes(6) it returns a value that is incompatible with bytea[] array representation time from time, so take a closer look at ext.gen_random_bytes() first. You can test the case using DO block. > If I comment out that line, it then tells me get_byte() is undefined, > which should be impossible because it's built in. Feels like somewhere inside ext.gen_random_bytes() you set a search_path that allows to see get_byte() and the search_path that was set before the gen_random() call doesn't allow it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray.ru@gmail.com
On 10/07/2014 04:41 PM, Sergey Konoplev wrote: > On Thu, Oct 2, 2014 at 4:00 PM, George Neuner <gneuner2@comcast.net> wrote: >> --------------- 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); > Guessing on the name of ext.gen_random_bytes(6) it returns a value > that is incompatible with bytea[] array representation time from time, > so take a closer look at ext.gen_random_bytes() first. You can test > the case using DO block. > >> If I comment out that line, it then tells me get_byte() is undefined, >> which should be impossible because it's built in. > Feels like somewhere inside ext.gen_random_bytes() you set a > search_path that allows to see get_byte() and the search_path that was > set before the gen_random() call doesn't allow it. > Why does this code want an array of byteas? It looks like the code thinks bytea[6] is a declaration of a bytea of length 6, which of course it is not. Shouldn't it just be declared as: bytes bytea; ? Oh, and pgsql-performance is completely the wrong forum for this query. usage questions should be on pgsql-general. cheers andrew