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
- CREATE TABLE ttest (
- x integer
- );
Expand|Select|Wrap|Line Numbers
- #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);
- }
Expand|Select|Wrap|Line Numbers
- 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();
=> 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.