Hi Alvaro -
Yes, your right that SPI_finish() should be called. It was being called
in my original program where this issue was discovered. I did not include
a call to SPI_finish() in my bug exercising example just to eliminate
extraneous stuff. I have just modified the submitted test_trigger.c
program
to include SPI_finish() at the end and verified that it does not affect the
results of the bug that I am reporting.
It is notable, however, that Postgres is not emitting the following:
WARNING: transaction left non-empty SPI stack
HINT: Check for missing "SPI_finish" calls.
.. with my test_trigger.c. If that test_trigger.c is completing without
calling SPI_finish() (which it doesn't) those messages should have
appeared. This may be another issue, or related in some way.. ??
Also, since reporting that issue, I have confirmed the same behavior on
7.4.2 using the same test scenerio.
Thanks
-Jim
----- Original Message -----
From: "Alvaro Herrera" <[EMAIL PROTECTED]>
To: "SPI_connect() failure." <[EMAIL PROTECTED]>
Cc: <pgsql-bugs@postgresql.org>
Sent: Wednesday, March 01, 2006 10:57 AM
Subject: Re: [BUGS] BUG #2294: SPI_connect() fails in trigger when a
Foreignkey constraint exists on same table as trigger.
SPI_connect() failure. wrote:
SPI_connect() throws "ERROR: SPI_connect failed" message (from
backend/utils/adt/ri_trigger.c:378) when called from (at least) a
before
insert trigger on a table which also contains a foreign key constraint.
The
exit from the trigger function is inconsistent. This error message is
emitted from ri_trigger.c but the return result
from SPI_connect() in the trigger is SPI_OK_CONNECT. The insert
operation
does not commit to the database.
Do you call SPI_finish() in your trigger? You should not leave the
SPI connection open.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
