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@commandprompt.com>
> To: "SPI_connect() failure." <jfitz@spacelink.com>
> 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.
>>
>