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:
Show quoted text
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 rowsHm, 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:
Show quoted text
Willem Buitendyk <willem@pcfish.ca> writes:
The problem was with the following:
FOR current_row IN SELECT * from temp_tags_18_countedThe 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