[BUGS] BUG #14737: Wrong PL/pgSQL behaviour - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14737
Logged by:          Frank Gard
Email address:      frank.von.postgresql.org@familie-gard.de
PostgreSQL version: 9.6.3
Operating system:   Debian GNU/Linux
Description:

As an example for my database programming lecture, I invented the following
PL/pgSQL function:
DROP TYPE IF EXISTS tp_histogramm CASCADE;
CREATE TYPE tp_histogramm AS  (   wert    INTEGER,   absolut INTEGER,   relativ DECIMAL( 7, 5 )
);

-- Variante 1 (funktioniert leider nicht korrekt, siehe Erläuterung im unten
stehenden Kommentar):
CREATE OR REPLACE FUNCTION fn_zufall(   p_von    INTEGER DEFAULT 1,   p_bis    INTEGER DEFAULT 6,   p_anzahl INTEGER
DEFAULT10000
 
)
RETURNS SETOF tp_histogramm
LANGUAGE PLpgSQL
AS $body$
DECLARE   c_create     CONSTANT VARCHAR := 'CREATE TEMPORARY TABLE IF NOT EXISTS
ttb_histogramm ( wert INTEGER, anzahl INTEGER ) ON COMMIT DROP';   c_drop       CONSTANT VARCHAR := 'DROP TABLE IF
EXISTSttb_histogramm 
CASCADE';   c_histogramm CURSOR FOR       SELECT * FROM ttb_histogramm ORDER BY wert   ;   v_ergebnis
tp_histogramm;
BEGIN   EXECUTE c_drop;   EXECUTE c_create;   FOR v_wert IN p_von .. p_bis LOOP       INSERT INTO ttb_histogramm( wert,
anzahl) VALUES ( v_wert, 0 );   END LOOP;   FOR v_anzahl IN 1 .. p_anzahl LOOP       UPDATE ttb_histogramm
SET              anzahl = anzahl + 1           WHERE               wert = p_von + FLOOR( ( 1 + p_bis - p_von ) *
RANDOM())       ;   END LOOP;   FOR r_histogramm IN c_histogramm LOOP       v_ergebnis.wert := r_histogramm.wert;
v_ergebnis.absolut:= r_histogramm.anzahl;       v_ergebnis.relativ := ROUND( CAST( r_histogramm.anzahl AS NUMERIC ) 
/ p_anzahl, 5 );       RETURN NEXT v_ergebnis;   END LOOP;   EXECUTE c_drop;   RETURN;
END;
$body$;

When calling
SELECT SUM( absolut ) FROM fn_zufall();
I'd expect PostgreSQL giving me the number of iterations, in this case
10000. Similarly, SELECT SUM( absolut ) FROM fn_zufall( p_anzahl := 100 );
should always give 100. Unfortunately this is not the case. When calling it
multiple times, it returns numbers smaller and greater, and always different
values for each call. Very strange (to me)!!!

When I change my function a little bit, writing the random number into an
INTEGER variable, and using this variable within my UPDATE statement,
everything works fine:

CREATE OR REPLACE FUNCTION fn_zufall(   p_von    INTEGER DEFAULT 1,   p_bis    INTEGER DEFAULT 6,   p_anzahl INTEGER
DEFAULT10000
 
)
RETURNS SETOF tp_histogramm
LANGUAGE PLpgSQL
AS $body$
DECLARE   c_create     CONSTANT VARCHAR := 'CREATE TEMPORARY TABLE IF NOT EXISTS
ttb_histogramm ( wert INTEGER, anzahl INTEGER ) ON COMMIT DROP';   c_drop       CONSTANT VARCHAR := 'DROP TABLE IF
EXISTSttb_histogramm 
CASCADE';   c_histogramm CURSOR FOR       SELECT * FROM ttb_histogramm ORDER BY wert   ;   v_ergebnis
tp_histogramm;  v_zufall              INTEGER;
 
BEGIN   EXECUTE c_drop;   EXECUTE c_create;   FOR v_wert IN p_von .. p_bis LOOP       INSERT INTO ttb_histogramm( wert,
anzahl) VALUES ( v_wert, 0 );   END LOOP;   FOR v_anzahl IN 1 .. p_anzahl LOOP       v_zufall := p_von + FLOOR( ( 1 +
p_bis- p_von ) * RANDOM() );       UPDATE ttb_histogramm           SET               anzahl = anzahl + 1
WHERE              wert = v_zufall       ;   END LOOP;   FOR r_histogramm IN c_histogramm LOOP       v_ergebnis.wert :=
r_histogramm.wert;      v_ergebnis.absolut := r_histogramm.anzahl;       v_ergebnis.relativ := ROUND( CAST(
r_histogramm.anzahlAS NUMERIC ) 
/ p_anzahl, 5 );       RETURN NEXT v_ergebnis;   END LOOP;   EXECUTE c_drop;   RETURN;
END;
$body$;

Now, SELECTs like the ones mentioned above work as expected.

I tried PostgreSQL in various versions (including 10beta1) and on several
versions of Debian GNU/Linux, but the result is always the same. Not using
"ON COMMIT DROP" doesn't help. Also, I tried to avoid the EXECUTE statements
within PL/pgSQL by creating the (temporary or "normal") table outside the
function before using the stored procedure, but no luck.

So, I believe this behaviour to be a bug within PostgreSQL, isn't it?

Cheers,
Frank.

P.S.: Thanks to Benjamin Mertens, one of my students, who discovered the
problem.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs