The following bug has been logged online:

Bug reference: 2294
Logged by: SPI_connect() failure.
Email address: jfitz (AT) spacelink (DOT) 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 (AT) x64 (DOT) 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);



/*
--------------------------------------------------------------------------


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:

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

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



---------------------------(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 (AT) spacelink (DOT) com> writes:
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 (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

"Jim Fitzgerald" <jfitz (AT) spacelink (DOT) com> writes:
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=#

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 -

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



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

http://archives.postgresql.org

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



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

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