Обсуждение: BUG #2294: SPI_connect() fails in trigger when a Foreignkey constraint exists on same table as trigger.
BUG #2294: SPI_connect() fails in trigger when a Foreignkey constraint exists on same table as trigger.
От
"SPI_connect() failure."
Дата:
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)); } } }
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.
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.
>>
>
"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
"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
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 >
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
>