Re: 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