Thread: Postgresql 7.3.2-3 and 7.3.3-4 freeze

Hi, first of all i don't speak english very good. I make a function in pl/p=
gsql, that cause a denial of service. The function has a simple select. If =
anyone can help with this.

create table "Users" (
"Usu_ID" int, "Usu_Nick" varchar(32), "Usu_Apellido" varchar(50), "Usu_Nomb=
re" varchar(50),
"Usu_Password" varchar(32), "Usu_Email" varchar(64), "Pai_ID" varchar(2), "=
Usu_FechaNac" date,
"Sex_ID" smallint, "Usu_TipoDoc" varchar(20), "Usu_NumDoc" varchar(20), "Us=
u_Direccion" varchar(50),
"Usu_CodPostal" varchar(16), "Usu_Ciudad" varchar(35), "Usu_Estado" varchar=
(35), "Ocu_ID" smallint,
"Ind_ID" smallint, "Usu_FechaInscrip" timestamp with time zone, "Usu_Codigo=
" varchar(8),
"Usu_Observ" varchar(100), "Usu_Jue_ID" smallint, "Usu_Newsletter" varchar(=
1), "Usu_Activo" varchar(1),
"Usu_GMT2" varchar(3), "Fmt_ID" smallint, "Usu_Ocu_Other" varchar(50), "Usu=
_Ind_Other" varchar(50),
"Idi_ID" smallint);

CREATE FUNCTION alta_usuario(varchar, varchar, varchar, varchar, varchar, v=
archar, smallint, smallint, date, smallint, varchar, varchar, smallint, var=
char, varchar, varchar, varchar, varchar, smallint, varchar, smallint, varc=
har) RETURNS int4 AS '

DECLARE
xUsu_Nick ALIAS FOR $1;
xUsu_Apellido ALIAS FOR $2;
xUsu_Nombre ALIAS FOR $3;
xUsu_Password ALIAS FOR $4;
xUsu_Email ALIAS FOR $5;
xPai_ID ALIAS FOR $6;
xFmt_ID ALIAS FOR $7;
xIdi_ID ALIAS FOR $8;
xUsu_FechaNac ALIAS FOR $9;
xSex_ID ALIAS FOR $10;
xUsu_Codigo ALIAS FOR $11;
xUsu_Newsletter ALIAS FOR $12;
xUsu_Jue_ID ALIAS FOR $13;
xUsu_GMT ALIAS FOR $14;
xUsu_Direccion ALIAS FOR $15;
xUsu_CodPostal ALIAS FOR $16;
xUsu_Ciudad ALIAS FOR $17;
xUsu_Estado ALIAS FOR $18;
xOcu_ID ALIAS FOR $19;
xUsu_Ocu_Other ALIAS FOR $20;
xInd_ID ALIAS FOR $21;
xUsu_Ind_Other ALIAS FOR $22;

Comprueba int4;
BEGIN
IF xUsu_Nick IS NULL THEN
 RETURN 0;
END IF;
Comprueba =3D (SELECT "Usu_ID" FROM "Users" WHERE LOWER("Usu_Nick") =3D LOW=
ER(xUsu_Nick) );
IF Comprueba IS NOT NULL THEN
 RETURN 0;
END IF;

RETURN 1;

END;'  LANGUAGE 'plpgsql';

select alta_usuario('cerito','a','a','a','a@a.com','AR',1,1,'1981-07-24',1,=
'a','a',1,'-3','a','a','a','a', 3, '', 4, '');

Regard,
Tavella Gaston.=

Instead of

Comprueba = (SELECT "Usu_ID" FROM "Users" WHERE LOWER("Usu_Nick") =
LOWER(xUsu_Nick) );

use

SELECT "Usu_ID"
    INTO Comprueba
    FROM "Users"
WHERE LOWER("Usu_Nick") = LOWER(xUsu_Nick)

Rick

"Admin Gaston" <admin@intergamesclub.com> writes:
> CREATE FUNCTION alta_usuario(varchar, varchar, varchar, varchar, varchar, v=
> archar, smallint, smallint, date, smallint, varchar, varchar, smallint, var=
> char, varchar, varchar, varchar, varchar, smallint, varchar, smallint, varc=
> har) RETURNS int4 AS '
> ...
> select alta_usuario('cerito','a','a','a','a@a.com','AR',1,1,'1981-07-24',1,=
> 'a','a',1,'-3','a','a','a','a', 3, '', 4, '');

This function declaration does not match the arguments you are passing:
the integer constants are int4 which does not implicitly convert to
int2.  So you need to declare the function to take integer arguments,
or else explicitly cast all the integer constants to smallint.

The reason it seems to freeze up is a known bug: when there's no match
to a function call, parse_func.c takes time proportional to
2-to-the-number-of-arguments to decide it can't find a match.  (If you
waited long enough, you'd get a "no such function" complaint.)  There is
a fix for that in CVS; it will appear in 7.3.4.

            regards, tom lane