Lists: | pgsql-bugs |
---|
From: | "Martin Edlman" <edlman(at)fortech(dot)cz> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5043: Stored procedure returning different results for same arguments |
Date: | 2009-09-08 13:30:30 |
Message-ID: | 200909081330.n88DUUJI078844@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5043
Logged by: Martin Edlman
Email address: edlman(at)fortech(dot)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;
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Martin Edlman <edlman(at)fortech(dot)cz> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5043: Stored procedure returning different results for same arguments |
Date: | 2009-09-08 13:40:52 |
Message-ID: | 162867790909080640m7eacc862u5be2aaafa2c5e298@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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 <= $2 AND contractid = $1;
$$ LANGUAGE sql;
has it same behave like plpgsql function?
regards
Pavel Stehule
2009/9/8 Martin Edlman <edlman(at)fortech(dot)cz>:
>
> The following bug has been logged online:
>
> Bug reference: 5043
> Logged by: Martin Edlman
> Email address: edlman(at)fortech(dot)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;
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Martin Edlman" <edlman(at)fortech(dot)cz> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5043: Stored procedure returning different results for same arguments |
Date: | 2009-09-08 14:21:50 |
Message-ID: | 25811.1252419710@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Martin Edlman" <edlman(at)fortech(dot)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
From: | Martin Edlman <edlman(at)fortech(dot)cz> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5043: Stored procedure returning different results for same arguments |
Date: | 2009-09-09 09:58:39 |
Message-ID: | 4AA77C4F.4090601@fortech.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
-----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-----
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Martin Edlman <edlman(at)fortech(dot)cz> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5043: Stored procedure returning different results for same arguments |
Date: | 2009-09-09 10:19:15 |
Message-ID: | 162867790909090319t2843aa3dw2f24943282743e8a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
2009/9/9 Martin Edlman <edlman(at)fortech(dot)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š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-----
>