SEO

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.
>>
>


---------------------------(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

"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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Jim Fitzgerald" <jfitz@spacelink.com> writes:
> 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.

It entirely does, since the given code is missing SPI_finish().
Moreover, adding the SPI_finish() makes it work, according to my
testing.

With code as given:

regression=# insert into test_table1 values ('abcd', 'group');
INFO: t2294.c(35) Trigger start
INFO: t2294.c(43) SPI_connect OK
INFO: t2294.c(44) Trigger end OK
ERROR: SPI_connect failed
regression=#

With SPI_finish() added just before return statement:

regression=# insert into test_table1 values ('abcd', 'group');
INFO: t2294.c(35) Trigger start
INFO: t2294.c(43) SPI_connect OK
INFO: t2294.c(44) Trigger end OK
ERROR: insert or update on table "test_table1" violates foreign key constraint "test_constraint1_fk1"
DETAIL: Key (groups)=(group) is not present in table "test_table2".
regression=# insert into test_table2 values('group');
INSERT 0 1
regression=# insert into test_table1 values ('abcd', 'group');
INFO: t2294.c(35) Trigger start
INFO: t2294.c(43) SPI_connect OK
INFO: t2294.c(44) Trigger end OK
INSERT 0 1
regression=#

> If one compiles this code (with or without the missing SPI_finish() call)
> the failure still exists.

I think you had some pilot error in your testing ... perhaps forgetting
to reload the shared library after recompiling?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom -

Indeed, its entirely possible I forgot to reLOAD the library, especially
given that it appears to work for you with this correction. I'll give it
another go in a clean environment and see how it goes.

Thanks
-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 2:55 PM
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:
>> 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.
>
> It entirely does, since the given code is missing SPI_finish().
> Moreover, adding the SPI_finish() makes it work, according to my
> testing.
>
> With code as given:
>
> regression=# insert into test_table1 values ('abcd', 'group');
> INFO: t2294.c(35) Trigger start
> INFO: t2294.c(43) SPI_connect OK
> INFO: t2294.c(44) Trigger end OK
> ERROR: SPI_connect failed
> regression=#
>
> With SPI_finish() added just before return statement:
>
> regression=# insert into test_table1 values ('abcd', 'group');
> INFO: t2294.c(35) Trigger start
> INFO: t2294.c(43) SPI_connect OK
> INFO: t2294.c(44) Trigger end OK
> ERROR: insert or update on table "test_table1" violates foreign key
> constraint "test_constraint1_fk1"
> DETAIL: Key (groups)=(group) is not present in table "test_table2".
> regression=# insert into test_table2 values('group');
> INSERT 0 1
> regression=# insert into test_table1 values ('abcd', 'group');
> INFO: t2294.c(35) Trigger start
> INFO: t2294.c(43) SPI_connect OK
> INFO: t2294.c(44) Trigger end OK
> INSERT 0 1
> regression=#
>
>> If one compiles this code (with or without the missing SPI_finish()
>> call)
>> the failure still exists.
>
> I think you had some pilot error in your testing ... perhaps forgetting
> to reload the shared library after recompiling?
>
> regards, tom lane
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org