By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,373 Members | 1,763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,373 IT Pros & Developers. It's quick & easy.

server closed the connection unexpectedly

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE ttest (
  2.     x integer
  3. );
  4.  
This is the source code of the trigger function:
Expand|Select|Wrap|Line Numbers
  1. #include "postgres.h"
  2. #include "executor/spi.h"       /* this is what you need to work with SPI */
  3. #include "commands/trigger.h"   /* ... and triggers */
  4.  
  5. extern Datum trigf(PG_FUNCTION_ARGS);
  6.  
  7. PG_FUNCTION_INFO_V1(trigf);
  8.  
  9. Datum
  10. trigf(PG_FUNCTION_ARGS)
  11. {
  12.     TriggerData *trigdata = (TriggerData *) fcinfo->context;
  13.     TupleDesc   tupdesc;
  14.     HeapTuple   rettuple;
  15.     char       *when;
  16.     bool        checknull = false;
  17.     bool        isnull;
  18.     int         ret, i;
  19.  
  20.     /* make sure it's called as a trigger at all */
  21.     if (!CALLED_AS_TRIGGER(fcinfo))
  22.         elog(ERROR, "trigf: not called by trigger manager");
  23.  
  24.     /* tuple to return to executor */
  25.     if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
  26.         rettuple = trigdata->tg_newtuple;
  27.     else
  28.         rettuple = trigdata->tg_trigtuple;
  29.  
  30.     /* check for null values */
  31.     if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
  32.         && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
  33.         checknull = true;
  34.  
  35.     if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
  36.         when = "before";
  37.     else
  38.         when = "after ";
  39.  
  40.     tupdesc = trigdata->tg_relation->rd_att;
  41.  
  42.     /* connect to SPI manager */
  43.     if ((ret = SPI_connect()) < 0)
  44.         elog(INFO, "trigf (fired %s): SPI_connect returned %d", when, ret);
  45.  
  46.     /* get number of rows in table */
  47.     ret = SPI_exec("SELECT count(*) FROM ttest", 0);
  48.  
  49.     if (ret < 0)
  50.         elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret);
  51.  
  52.     /* count(*) returns int8, so be careful to convert */
  53.     i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
  54.                                     SPI_tuptable->tupdesc,
  55.                                     1,
  56.                                     &isnull));
  57.  
  58.     elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i);
  59.  
  60.     SPI_finish();
  61.  
  62.     if (checknull)
  63.     {
  64.         SPI_getbinval(rettuple, tupdesc, 1, &isnull);
  65.         if (isnull)
  66.             rettuple = NULL;
  67.     }
  68.  
  69.     return PointerGetDatum(rettuple);
  70. }
  71.  
After compiling the source code, declare the function and the triggers:
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION trigf() RETURNS trigger
  2.     AS 'filename'
  3.     LANGUAGE C;
  4.  
  5. CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest 
  6.     FOR EACH ROW EXECUTE PROCEDURE trigf();
  7.  
  8. CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest 
  9.     FOR EACH ROW EXECUTE PROCEDURE trigf();
  10.  
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.
May 4 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 534
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:

Expand|Select|Wrap|Line Numbers
  1. client_min_messages = notice
  2. log_min_messages = notice
  3. log_min_error_statement = notice
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION trigf() RETURNS trigger
  2.     AS 'filename'
  3.     LANGUAGE C;
  4.  
Is this indeed the name of the compiled C code?
Did you build it as a shared library?
May 5 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.