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

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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jim Fitzgerald" <jfitz(at)spacelink(dot)com>
Cc: <pgsql-bugs(at)postgresql(dot)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(at)spacelink(dot)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
>