Thread: SPI_ERROR_CONNECT
I am receiving a SPI_ERROR_CONNECT error. From what I'm reading I could
fix this in C using SPI_push(). How does one fix this with PL/PGSql?
Return error:
---------------------------------------------------------------------------------------------------
NOTICE: current day = 1
ERROR: SPI_connect failed: SPI_ERROR_CONNECT
CONTEXT: PL/pgSQL function "pop_tag_day_over" line 17 at FOR over
SELECT rows
********** Error **********
ERROR: SPI_connect failed: SPI_ERROR_CONNECT
SQL state: XX000
Context: PL/pgSQL function "pop_tag_day_over" line 17 at FOR over SELECT
rows
---------------------------------------------------------------------------------------------------
Here is my function:
---------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pop_tag_day_over()
RETURNS void AS
$BODY$
DECLARE
current_row RECORD;
trans_day integer;
BEGIN
trans_day := 0;
truncate table day_over;
FOR i IN 1..(extract('day'
from(last_day(process_month())))-1)::integer LOOP
execute 'CREATE OR REPLACE VIEW temp_tags_18 AS SELECT datetime,
tagnum, tagtype, vrn
FROM tag
WHERE datetime <= process_month()
AND datetime > (process_month() - 18 + ' || trans_day || ')
ORDER BY vrn, tagnum, datetime';
FOR current_row IN SELECT * from temp_tags_18_counted
LOOP
IF current_row.day_count = 1 THEN
insert into day_over (vrn,process_day) values
(current_row.vrn,(1 + trans_day) );
END IF;
END LOOP;
raise notice 'current day = %',trans_day+1;
trans_day := i;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';
---------------------------------------------------------------------------------------------------
Willem Buitendyk <willem@pcfish.ca> writes:
> ERROR: SPI_connect failed: SPI_ERROR_CONNECT
> CONTEXT: PL/pgSQL function "pop_tag_day_over" line 17 at FOR over
> SELECT rows
Hm, what PG version is this? And could we have a complete test case
not just the function? (I don't feel like trying to reverse-engineer
your tables and views...)
regards, tom lane
The problem was with the following:
FOR current_row IN SELECT * from temp_tags_18_counted
The select from the [temp_tags_18_counted] view is made up of 3 cross
joins. When I simplify and remove the joins everything works. I tried
this with some test data with only a few rows and the joins in place and
it works too.
In the production data table there are about 250K rows. Is it possible
that calls to queries are colliding here or not giving each other enough
time before being whisked around to next call in the FOR loop?
cheers,
willem
Tom Lane wrote:
> Willem Buitendyk <willem@pcfish.ca> writes:
>
>> ERROR: SPI_connect failed: SPI_ERROR_CONNECT
>> CONTEXT: PL/pgSQL function "pop_tag_day_over" line 17 at FOR over
>> SELECT rows
>>
>
> Hm, what PG version is this? And could we have a complete test case
> not just the function? (I don't feel like trying to reverse-engineer
> your tables and views...)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
Willem Buitendyk <willem@pcfish.ca> writes:
> The problem was with the following:
> FOR current_row IN SELECT * from temp_tags_18_counted
> The select from the [temp_tags_18_counted] view is made up of 3 cross
> joins. When I simplify and remove the joins everything works. I tried
> this with some test data with only a few rows and the joins in place and
> it works too.
> In the production data table there are about 250K rows. Is it possible
> that calls to queries are colliding here or not giving each other enough
> time before being whisked around to next call in the FOR loop?
No. Please provide a test case instead of speculating. And, again,
what is the PG version?
regards, tom lane
Thanks Tom, I sent you a test case. The problem has since been resolved by changing one of my functions to VOLATILE instead of IMMUTABLE. This has caught me twice now in the last few days. I hope my learning of this will be a little more IMMUTABLE :) cheers, willem PG 8.3 Tom Lane wrote: > Willem Buitendyk <willem@pcfish.ca> writes: > >> The problem was with the following: >> FOR current_row IN SELECT * from temp_tags_18_counted >> > > >> The select from the [temp_tags_18_counted] view is made up of 3 cross >> joins. When I simplify and remove the joins everything works. I tried >> this with some test data with only a few rows and the joins in place and >> it works too. >> In the production data table there are about 250K rows. Is it possible >> that calls to queries are colliding here or not giving each other enough >> time before being whisked around to next call in the FOR loop? >> > > No. Please provide a test case instead of speculating. And, again, > what is the PG version? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > >
Willem Buitendyk <willem@pcfish.ca> writes: > I sent you a test case. Thanks for the test case --- I've committed a patch: http://archives.postgresql.org/pgsql-committers/2008-02/msg00108.php > The problem has since been resolved by changing > one of my functions to VOLATILE instead of IMMUTABLE. This has caught > me twice now in the last few days. That's a fairly bad workaround (assuming that the function is a legitimate candidate to be IMMUTABLE) because it defeats potential optimizations. What I'd suggest you do instead is rethink your apparently widespread habit of whacking your view definitions around on-the-fly. This would never have worked at all before PG 8.3 (and as you can see we still have some bugs left in supporting it in 8.3 :-(). Even when it does work, there is a whole lot of frantic paddling going on just under the surface. We may sail serenely on like the swan, but not very speedily ... regards, tom lane
Tom Lane wrote: >That's a fairly bad workaround (assuming that the function is a > legitimate candidate to be IMMUTABLE) because it defeats potential > optimizations. > > What I'd suggest you do instead is rethink your apparently widespread > habit of whacking your view definitions around on-the-fly. This would > never have worked at all before PG 8.3 (and as you can see we still have > some bugs left in supporting it in 8.3 :-(). Even when it does work, > there is a whole lot of frantic paddling going on just under the > surface. We may sail serenely on like the swan, but not very speedily Yep, already started reorganizing so that I don't have to hack away at the views so much. So far I've been able to do without the functions that would only work with volatile. cheers, willem