Hi,
I have written a couple of trigger functions in C that utilise the SPI
interface. They are both row level triggers, one a before trigger and
one an after trigger.
If the triggers are called with an update statement that only affects
one row then both are excecuted correctly and without error. But if I
try to update multiple rows then this fails.
The before trigger is excecuted and returns, but before the after
trigger is excecuted the server terminates the connection. Looking in
the logs then it shows the server crashing with a signal 11 (SIGSEGV).
The triggers are compiled / run on SUSE 9.3, Postgres 8.03 using gcc
version 3.3.5 20050117
The output is shown below:
INFO: Cleanup: EXIT
INFO: Cleanup: EXIT 2
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The seccond INFO line is prited directly beore the return statement at
the end of the first trigger.
Any information or ideas would be greatly appreciated.
Many Thanks
Chris Coleman
The code for the triggers is below:
(I have removed most of the app specific code from the triggers - but
the resutls are identical wether it is present or not.)
/* SQL */
CREATE OR REPLACE FUNCTION trig_cleanupexistingrelationships()
RETURNS "trigger" AS
'$libdir/chrisc/trig_cleanupexistingrelationships.so',
'trig_cleanupexistingrelationships'
LANGUAGE 'c' VOLATILE;
ALTER FUNCTION trig_cleanupexistingrelationships() OWNER TO postgres;
CREATE OR REPLACE FUNCTION trig_calculatenewrelationships()
RETURNS "trigger" AS
'$libdir/chrisc/trig_calculatenewrelationships.so',
'trig_calculatenewrelationships'
LANGUAGE 'c' VOLATILE;
ALTER FUNCTION trig_calculatenewrelationships() OWNER TO postgres;
/////////////////////////////////////////////
// THE BEFORE TRIGGER
//
/* Prototypes*/
extern Datum trig_cleanupexistingrelationships(PG_FUNCTION_ARGS);
/* Implementation */
PG_FUNCTION_INFO_V1(trig_cleanupexistingrelationships);
Datum
trig_cleanupexistingrelationships(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple, newtuple, oldtuple;
int64 nResourceId, nNewParentId, nOldParentId, nNewResourceId;
bool bIsNull;
/* make sure it's called as a trigger at all */
if(!CALLED_AS_TRIGGER(fcinfo))
{
elog(ERROR, "trig_cleanupexistingrelationships: not called by
trigger manager");
}
/* Check this is an after trigger */
if(!TRIGGER_FIRED_BEFORE(trigdata->tg_event))
{
elog(ERROR, "trig_cleanupexistingrelationships: must be called
as a before trigger");
}
/* check this is a row level trigger */
if(!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
{
elog(ERROR, "trig_cleanupexistingrelationships: must be called as a
row level trigger");
}
/* tuple to return to executor */
if(!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
{
elog(ERROR, "trig_cleanupexistingrelationships: must only be
called by an update event");
}
/* get the old and new tuples out - since this def an update by this */
/* point we can guarntee neither is null */
oldtuple = trigdata->tg_trigtuple;
newtuple = trigdata->tg_newtuple;
/* Check that it is not being made a parent of itself */
nOldParentId =
DatumGetInt64(SPI_getbinval(newtuple,
tupdesc,
RESOURCES_PARENTRESOURCE_ID,
&bIsNull));
/* tuple to return to executor */
rettuple = trigdata->tg_newtuple;
/* Get the tuple description */
tupdesc = trigdata->tg_relation->rd_att;
/* connect to SPI manager */
if(SPI_connect() < 0)
{
elog(INFO, "trig_cleanupexistingrelationships: SPI_connect failed");
}
/* Get the old parent resource ids */
nNewParentId =
DatumGetInt64(SPI_getbinval(newtuple,
tupdesc,
RESOURCES_PARENTRESOURCE_ID,
&bIsNull));
/* old parent id */
nOldParentId =
DatumGetInt64(SPI_getbinval(oldtuple,
tupdesc,
RESOURCES_PARENTRESOURCE_ID,
&bIsNull));
/* new resource id */
nNewResourceId =
DatumGetInt64(SPI_getbinval(oldtuple,
tupdesc,
RESOURCES_ID,
&bIsNull));
/* Clean up SPI stuff */
SPI_finish();
elog(INFO, "Cleanup: EXIT");
Datum d = PointerGetDatum(rettuple);
elog(INFO, "Cleanup: EXIT 2");
return d;
}
///////////////////////////////////////////
// THE AFTER TRIGGER
//
/* Prototypes*/
extern Datum trig_calculatenewrelationships(PG_FUNCTION_ARGS);
/* Implementation */
PG_FUNCTION_INFO_V1(trig_calculatenewrelationships);
Datum
trig_calculatenewrelationships(PG_FUNCTION_ARGS)
{
elog(INFO, "here a");
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple, starttuple;
int64 nResourceId, nStartId, nOldParentId;
bool bIsNull, bDoIt = false;
elog(INFO, "here b");
/* make sure it's called as a trigger at all */
if(!CALLED_AS_TRIGGER(fcinfo))
{
elog(ERROR, "trig_calculatenewrelationships: not called by trigger manager");
}
elog(INFO, "here c");
/* Check this is an after trigger */
if(TRIGGER_FIRED_BEFORE(trigdata->tg_event))
{
elog(ERROR, "trig_calculatenewrelationships: must be called as
an after trigger");
}
elog(INFO, "here d");
/* check this is a row level trigger */
if(!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
{
elog(ERROR, "trig_calculatenewrelationships: must be called as a row
level trigger");
}
elog(INFO, "here e");
/* connect to SPI manager */
if(SPI_connect() < 0)
{
elog(ERROR, "trig_calculatenewrelationships: SPI_connect failed");
}
elog(INFO, "here f");
/* Get the tuple description */
tupdesc = trigdata->tg_relation->rd_att;
/* tuple to return to executor */
elog(INFO, "here1");
nOldParentId = -1;
if(TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
{
elog(INFO, "here2");
/* Get the new parent resource id */
nStartId =
DatumGetInt64(SPI_getbinval(trigdata->tg_newtuple,
tupdesc,
RESOURCES_PARENTRESOURCE_ID,
&bIsNull));
elog(INFO, "here3");
/* Get the old parent resource id */
nOldParentId =
DatumGetInt64(SPI_getbinval(trigdata->tg_trigtuple,
tupdesc,
RESOURCES_PARENTRESOURCE_ID,
&bIsNull));
elog(INFO, "here4");
}
else
{
elog(INFO, "here5");
/* Get the new parent resource id */
nStartId =
DatumGetInt64(SPI_getbinval(trigdata->tg_trigtuple,
tupdesc,
RESOURCES_PARENTRESOURCE_ID,
&bIsNull));
elog(INFO, "here6");
}
/* Clean up SPI stuff */
SPI_finish();
/* since its an after trigger then we may as well return null */
return PointerGetDatum(NULL);
}
