server closed the connection unexpectedly

[madhupk]

this is regarding C trigger function in postgresql 8.2

The function trigf reports the number of rows in the table ttest and skips the actual operation if the command attempts to insert a null value into the column x. (So the trigger acts as a not-null constraint but doesn't abort the transaction.)

First, the table definition:

CREATE TABLE ttest (
    x integer
);
 

This is the source code of the trigger function:

#include "postgres.h"
#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* ... and triggers */
 
extern Datum trigf(PG_FUNCTION_ARGS);
 
PG_FUNCTION_INFO_V1(trigf);
 
Datum
trigf(PG_FUNCTION_ARGS)
{
    TriggerData *trigdata = (TriggerData *) fcinfo->context;
    TupleDesc   tupdesc;
    HeapTuple   rettuple;
    char       *when;
    bool        checknull = false;
    bool        isnull;
    int         ret, i;
 
    /* make sure it's called as a trigger at all */
    if (!CALLED_AS_TRIGGER(fcinfo))
        elog(ERROR, "trigf: not called by trigger manager");
 
    /* tuple to return to executor */
    if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
        rettuple = trigdata->tg_newtuple;
    else
        rettuple = trigdata->tg_trigtuple;
 
    /* check for null values */
    if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
        && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        checknull = true;
 
    if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        when = "before";
    else
        when = "after ";
 
    tupdesc = trigdata->tg_relation->rd_att;
 
    /* connect to SPI manager */
    if ((ret = SPI_connect()) < 0)
        elog(INFO, "trigf (fired %s): SPI_connect returned %d", when, ret);
 
    /* get number of rows in table */
    ret = SPI_exec("SELECT count(*) FROM ttest", 0);
 
    if (ret < 0)
        elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret);
 
    /* count(*) returns int8, so be careful to convert */
    i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
                                    SPI_tuptable->tupdesc,
                                    1,
                                    &isnull));
 
    elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i);
 
    SPI_finish();
 
    if (checknull)
    {
        SPI_getbinval(rettuple, tupdesc, 1, &isnull);
        if (isnull)
            rettuple = NULL;
    }
 
    return PointerGetDatum(rettuple);
}
 

After compiling the source code, declare the function and the triggers:

CREATE FUNCTION trigf() RETURNS trigger
    AS 'filename'
    LANGUAGE C;
 
CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest 
    FOR EACH ROW EXECUTE PROCEDURE trigf();
 
CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest 
    FOR EACH ROW EXECUTE PROCEDURE trigf();
 

Now you can test the operation of the trigger:

=> INSERT INTO ttest VALUES (NULL)

THE PROBLEM STARTS WHILE EXECUTING THIS INSERT QUERY.
IT SHOWS:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

THE ERROR LOG FILE SHOWS:

SERVER PROCESS(PID 195) TERMINATED BY SIGNAL11


Please clarify on what should be done.

[michaelb]

Please read the Posting Guidelines at the top of this forum. Make sure you quote your code snippets appropriately using the CODE tags.

It might be helpful to give us the OS and the database version you are using.

One good technique when dealing with problems like this is to enable verbose logging. You can put something like this in postgresql.conf :

client_min_messages = notice
log_min_messages = notice
log_min_error_statement = notice
log_statement = all

Look here for all available options.

After you edited the Postgres configuration file you need to either restart the database server or make it re-read the config. by running pg_ctl reload

The code you posted references some filename:

CREATE FUNCTION trigf() RETURNS trigger
    AS 'filename'
    LANGUAGE C;
 

Is this indeed the name of the compiled C code?
Did you build it as a shared library?