Thread: BUG #5043: Stored procedure returning different results for same arguments
BUG #5043: Stored procedure returning different results for same arguments
From
"Martin Edlman"
Date:
The following bug has been logged online: Bug reference: 5043 Logged by: Martin Edlman Email address: edlman@fortech.cz PostgreSQL version: 8.2.0 Operating system: Linux (RHEL 4.4) Description: Stored procedure returning different results for same arguments Details: I have a stored procedure (SP) get_schemebind_date(int, date) which looks up a date of validity of a payment scheme. Table schemebind contains valid_from (date), schemeid (int), contractid (int). There are these records in schemebind '2008-11-01', 123, 1004 '2009-09-01', 456, 1004 When I call "select get_schemebind_date(1004,'2009-09-01')" from psql I get correct result, which is 2009-09-01. But when I call it from within another sp (let's call it xfunc(int, date, varchar), I get wrong result 2008-11-01. The date passed to xfunc() is then passed to get_schemebind_date(). I enabled RAISE NOTICE to see the parameters and the result, in case I call get_schemebind_date() from console I see NOTICE: get_schemebind_date(1004, 2009-09-01) = 2009-09-01 In case get_schemebind_date() is called from xfunc() I see NOTICE: get_schemebind_date(1004, 2009-09-01) = 2008-11-01 I really don't understand it. All parameters are of type date, column valid_from is of type date as well. If you need full code of xfunc, I can send it to you. CREATE OR REPLACE FUNCTION get_schemebind_date(integer, date) RETURNS date AS $BODY$ DECLARE rec RECORD; con ALIAS FOR $1; dat ALIAS FOR $2; BEGIN SELECT max(valid_from) AS vf INTO rec FROM schemebind WHERE valid_from <= dat AND contractid = con; RAISE NOTICE 'get_schemebind_date(%, %) = %', con, dat, rec.vf; IF FOUND THEN RETURN rec.vf; END IF; RETURN NULL; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION get_schemebind_date(integer, date) OWNER TO postgres;
Re: BUG #5043: Stored procedure returning different results for same arguments
From
Pavel Stehule
Date:
Hello please send function xfunc. Your code looks well. please try sql function CREATE OR REPLACE FUNCTION get_schemebind_date(integer, date) RETURNS date AS $$ SELECT max(valid_from) FROM schemebind WHERE valid_from <=3D $2 AND contractid =3D $1; $$ LANGUAGE sql; has it same behave like plpgsql function? regards Pavel Stehule 2009/9/8 Martin Edlman <edlman@fortech.cz>: > > The following bug has been logged online: > > Bug reference: =C2=A0 =C2=A0 =C2=A05043 > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Martin Edlman > Email address: =C2=A0 =C2=A0 =C2=A0edlman@fortech.cz > PostgreSQL version: 8.2.0 > Operating system: =C2=A0 Linux (RHEL 4.4) > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Stored procedure returning differ= ent results for same > arguments > Details: > > I have a stored procedure (SP) get_schemebind_date(int, date) which looks= up > a date of validity of a payment scheme. Table schemebind contains valid_f= rom > (date), schemeid (int), contractid (int). > > There are these records in schemebind > '2008-11-01', 123, 1004 > '2009-09-01', 456, 1004 > > When I call "select get_schemebind_date(1004,'2009-09-01')" from psql I g= et > correct result, which is 2009-09-01. > But when I call it from within another sp (let's call it xfunc(int, date, > varchar), I get wrong result 2008-11-01. The date passed to xfunc() is th= en > passed to get_schemebind_date(). > > I enabled RAISE NOTICE to see the parameters and the result, in case I ca= ll > get_schemebind_date() from console I see > NOTICE: get_schemebind_date(1004, 2009-09-01) =3D 2009-09-01 > In case get_schemebind_date() is called from xfunc() I see > NOTICE: get_schemebind_date(1004, 2009-09-01) =3D 2008-11-01 > > I really don't understand it. All parameters are of type date, column > valid_from is of type date as well. > > If you need full code of xfunc, I can send it to you. > > CREATE OR REPLACE FUNCTION get_schemebind_date(integer, date) > =C2=A0RETURNS date AS > $BODY$ > DECLARE > =C2=A0 =C2=A0 =C2=A0 =C2=A0rec RECORD; > =C2=A0 =C2=A0 =C2=A0 =C2=A0con ALIAS FOR $1; > =C2=A0 =C2=A0 =C2=A0 =C2=A0dat ALIAS FOR $2; > BEGIN > =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT max(valid_from) AS vf > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INTO rec > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM schemebind > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE valid_from <= =3D dat AND contractid =3D con; > =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE 'get_schemebind_date(%, %) =3D %'= , con, dat, rec.vf; > =C2=A0 =C2=A0 =C2=A0 =C2=A0IF FOUND THEN > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURN rec.vf; > =C2=A0 =C2=A0 =C2=A0 =C2=A0END IF; > =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURN NULL; > END > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > ALTER FUNCTION get_schemebind_date(integer, date) OWNER TO postgres; > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
"Martin Edlman" <edlman@fortech.cz> writes: > When I call "select get_schemebind_date(1004,'2009-09-01')" from psql I get > correct result, which is 2009-09-01. > But when I call it from within another sp (let's call it xfunc(int, date, > varchar), I get wrong result 2008-11-01. The only thought that comes to mind is that maybe you've got two tables named schemebind in different schemas, and the other function is changing the search_path? However, 8.2.0 is exceedingly out of date and full of known bugs. Really the first thing you ought to do is update to 8.2.latest. regards, tom lane
Re: BUG #5043: Stored procedure returning different results for same arguments
From
Martin Edlman
Date:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello, > please send function xfunc. Your code looks well. > please try sql function > ... > has it same behave like plpgsql function? Yes, result is the same - it returns 2008-11-01 I made further investigation - it seems the problem is here between the keyboard and the chair :-) As I wrote an answer for you I realized the problem. The xfunc() and therefore get_schemebind_date() is called from a trigger AFTER DELETE ON schemebind. So the trigger deletes the '2009-09-01' record, get_schemebind_date() then returns correct result '2008-11-01'. But xfunc() then fails and the trigger operation is rolled back and I see the '2009-09-01' record again... Sorry guys for bothering you and taking your time. Regards, - -- Martin Edlman Fortech, spol. s r.o, Ropkova 51, 57001 Litomyšl Public GPG key: http://edas.visaci.cz/#gpgkeys -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkqnfE4ACgkQqmMakYm+VJ8iswCggwTcu9pZQOaAjAIjW0D22kTs HYIAni3mLdNilwgxNeQsGFxNogBg6OCL =ZVLv -----END PGP SIGNATURE-----
Re: BUG #5043: Stored procedure returning different results for same arguments
From
Pavel Stehule
Date:
2009/9/9 Martin Edlman <edlman@fortech.cz>: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hello, > >> please send function xfunc. Your code looks well. >> please try sql function >> ... >> has it same behave like plpgsql function? > > Yes, result is the same - it returns 2008-11-01 > > I made further investigation - it seems the problem is here between the > keyboard and the chair :-) > > As I wrote an answer for you I realized the problem. The xfunc() and > therefore get_schemebind_date() is called from a trigger AFTER DELETE ON > schemebind. So the trigger deletes the '2009-09-01' record, > get_schemebind_date() then returns correct result '2008-11-01'. But > xfunc() then fails and the trigger operation is rolled back and I see > the '2009-09-01' record again... > Sorry guys for bothering you and taking your time. v pohode :) Pavel > > Regards, > - -- > Martin Edlman > Fortech, spol. s r.o, > Ropkova 51, 57001 Litomy=C5=A1l > Public GPG key: http://edas.visaci.cz/#gpgkeys > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (GNU/Linux) > Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkqnfE4ACgkQqmMakYm+VJ8iswCggwTcu9pZQOaAjAIjW0D22kTs > HYIAni3mLdNilwgxNeQsGFxNogBg6OCL > =3DZVLv > -----END PGP SIGNATURE----- >