> > For the real functions which I use, instead of
>
> > query = ''SELECT * FROM usno'';
>
> > I have
>
> > query = my_C_function(some_args);
>
> Oh? I'd make a small side bet that the underlying error is in your C
> function --- possibly it's tromping on some data structure and the
> damage doesn't have an effect till later. If you can demonstrate the
> problem without using any custom C functions then I'd be interested to
> see a test case.
I want to clarify, that I have a problem even without my C functions!!
And show the full exact(but long) test case, which I performed just now
specially.
I begin from table usno with 500 millions records
wsdb=# \d usno
Table "public.usno"
Column | Type | Modifiers
--------+--------+-----------
ra | real |
dec | real |
bmag | real |
rmag | real |
ipix | bigint |
errbox | box |
Indexes:
"box_ind" rtree (errbox)
"ipix_ind" btree (ipix)
"radec_ind" btree (ra, "dec")
The declaration of the functions:
CREATE OR REPLACE FUNCTION xxx(refcursor) RETURNS refcursor AS '
DECLARE query varchar;
BEGIN
query = ''SELECT * FROM usno'';
OPEN $1 FOR EXECUTE query;
RETURN $1;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION yyy() RETURNS SETOF usno AS '
DECLARE rec record;
DECLARE cur refcursor;
BEGIN
cur=xxx(''curs_name'');
LOOP
FETCH cur into rec;
EXIT WHEN NOT FOUND;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
wsdb=# \i q3c.sql
CREATE FUNCTION
CREATE FUNCTION
wsdb=# select * from yyy();
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "yyy" line 10 at return next
We see the error
#############################################
Now with q3c table instead of unso
wsdb=# \d q3c
Table "public.q3c"
Column | Type | Modifiers
--------+--------+-----------
ra | real |
dec | real |
bmag | real |
rmag | real |
ipix | bigint |
errbox | box |
Indexes:
"box_ind1" rtree (errbox)
"ipix_ind1" btree (ipix)
"radec_ind1" btree (ra, "dec")
That table is not empty but filled by random numbers
wsdb=# select * from q3c;
ra | dec | bmag | rmag | ipix | errbox
----+-----+------+------+------+-------------
3 | 3 | 4 | 5 | 55 | (5,6),(3,4)
4 | 5 | 6 | 5 | 33 | (3,4),(1,2)
(2 rows)
Now the changed functions (notice, the only difference is
replacing all occurencies of "usno" to "q3c")
CREATE OR REPLACE FUNCTION xxx(refcursor) RETURNS refcursor AS '
DECLARE query varchar;
BEGIN
query = ''SELECT * FROM q3c'';
OPEN $1 FOR EXECUTE query;
RETURN $1;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION yyy() RETURNS SETOF q3c AS '
DECLARE rec record;
DECLARE cur refcursor;
BEGIN
cur=xxx(''curs_name'');
LOOP
FETCH cur into rec;
EXIT WHEN NOT FOUND;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
wsdb=# drop FUNCTION yyy();
DROP FUNCTION
wsdb=# \i q3c.sql
CREATE FUNCTION
CREATE FUNCTION
wsdb=# select * from yyy();
ra | dec | bmag | rmag | ipix | errbox
----+-----+------+------+------+-------------
3 | 3 | 4 | 5 | 55 | (5,6),(3,4)
4 | 5 | 6 | 5 | 33 | (3,4),(1,2)
(2 rows)
We don't see the error. But the only change was the change from one big
table to a smaller one with the precisely same structure.
###########################################
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
