The following bug has been logged online:

Bug reference: 2294
Logged by: SPI_connect() failure.
Email address: jfitz@spacelink.com
PostgreSQL version: 8.1.3
Operating system: FreeBSD 6.0-RELEASE
Description: SPI_connect() fails in trigger when a Foreignkey
constraint exists on same table as trigger.
Details:

A copy of the below is also accessible at
http://clients.spacelink.com/pgsql_trigger_issue.html

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.

The PostgreSQL version is 8.1.3 running on brand new FreeBSD 6.0
installation running the generic kernel.

PostgreSQL was built from the downloadable sources on www.postgresql.org
(ie, not from FreeBSD ports, etc).

Included below are the materials to reproduce this situation.

1. Console output from 'psql' of the insert statement and the resultant
error messages/table contents.
2. pg_dump of the database in question. Contains two tables, no data, and
the relevant triggers.
3. C source code for a simple trigger that demonstrates the issue


**** 1. Output from 'psql'

testdb=# select * from
testdb=# select * from test_table1;
name | groups
------+--------
(0 rows)

testdb=# insert into test_table1 values ('abcd', 'group');
INFO: test_trigger.c(42) Trigger start
INFO: test_trigger.c(50) SPI_connect OK <-- SPI_connect() apparently
succeeded
INFO: test_trigger.c(51) Trigger end OK <-- 'C' trigger completes ok
ERROR: SPI_connect failed <-- huh? Comes from ri_trigger.c:378
testdb=#
testdb=# select * from test_table1;
name | groups
------+--------
(0 rows)

testdb=#

**** 2. pg_dump of testdb

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

--
-- Name: test_trigger(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION test_trigger() RETURNS "trigger"
AS '/tmp/test_trigger.so', 'test_trigger'
LANGUAGE c;


ALTER FUNCTION public.test_trigger() OWNER TO root;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test_table1; Type: TABLE; Schema: public; Owner: root; Tablespace:

--

CREATE TABLE test_table1 (
name character varying(30),
groups character varying(60) NOT NULL
);


ALTER TABLE public.test_table1 OWNER TO root;

--
-- Name: test_table2; Type: TABLE; Schema: public; Owner: root; Tablespace:

--

CREATE TABLE test_table2 (
groups character varying(60) NOT NULL
);


ALTER TABLE public.test_table2 OWNER TO root;

--
-- Data for Name: test_table1; Type: TABLE DATA; Schema: public; Owner:
root
--

COPY test_table1 (name, groups) FROM stdin;
\.


--
-- Data for Name: test_table2; Type: TABLE DATA; Schema: public; Owner:
root
--

COPY test_table2 (groups) FROM stdin;
\.


--
-- Name: test_table2_groups_key; Type: CONSTRAINT; Schema: public; Owner:
root; Tablespace:
--

ALTER TABLE ONLY test_table2
ADD CONSTRAINT test_table2_groups_key UNIQUE (groups);


--
-- Name: test_before; Type: TRIGGER; Schema: public; Owner: root
--

CREATE TRIGGER test_before
BEFORE INSERT ON test_table1
FOR EACH ROW
EXECUTE PROCEDURE test_trigger();


--
-- Name: test_constraint1_fk1; Type: FK CONSTRAINT; Schema: public; Owner:
root
--

ALTER TABLE ONLY test_table1
ADD CONSTRAINT test_constraint1_fk1 FOREIGN KEY (groups) REFERENCES
test_table2(groups) ON UPDATE CASCADE ON

DELETE CASCADE;


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--


**** 3. C source code for test_trigger() and the compile command lines.

root@# uname -a
FreeBSD 6.0-RELEASE FreeBSD 6.0-RELEASE #0: Thu Nov 3 09:36:13 UTC 2005


root@x64.samsco.home:/usr/obj/usr/src/sys/GENERIC i386
root@# gcc -g -fpic -c test_trigger.c -I /usr/local/pgsql/include/server
root@# gcc -shared -o test_trigger.so test_trigger.o
root@#
root@# cat m3
#!/bin/sh
#gcc -g -fpic -c mailbox_trigger.c -I /usr/local/pgsql/include/server
#gcc -shared -o mailbox_trigger.so mailbox_trigger.o config.o imap_err.o
.../lib/xmalloc.o -lcom_err

gcc -g -fpic -c test_trigger.c -I /usr/local/pgsql/include/server
gcc -shared -o test_trigger.so test_trigger.o

#
# create function trigf() returns trigger
# as 'filename'
# language C;
#
# manually load if needed: LOAD 'filename'
#

root@# gcc -g -fpic -c test_trigger.c -I /usr/local/pgsql/include/server
root@# gcc -shared -o test_trigger.so test_trigger.o
root@#
root@#
root@# cat test_trigger.c
#include "postgres.h"
#include "executor/spi.h"
#include "commands/trigger.h"

extern Datum test_trigger(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(test_trigger);



/*
--------------------------------------------------------------------------
|
X test_trigger
|
|
*/


Datum test_trigger(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple;
char *when;
bool checknull = false;
bool isnull;
int ret, i;
char *p;
char *lowerdomain;
char *sql[1024];


/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "test_trigger: not called by trigger manager");

tupdesc = trigdata->tg_relation->rd_att;

if(TRIGGER_FIRED_BEFORE(trigdata->tg_event)) {
if(TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) {

elog(INFO,"%s(%d) Trigger start", __FILE__,
__LINE__);
ret = SPI_connect();
if(ret!=SPI_OK_CONNECT) {
elog(ERROR, "%s(%d) SPI_connect failed,
ret=%d",
__FILE__, __LINE__, ret);
elog(INFO,"%s(%d) Trigger end with ERROR", __FILE__,
__LINE__);
return 0;
}
elog(INFO,"%s(%d) SPI_connect OK", __FILE__,
__LINE__);
elog(INFO,"%s(%d) Trigger end OK", __FILE__,
__LINE__);
return(PointerGetDatum(trigdata->tg_trigtuple));
}
}
}

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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