plpgsql cursor reuse
Came across this problem when trying to assign to a variable a field from a record that could come from multiple cursors. PG throws an error - "
ERROR: type of parameter 7 (bigint) does not match that when preparing the plan (unknown)". If I make the null column in c1 null::bigint to match cursor c2, it works fine.
Where is this plan coming from? Why would it match c1 to a plan coming from c2? In reality, the two cursors in question are wildly different- a join of about 10 completely different tables. When I saw the text of the error I was a bit concerned that it was being overly flexible in matching the current cursor to another.
It errors out on the assignment to I, not the fetch. (maybe the fetch isn't actually being done until the data in r is used).
CREATE OR REPLACE FUNCTION demo.test_cursor_bug ( a IN integer ) RETURNS void AS
$BODY$
DECLARE
c1 cursor FOR SELECT 1 as shipmentid, null as olmid;
c2 cursor FOR SELECT 2 as shipmentid, 32::bigint as olmid;
r record;
i bigint;
BEGIN
IF ( a = 0 ) THEN
open c1;
fetch c1 INTO r;
close c1;
END IF;
IF ( a = 1 ) THEN
open c2;
fetch c2 INTO r;
close c2;
END IF;
i := r.olmid;
END;
$BODY$
LANGUAGE plpgsql;
select demo.test_cursor_bug(0);
select demo.test_cursor_bug(1);
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Greco
Sent: Monday, November 12, 2012 3:35 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] plpgsql cursor reuse
Came across this problem when trying to assign to a variable a field from a
record that could come from multiple cursors. PG throws an error - "
ERROR: type of parameter 7 (bigint) does not match that when preparing the
plan (unknown)". If I make the null column in c1 null::bigint to match
cursor c2, it works fine.
Where is this plan coming from? Why would it match c1 to a plan coming from
c2? In reality, the two cursors in question are wildly different- a join of
about 10 completely different tables. When I saw the text of the error I was
a bit concerned that it was being overly flexible in matching the current
cursor to another.
It errors out on the assignment to I, not the fetch. (maybe the fetch isn't
actually being done until the data in r is used).
CREATE OR REPLACE FUNCTION demo.test_cursor_bug ( a IN integer ) RETURNS
void AS
$BODY$
DECLARE
c1 cursor FOR SELECT 1 as shipmentid, null as olmid;
c2 cursor FOR SELECT 2 as shipmentid, 32::bigint as olmid;
r record;
i bigint;
BEGIN
IF ( a = 0 ) THEN
open c1;
fetch c1 INTO r;
close c1;
END IF;
IF ( a = 1 ) THEN
open c2;
fetch c2 INTO r;
close c2;
END IF;
i := r.olmid;
END;
$BODY$
LANGUAGE plpgsql;
select demo.test_cursor_bug(0);
select demo.test_cursor_bug(1);
i := r.olmid;
I'm guessing that the system evaluates the fact that since "i" is a bigint
that r.olmid must be a bigint as well. When it goes to find and retrieve
olmid at parameter position 7 it expects to find a bigint but instead finds
an undefined type with a NULL and so it throws an error.
The only solution to your example is to cast the explicit NULL to a bigint.
In your real-life situation there are possible other options but generally
speaking you need to remove the ambiguity explicitly.
The exact mechanics behind why this specific message is being thrown is
beyond me but regardless the fundamental reason and solution remain
constant. You would get a different but similar message if you avoided
cursors and used direct SQL instead. The use of cursors just causes a
slightly different ordering of operations to occur. But, it is not
"match[ing] c1 to a plan coming from c2" but rather it is matching the plan
for c1 to the declared type for "i".
David J.
Hello David,
Well, I think this is normal you can t assign null to a variable without a proper cating in your example you can do somthing like this
c1 cursor FOR SELECT 1 as shipmentid, null::bigint as olmid;
Regards
________________________________
From: David Greco <David_Greco@harte-hanks.com>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Monday, November 12, 2012 9:34 PM
Subject: [GENERAL] plpgsql cursor reuse
Came across this problem when trying to assign to a variable a field from a record that could come from multiple cursors. PG throws an error – “
ERROR: type of parameter 7 (bigint) does not match that when preparing the plan (unknown)”. If I make the null column in c1 null::bigint to match cursor c2, it works fine.
Where is this plan coming from? Why would it match c1 to a plan coming from c2? In reality, the two cursors in question are wildly different- a join of about 10 completely different tables. When I saw the text of the error I was a bit concerned that it was being overly flexible in matching the current cursor to another.
It errors out on the assignment to I, not the fetch. (maybe the fetch isn’t actually being done until the data in r is used).
CREATE OR REPLACE FUNCTION demo.test_cursor_bug ( a IN integer ) RETURNS void AS
$BODY$
DECLARE
c1 cursor FOR SELECT 1 as shipmentid, null as olmid;
c2 cursor FOR SELECT 2 as shipmentid, 32::bigint as olmid;
r record;
i bigint;
BEGIN
IF ( a = 0 ) THEN
open c1;
fetch c1 INTO r;
close c1;
END IF;
IF ( a = 1 ) THEN
open c2;
fetch c2 INTO r;
close c2;
END IF;
i := r.olmid;
END;
$BODY$
LANGUAGE plpgsql;
select demo.test_cursor_bug(0);
select demo.test_cursor_bug(1);