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

Tom -

  Yes, what your suggesting would make sense WRT the ri_triggers however it
doesn't explain the results that are actually appearing using the given code
in the bug report.

  If one compiles this code (with or without the missing SPI_finish() call)
the failure still exists.  In my opinion there may be two bugs at play.  The
original one submitted -- Why does SPI_connect() fail when the example table
constraint is in place?   The second issue would be -- if SPI_connect() does
fail for some reason in this case (legitimate or not) why does it return an
SPI_OK_CONNECT result and why is an SPI_connect failed error being emitted
from ri_trigger.c:378?

  I don't believe there is any bug in the submitted trigger code (other than
omission of the SPI_finish() cleanup call).  This submitted code is example
code created just for the purposes of demonstrating this issue.  It has no
other components (external or otherwise) and does no work other than calling
SPI_connect().  This being the case, the need for push & pop SPI calls would
be irrelevant.  Furthermore it was created based on the 'C' language trigger
example given in the 8.x documentation with the idea of creating the most
minimalist code example possible that will demonstrate the problem.

  If it would simplify things, I can modify/resubmit the but report with the
missing SPI_finish() statement corrected.  I have already corrected it here
and noted that it did not affect the issue I have reported.  Anyway, I'll be
particularly curious to see if anyone here reproduces the issue.  All needed
materials are included in my original report.  If it turns out to be a bug
in my example trigger I'll be quite glad to see what it is!

-Jim

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Jim Fitzgerald" <jfitz@spacelink.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Thursday, March 02, 2006 7:44 AM
Subject: Re: [BUGS] BUG #2294: SPI_connect() fails in trigger when a
Foreignkey constraint exists on same table as trigger.


> "Jim Fitzgerald" <jfitz@spacelink.com> writes:
>>   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.
>
> Well, no, because control isn't going to get that far before the
> mismatched SPI_connect/SPI_finish calls are noted.
>
> If your theory of the problem were correct then the RI triggers
> themselves would cause failures whenever a table had more than one
> foreign key.  I feel fairly confident that it's just a bug in your
> trigger code.  Aside from the missing SPI_finish, you might be needing
> SPI_push/SPI_pop calls if the trigger code invokes anything that might
> itself call SPI.
>
> regards, tom lane
>