Re: BUG #2294: SPI_connect() fails in trigger when a Foreignkey constraint exists on same table as trigger.

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" <alvherre(at)commandprompt(dot)com>
> To: "SPI_connect() failure." <jfitz(at)spacelink(dot)com>
> Cc: <pgsql-bugs(at)postgresql(dot)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.
>>
>