Datum _Slony_I_lockedSet(PG_FUNCTION_ARGS) { TriggerData *tg; /* * Get the trigger call context */ if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "Slony-I: lockedSet() not called as trigger"); tg = (TriggerData *) (fcinfo->context); /* * Check all logTrigger() calling conventions */ if (!TRIGGER_FIRED_BEFORE(tg->tg_event)) elog(ERROR, "Slony-I: denyAccess() must be fired BEFORE"); if (!TRIGGER_FIRED_FOR_ROW(tg->tg_event)) elog(ERROR, "Slony-I: denyAccess() must be fired FOR EACH ROW"); if (tg->tg_trigger->tgnargs != 1) elog(ERROR, "Slony-I: denyAccess() must be defined with 1 arg"); elog(ERROR, "Slony-I: Table %s is currently locked against updates " "because of MOVE_SET operation in progress", NameStr(tg->tg_relation->rd_rel->relname)); return (Datum) 0; }
/* * suppress_redundant_updates_trigger * * This trigger function will inhibit an update from being done * if the OLD and NEW records are identical. */ Datum suppress_redundant_updates_trigger(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; HeapTuple newtuple, oldtuple, rettuple; HeapTupleHeader newheader, oldheader; /* make sure it's called as a trigger */ if (!CALLED_AS_TRIGGER(fcinfo)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), errmsg("suppress_redundant_updates_trigger: must be called as trigger"))); /* and that it's called on update */ if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), errmsg("suppress_redundant_updates_trigger: must be called on update"))); /* and that it's called before update */ if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), errmsg("suppress_redundant_updates_trigger: must be called before update"))); /* and that it's called for each row */ if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), errmsg("suppress_redundant_updates_trigger: must be called for each row"))); /* get tuple data, set default result */ rettuple = newtuple = trigdata->tg_newtuple; oldtuple = trigdata->tg_trigtuple; newheader = newtuple->t_data; oldheader = oldtuple->t_data; /* if the tuple payload is the same ... */ if (newtuple->t_len == oldtuple->t_len && newheader->t_hoff == oldheader->t_hoff && (HeapTupleHeaderGetNatts(newheader) == HeapTupleHeaderGetNatts(oldheader)) && ((newheader->t_infomask & ~HEAP_XACT_MASK) == (oldheader->t_infomask & ~HEAP_XACT_MASK)) && memcmp(((char *) newheader) + SizeofHeapTupleHeader, ((char *) oldheader) + SizeofHeapTupleHeader, newtuple->t_len - SizeofHeapTupleHeader) == 0) { /* ... then suppress the update */ rettuple = NULL; } return PointerGetDatum(rettuple); }
/** * @fn Datum reorg_trigger(PG_FUNCTION_ARGS) * @brief Insert a operation log into log-table. * * reorg_trigger(sql) * * @param sql SQL to insert a operation log into log-table. */ Datum reorg_trigger(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; TupleDesc desc; HeapTuple tuple; Datum values[2]; bool nulls[2] = { 0, 0 }; Oid argtypes[2]; const char *sql; /* authority check */ must_be_superuser("reorg_trigger"); /* make sure it's called as a trigger at all */ if (!CALLED_AS_TRIGGER(fcinfo) || !TRIGGER_FIRED_BEFORE(trigdata->tg_event) || !TRIGGER_FIRED_FOR_ROW(trigdata->tg_event) || trigdata->tg_trigger->tgnargs != 1) elog(ERROR, "reorg_trigger: invalid trigger call"); /* retrieve parameters */ sql = trigdata->tg_trigger->tgargs[0]; desc = RelationGetDescr(trigdata->tg_relation); argtypes[0] = argtypes[1] = trigdata->tg_relation->rd_rel->reltype; /* connect to SPI manager */ reorg_init(); if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) { /* INSERT: (NULL, newtup) */ tuple = trigdata->tg_trigtuple; nulls[0] = true; values[1] = copy_tuple(tuple, desc); } else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) { /* DELETE: (oldtup, NULL) */ tuple = trigdata->tg_trigtuple; values[0] = copy_tuple(tuple, desc); nulls[1] = true; } else { /* UPDATE: (oldtup, newtup) */ tuple = trigdata->tg_newtuple; values[0] = copy_tuple(trigdata->tg_trigtuple, desc); values[1] = copy_tuple(tuple, desc); } /* INSERT INTO reorg.log VALUES ($1, $2) */ execute_with_args(SPI_OK_INSERT, sql, 2, argtypes, values, nulls); SPI_finish(); PG_RETURN_POINTER(tuple); }
Datum _Slony_I_denyAccess(PG_FUNCTION_ARGS) { TriggerData *tg; /* * Get the trigger call context */ if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "Slony-I: denyAccess() not called as trigger"); tg = (TriggerData *) (fcinfo->context); /* * Check all logTrigger() calling conventions */ if (!TRIGGER_FIRED_BEFORE(tg->tg_event)) elog(ERROR, "Slony-I: denyAccess() must be fired BEFORE"); if (!TRIGGER_FIRED_FOR_ROW(tg->tg_event)) elog(ERROR, "Slony-I: denyAccess() must be fired FOR EACH ROW"); if (tg->tg_trigger->tgnargs != 1) elog(ERROR, "Slony-I: denyAccess() must be defined with 1 arg"); /* * Connect to the SPI manager */ if (SPI_connect() < 0) elog(ERROR, "Slony-I: SPI_connect() failed in denyAccess()"); /* * If the replication role is: ORIGIN - default role --> FAIL REPLICA - * this trigger will not fire in --> N/A LOCAL - role when running "local * updates" --> PERMIT UPDATE */ if (SessionReplicationRole == SESSION_REPLICATION_ROLE_ORIGIN) elog(ERROR, "Slony-I: Table %s is replicated and cannot be " "modified on a subscriber node - role=%d", NameStr(tg->tg_relation->rd_rel->relname), SessionReplicationRole); SPI_finish(); if (TRIGGER_FIRED_BY_UPDATE(tg->tg_event)) return PointerGetDatum(tg->tg_newtuple); else return PointerGetDatum(tg->tg_trigtuple); }
/* * Set environment variables corresponding to trigger data */ static void set_trigger_data_envvars(TriggerData *trigdata) { const char *tg_when_str = NULL; const char *tg_level_str = NULL; const char *tg_op_str = NULL; setenv("PLSH_TG_NAME", trigdata->tg_trigger->tgname, 1); if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) tg_when_str = "BEFORE"; #ifdef TRIGGER_FIRED_INSTEAD else if (TRIGGER_FIRED_INSTEAD(trigdata->tg_event)) tg_when_str = "INSTEAD OF"; #endif else if (TRIGGER_FIRED_AFTER(trigdata->tg_event)) tg_when_str = "AFTER"; if (tg_when_str) setenv("PLSH_TG_WHEN", tg_when_str, 1); if (TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) tg_level_str = "ROW"; else if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event)) tg_level_str = "STATEMENT"; if (tg_level_str) setenv("PLSH_TG_LEVEL", tg_level_str, 1); if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) tg_op_str = "DELETE"; else if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) tg_op_str = "INSERT"; else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) tg_op_str = "UPDATE"; #ifdef TRIGGER_FIRED_BY_TRUNCATE else if (TRIGGER_FIRED_BY_TRUNCATE(trigdata->tg_event)) tg_op_str = "TRUNCATE"; #endif if (tg_op_str) setenv("PLSH_TG_OP", tg_op_str, 1); setenv("PLSH_TG_TABLE_NAME", NameStr(trigdata->tg_relation->rd_rel->relname), 1); setenv("PLSH_TG_TABLE_SCHEMA", get_namespace_name(trigdata->tg_relation->rd_rel->relnamespace), 1); }
Datum check_authorization(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; char *colname; HeapTuple rettuple_ok; HeapTuple rettuple_fail; TupleDesc tupdesc; int SPIcode; char query[1024]; const char *pk_id = NULL; SPITupleTable *tuptable; HeapTuple tuple; char *lockcode; char *authtable = "authorization_table"; const char *op; #define ERRMSGLEN 256 char err_msg[ERRMSGLEN]; /* Make sure trigdata is pointing at what I expect */ if ( ! CALLED_AS_TRIGGER(fcinfo) ) { elog(ERROR,"check_authorization: not fired by trigger manager"); } if ( ! TRIGGER_FIRED_BEFORE(trigdata->tg_event) ) { elog(ERROR,"check_authorization: not fired *before* event"); } if ( TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event) ) { rettuple_ok = trigdata->tg_newtuple; rettuple_fail = NULL; op = "UPDATE"; } else if ( TRIGGER_FIRED_BY_DELETE(trigdata->tg_event) ) { rettuple_ok = trigdata->tg_trigtuple; rettuple_fail = NULL; op = "DELETE"; } else { elog(ERROR,"check_authorization: not fired by update or delete"); PG_RETURN_NULL(); } tupdesc = trigdata->tg_relation->rd_att; /* Connect to SPI manager */ SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR,"check_authorization: could not connect to SPI"); PG_RETURN_NULL() ; } colname = trigdata->tg_trigger->tgargs[0]; pk_id = SPI_getvalue(trigdata->tg_trigtuple, tupdesc, SPI_fnumber(tupdesc, colname)); POSTGIS_DEBUG(3, "check_authorization called"); sprintf(query,"SELECT authid FROM \"%s\" WHERE expires >= now() AND toid = '%d' AND rid = '%s'", authtable, trigdata->tg_relation->rd_id, pk_id); POSTGIS_DEBUGF(3 ,"about to execute :%s", query); SPIcode = SPI_exec(query,0); if (SPIcode !=SPI_OK_SELECT ) elog(ERROR,"couldnt execute to test for lock :%s",query); if (!SPI_processed ) { POSTGIS_DEBUGF(3, "there is NO lock on row '%s'", pk_id); SPI_finish(); return PointerGetDatum(rettuple_ok); } /* there is a lock - check to see if I have rights to it! */ tuptable = SPI_tuptable; tupdesc = tuptable->tupdesc; tuple = tuptable->vals[0]; lockcode = SPI_getvalue(tuple, tupdesc, 1); POSTGIS_DEBUGF(3, "there is a lock on row '%s' (auth: '%s').", pk_id, lockcode); /* * check to see if temp_lock_have_table table exists * (it might not exist if they own no locks) */ sprintf(query,"SELECT * FROM pg_class WHERE relname = 'temp_lock_have_table'"); SPIcode = SPI_exec(query,0); if (SPIcode != SPI_OK_SELECT ) elog(ERROR,"couldnt execute to test for lockkey temp table :%s",query); if (SPI_processed==0) { goto fail; } sprintf(query, "SELECT * FROM temp_lock_have_table WHERE xideq( transid, getTransactionID() ) AND lockcode ='%s'", lockcode); POSTGIS_DEBUGF(3, "about to execute :%s", query); SPIcode = SPI_exec(query,0); if (SPIcode != SPI_OK_SELECT ) elog(ERROR, "couldnt execute to test for lock aquire: %s", query); if (SPI_processed >0) { POSTGIS_DEBUG(3, "I own the lock - I can modify the row"); SPI_finish(); return PointerGetDatum(rettuple_ok); } fail: snprintf(err_msg, ERRMSGLEN, "%s where \"%s\" = '%s' requires authorization '%s'", op, colname, pk_id, lockcode); err_msg[ERRMSGLEN-1] = '\0'; #ifdef ABORT_ON_AUTH_FAILURE elog(ERROR, "%s", err_msg); #else elog(NOTICE, "%s", err_msg); #endif SPI_finish(); return PointerGetDatum(rettuple_fail); }
static SV * plperl_trigger_build_args(FunctionCallInfo fcinfo) { TriggerData *tdata; TupleDesc tupdesc; int i; char *level; char *event; char *relid; char *when; HV *hv; hv = newHV(); tdata = (TriggerData *) fcinfo->context; tupdesc = tdata->tg_relation->rd_att; relid = DatumGetCString( DirectFunctionCall1(oidout, ObjectIdGetDatum(tdata->tg_relation->rd_id) ) ); hv_store(hv, "name", 4, newSVpv(tdata->tg_trigger->tgname, 0), 0); hv_store(hv, "relid", 5, newSVpv(relid, 0), 0); if (TRIGGER_FIRED_BY_INSERT(tdata->tg_event)) { event = "INSERT"; if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event)) hv_store(hv, "new", 3, plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc), 0); } else if (TRIGGER_FIRED_BY_DELETE(tdata->tg_event)) { event = "DELETE"; if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event)) hv_store(hv, "old", 3, plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc), 0); } else if (TRIGGER_FIRED_BY_UPDATE(tdata->tg_event)) { event = "UPDATE"; if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event)) { hv_store(hv, "old", 3, plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc), 0); hv_store(hv, "new", 3, plperl_hash_from_tuple(tdata->tg_newtuple, tupdesc), 0); } } else event = "UNKNOWN"; hv_store(hv, "event", 5, newSVpv(event, 0), 0); hv_store(hv, "argc", 4, newSViv(tdata->tg_trigger->tgnargs), 0); if (tdata->tg_trigger->tgnargs > 0) { AV *av = newAV(); for (i = 0; i < tdata->tg_trigger->tgnargs; i++) av_push(av, newSVpv(tdata->tg_trigger->tgargs[i], 0)); hv_store(hv, "args", 4, newRV_noinc((SV *) av), 0); } hv_store(hv, "relname", 7, newSVpv(SPI_getrelname(tdata->tg_relation), 0), 0); if (TRIGGER_FIRED_BEFORE(tdata->tg_event)) when = "BEFORE"; else if (TRIGGER_FIRED_AFTER(tdata->tg_event)) when = "AFTER"; else when = "UNKNOWN"; hv_store(hv, "when", 4, newSVpv(when, 0), 0); if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event)) level = "ROW"; else if (TRIGGER_FIRED_FOR_STATEMENT(tdata->tg_event)) level = "STATEMENT"; else level = "UNKNOWN"; hv_store(hv, "level", 5, newSVpv(level, 0), 0); return newRV_noinc((SV *) hv); }
Datum /* have to return HeapTuple to Executor */ timetravel(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; Trigger *trigger; /* to get trigger name */ int argc; char **args; /* arguments */ int attnum[MaxAttrNum]; /* fnumbers of start/stop columns */ Datum oldtimeon, oldtimeoff; Datum newtimeon, newtimeoff, newuser, nulltext; Datum *cvals; /* column values */ char *cnulls; /* column nulls */ char *relname; /* triggered relation name */ Relation rel; /* triggered relation */ HeapTuple trigtuple; HeapTuple newtuple = NULL; HeapTuple rettuple; TupleDesc tupdesc; /* tuple description */ int natts; /* # of attributes */ EPlan *plan; /* prepared plan */ char ident[2 * NAMEDATALEN]; bool isnull; /* to know is some column NULL or not */ bool isinsert = false; int ret; int i; /* * Some checks first... */ /* Called by trigger manager ? */ if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "timetravel: not fired by trigger manager"); /* Should be called for ROW trigger */ if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) elog(ERROR, "timetravel: must be fired for row"); /* Should be called BEFORE */ if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) elog(ERROR, "timetravel: must be fired before event"); /* INSERT ? */ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) isinsert = true; if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) newtuple = trigdata->tg_newtuple; trigtuple = trigdata->tg_trigtuple; rel = trigdata->tg_relation; relname = SPI_getrelname(rel); /* check if TT is OFF for this relation */ if (0 == findTTStatus(relname)) { /* OFF - nothing to do */ pfree(relname); return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple); } trigger = trigdata->tg_trigger; argc = trigger->tgnargs; if (argc != MinAttrNum && argc != MaxAttrNum) elog(ERROR, "timetravel (%s): invalid (!= %d or %d) number of arguments %d", relname, MinAttrNum, MaxAttrNum, trigger->tgnargs); args = trigger->tgargs; tupdesc = rel->rd_att; natts = tupdesc->natts; for (i = 0; i < MinAttrNum; i++) { attnum[i] = SPI_fnumber(tupdesc, args[i]); if (attnum[i] < 0) elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]); if (SPI_gettypeid(tupdesc, attnum[i]) != ABSTIMEOID) elog(ERROR, "timetravel (%s): attribute %s must be of abstime type", relname, args[i]); } for (; i < argc; i++) { attnum[i] = SPI_fnumber(tupdesc, args[i]); if (attnum[i] < 0) elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]); if (SPI_gettypeid(tupdesc, attnum[i]) != TEXTOID) elog(ERROR, "timetravel (%s): attribute %s must be of text type", relname, args[i]); } /* create fields containing name */ newuser = CStringGetTextDatum(GetUserNameFromId(GetUserId())); nulltext = (Datum) NULL; if (isinsert) { /* INSERT */ int chnattrs = 0; int chattrs[MaxAttrNum]; Datum newvals[MaxAttrNum]; char newnulls[MaxAttrNum]; oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull); if (isnull) { newvals[chnattrs] = GetCurrentAbsoluteTime(); newnulls[chnattrs] = ' '; chattrs[chnattrs] = attnum[a_time_on]; chnattrs++; } oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull); if (isnull) { if ((chnattrs == 0 && DatumGetInt32(oldtimeon) >= NOEND_ABSTIME) || (chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) >= NOEND_ABSTIME)) elog(ERROR, "timetravel (%s): %s is infinity", relname, args[a_time_on]); newvals[chnattrs] = NOEND_ABSTIME; newnulls[chnattrs] = ' '; chattrs[chnattrs] = attnum[a_time_off]; chnattrs++; } else { if ((chnattrs == 0 && DatumGetInt32(oldtimeon) > DatumGetInt32(oldtimeoff)) || (chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) > DatumGetInt32(oldtimeoff))) elog(ERROR, "timetravel (%s): %s gt %s", relname, args[a_time_on], args[a_time_off]); } pfree(relname); if (chnattrs <= 0) return PointerGetDatum(trigtuple); if (argc == MaxAttrNum) { /* clear update_user value */ newvals[chnattrs] = nulltext; newnulls[chnattrs] = 'n'; chattrs[chnattrs] = attnum[a_upd_user]; chnattrs++; /* clear delete_user value */ newvals[chnattrs] = nulltext; newnulls[chnattrs] = 'n'; chattrs[chnattrs] = attnum[a_del_user]; chnattrs++; /* set insert_user value */ newvals[chnattrs] = newuser; newnulls[chnattrs] = ' '; chattrs[chnattrs] = attnum[a_ins_user]; chnattrs++; } rettuple = SPI_modifytuple(rel, trigtuple, chnattrs, chattrs, newvals, newnulls); return PointerGetDatum(rettuple); /* end of INSERT */ } /* UPDATE/DELETE: */ oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull); if (isnull) elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]); oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull); if (isnull) elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]); /* * If DELETE/UPDATE of tuple with stop_date neq INFINITY then say upper * Executor to skip operation for this tuple */ if (newtuple != NULL) { /* UPDATE */ newtimeon = SPI_getbinval(newtuple, tupdesc, attnum[a_time_on], &isnull); if (isnull) elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]); newtimeoff = SPI_getbinval(newtuple, tupdesc, attnum[a_time_off], &isnull); if (isnull) elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]); if (oldtimeon != newtimeon || oldtimeoff != newtimeoff) elog(ERROR, "timetravel (%s): you cannot change %s and/or %s columns (use set_timetravel)", relname, args[a_time_on], args[a_time_off]); } if (oldtimeoff != NOEND_ABSTIME) { /* current record is a deleted/updated record */ pfree(relname); return PointerGetDatum(NULL); } newtimeoff = GetCurrentAbsoluteTime(); /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) elog(ERROR, "timetravel (%s): SPI_connect returned %d", relname, ret); /* Fetch tuple values and nulls */ cvals = (Datum *) palloc(natts * sizeof(Datum)); cnulls = (char *) palloc(natts * sizeof(char)); for (i = 0; i < natts; i++) { cvals[i] = SPI_getbinval(trigtuple, tupdesc, i + 1, &isnull); cnulls[i] = (isnull) ? 'n' : ' '; } /* change date column(s) */ cvals[attnum[a_time_off] - 1] = newtimeoff; /* stop_date eq current date */ cnulls[attnum[a_time_off] - 1] = ' '; if (!newtuple) { /* DELETE */ if (argc == MaxAttrNum) { cvals[attnum[a_del_user] - 1] = newuser; /* set delete user */ cnulls[attnum[a_del_user] - 1] = ' '; } } /* * Construct ident string as TriggerName $ TriggeredRelationId and try to * find prepared execution plan. */ snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); plan = find_plan(ident, &Plans, &nPlans); /* if there is no plan ... */ if (plan->splan == NULL) { SPIPlanPtr pplan; Oid *ctypes; char sql[8192]; char separ = ' '; /* allocate ctypes for preparation */ ctypes = (Oid *) palloc(natts * sizeof(Oid)); /* * Construct query: INSERT INTO _relation_ VALUES ($1, ...) */ snprintf(sql, sizeof(sql), "INSERT INTO %s VALUES (", relname); for (i = 1; i <= natts; i++) { ctypes[i - 1] = SPI_gettypeid(tupdesc, i); if (!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */ { snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ, i); separ = ','; } } snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")"); elog(DEBUG4, "timetravel (%s) update: sql: %s", relname, sql); /* Prepare plan for query */ pplan = SPI_prepare(sql, natts, ctypes); if (pplan == NULL) elog(ERROR, "timetravel (%s): SPI_prepare returned %d", relname, SPI_result); /* * Remember that SPI_prepare places plan in current memory context - * so, we have to save plan in Top memory context for later use. */ if (SPI_keepplan(pplan)) elog(ERROR, "timetravel (%s): SPI_keepplan failed", relname); plan->splan = pplan; } /* * Ok, execute prepared plan. */ ret = SPI_execp(plan->splan, cvals, cnulls, 0); if (ret < 0) elog(ERROR, "timetravel (%s): SPI_execp returned %d", relname, ret); /* Tuple to return to upper Executor ... */ if (newtuple) { /* UPDATE */ int chnattrs = 0; int chattrs[MaxAttrNum]; Datum newvals[MaxAttrNum]; char newnulls[MaxAttrNum]; newvals[chnattrs] = newtimeoff; newnulls[chnattrs] = ' '; chattrs[chnattrs] = attnum[a_time_on]; chnattrs++; newvals[chnattrs] = NOEND_ABSTIME; newnulls[chnattrs] = ' '; chattrs[chnattrs] = attnum[a_time_off]; chnattrs++; if (argc == MaxAttrNum) { /* set update_user value */ newvals[chnattrs] = newuser; newnulls[chnattrs] = ' '; chattrs[chnattrs] = attnum[a_upd_user]; chnattrs++; /* clear delete_user value */ newvals[chnattrs] = nulltext; newnulls[chnattrs] = 'n'; chattrs[chnattrs] = attnum[a_del_user]; chnattrs++; /* set insert_user value */ newvals[chnattrs] = nulltext; newnulls[chnattrs] = 'n'; chattrs[chnattrs] = attnum[a_ins_user]; chnattrs++; } rettuple = SPI_modifytuple(rel, newtuple, chnattrs, chattrs, newvals, newnulls); /* * SPI_copytuple allocates tmptuple in upper executor context - have * to free allocation using SPI_pfree */ /* SPI_pfree(tmptuple); */ } else /* DELETE case */ rettuple = trigtuple; SPI_finish(); /* don't forget say Bye to SPI mgr */ pfree(relname); return PointerGetDatum(rettuple); }
static Datum tsvector_update_trigger(PG_FUNCTION_ARGS, bool config_column) { TriggerData *trigdata; Trigger *trigger; Relation rel; HeapTuple rettuple = NULL; int tsvector_attr_num, i; ParsedText prs; Datum datum; bool isnull; text *txt; Oid cfgId; /* Check call context */ if (!CALLED_AS_TRIGGER(fcinfo)) /* internal error */ elog(ERROR, "tsvector_update_trigger: not fired by trigger manager"); trigdata = (TriggerData *) fcinfo->context; if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) elog(ERROR, "tsvector_update_trigger: must be fired for row"); if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) elog(ERROR, "tsvector_update_trigger: must be fired BEFORE event"); if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) rettuple = trigdata->tg_trigtuple; else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) rettuple = trigdata->tg_newtuple; else elog(ERROR, "tsvector_update_trigger: must be fired for INSERT or UPDATE"); trigger = trigdata->tg_trigger; rel = trigdata->tg_relation; if (trigger->tgnargs < 3) elog(ERROR, "tsvector_update_trigger: arguments must be tsvector_field, ts_config, text_field1, ...)"); /* Find the target tsvector column */ tsvector_attr_num = SPI_fnumber(rel->rd_att, trigger->tgargs[0]); if (tsvector_attr_num == SPI_ERROR_NOATTRIBUTE) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_COLUMN), errmsg("tsvector column \"%s\" does not exist", trigger->tgargs[0]))); if (!IsBinaryCoercible(SPI_gettypeid(rel->rd_att, tsvector_attr_num), TSVECTOROID)) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("column \"%s\" is not of tsvector type", trigger->tgargs[0]))); /* Find the configuration to use */ if (config_column) { int config_attr_num; config_attr_num = SPI_fnumber(rel->rd_att, trigger->tgargs[1]); if (config_attr_num == SPI_ERROR_NOATTRIBUTE) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_COLUMN), errmsg("configuration column \"%s\" does not exist", trigger->tgargs[1]))); if (!IsBinaryCoercible(SPI_gettypeid(rel->rd_att, config_attr_num), REGCONFIGOID)) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("column \"%s\" is not of regconfig type", trigger->tgargs[1]))); datum = SPI_getbinval(rettuple, rel->rd_att, config_attr_num, &isnull); if (isnull) ereport(ERROR, (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), errmsg("configuration column \"%s\" must not be null", trigger->tgargs[1]))); cfgId = DatumGetObjectId(datum); } else { List *names; names = stringToQualifiedNameList(trigger->tgargs[1]); /* require a schema so that results are not search path dependent */ if (list_length(names) < 2) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("text search configuration name \"%s\" must be schema-qualified", trigger->tgargs[1]))); cfgId = get_ts_config_oid(names, false); } /* initialize parse state */ prs.lenwords = 32; prs.curwords = 0; prs.pos = 0; prs.words = (ParsedWord *) palloc(sizeof(ParsedWord) * prs.lenwords); /* find all words in indexable column(s) */ for (i = 2; i < trigger->tgnargs; i++) { int numattr; numattr = SPI_fnumber(rel->rd_att, trigger->tgargs[i]); if (numattr == SPI_ERROR_NOATTRIBUTE) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_COLUMN), errmsg("column \"%s\" does not exist", trigger->tgargs[i]))); if (!IsBinaryCoercible(SPI_gettypeid(rel->rd_att, numattr), TEXTOID)) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("column \"%s\" is not of a character type", trigger->tgargs[i]))); datum = SPI_getbinval(rettuple, rel->rd_att, numattr, &isnull); if (isnull) continue; txt = DatumGetTextP(datum); parsetext(cfgId, &prs, VARDATA(txt), VARSIZE(txt) - VARHDRSZ); if (txt != (text *) DatumGetPointer(datum)) pfree(txt); } /* make tsvector value */ if (prs.curwords) { datum = PointerGetDatum(make_tsvector(&prs)); rettuple = SPI_modifytuple(rel, rettuple, 1, &tsvector_attr_num, &datum, NULL); pfree(DatumGetPointer(datum)); } else { TSVector out = palloc(CALCDATASIZE(0, 0)); SET_VARSIZE(out, CALCDATASIZE(0, 0)); out->size = 0; datum = PointerGetDatum(out); rettuple = SPI_modifytuple(rel, rettuple, 1, &tsvector_attr_num, &datum, NULL); pfree(prs.words); } if (rettuple == NULL) /* internal error */ elog(ERROR, "tsvector_update_trigger: %d returned by SPI_modifytuple", SPI_result); return PointerGetDatum(rettuple); }
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(ERROR, "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(ERROR, "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); }
Datum ttdummy(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; Trigger *trigger; /* to get trigger name */ char **args; /* arguments */ int attnum[2]; /* fnumbers of start/stop columns */ Datum oldon, oldoff; Datum newon, newoff; Datum *cvals; /* column values */ char *cnulls; /* column nulls */ char *relname; /* triggered relation name */ Relation rel; /* triggered relation */ HeapTuple trigtuple; HeapTuple newtuple = NULL; HeapTuple rettuple; TupleDesc tupdesc; /* tuple description */ int natts; /* # of attributes */ bool isnull; /* to know is some column NULL or not */ int ret; int i; if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "ttdummy: not fired by trigger manager"); if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) elog(ERROR, "ttdummy: must be fired for row"); if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) elog(ERROR, "ttdummy: must be fired before event"); if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) elog(ERROR, "ttdummy: cannot process INSERT event"); if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) newtuple = trigdata->tg_newtuple; trigtuple = trigdata->tg_trigtuple; rel = trigdata->tg_relation; relname = SPI_getrelname(rel); /* check if TT is OFF for this relation */ if (ttoff) /* OFF - nothing to do */ { pfree(relname); return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple); } trigger = trigdata->tg_trigger; if (trigger->tgnargs != 2) elog(ERROR, "ttdummy (%s): invalid (!= 2) number of arguments %d", relname, trigger->tgnargs); args = trigger->tgargs; tupdesc = rel->rd_att; natts = tupdesc->natts; for (i = 0; i < 2; i++) { attnum[i] = SPI_fnumber(tupdesc, args[i]); if (attnum[i] <= 0) elog(ERROR, "ttdummy (%s): there is no attribute %s", relname, args[i]); if (SPI_gettypeid(tupdesc, attnum[i]) != INT4OID) elog(ERROR, "ttdummy (%s): attribute %s must be of integer type", relname, args[i]); } oldon = SPI_getbinval(trigtuple, tupdesc, attnum[0], &isnull); if (isnull) elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]); oldoff = SPI_getbinval(trigtuple, tupdesc, attnum[1], &isnull); if (isnull) elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]); if (newtuple != NULL) /* UPDATE */ { newon = SPI_getbinval(newtuple, tupdesc, attnum[0], &isnull); if (isnull) elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]); newoff = SPI_getbinval(newtuple, tupdesc, attnum[1], &isnull); if (isnull) elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]); if (oldon != newon || oldoff != newoff) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("ttdummy (%s): you cannot change %s and/or %s columns (use set_ttdummy)", relname, args[0], args[1]))); if (newoff != TTDUMMY_INFINITY) { pfree(relname); /* allocated in upper executor context */ return PointerGetDatum(NULL); } } else if (oldoff != TTDUMMY_INFINITY) /* DELETE */ { pfree(relname); return PointerGetDatum(NULL); } newoff = DirectFunctionCall1(nextval, CStringGetTextDatum("ttdummy_seq")); /* nextval now returns int64; coerce down to int32 */ newoff = Int32GetDatum((int32) DatumGetInt64(newoff)); /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) elog(ERROR, "ttdummy (%s): SPI_connect returned %d", relname, ret); /* Fetch tuple values and nulls */ cvals = (Datum *) palloc(natts * sizeof(Datum)); cnulls = (char *) palloc(natts * sizeof(char)); for (i = 0; i < natts; i++) { cvals[i] = SPI_getbinval((newtuple != NULL) ? newtuple : trigtuple, tupdesc, i + 1, &isnull); cnulls[i] = (isnull) ? 'n' : ' '; } /* change date column(s) */ if (newtuple) /* UPDATE */ { cvals[attnum[0] - 1] = newoff; /* start_date eq current date */ cnulls[attnum[0] - 1] = ' '; cvals[attnum[1] - 1] = TTDUMMY_INFINITY; /* stop_date eq INFINITY */ cnulls[attnum[1] - 1] = ' '; } else /* DELETE */ { cvals[attnum[1] - 1] = newoff; /* stop_date eq current date */ cnulls[attnum[1] - 1] = ' '; } /* if there is no plan ... */ if (splan == NULL) { SPIPlanPtr pplan; Oid *ctypes; char *query; /* allocate space in preparation */ ctypes = (Oid *) palloc(natts * sizeof(Oid)); query = (char *) palloc(100 + 16 * natts); /* * Construct query: INSERT INTO _relation_ VALUES ($1, ...) */ sprintf(query, "INSERT INTO %s VALUES (", relname); for (i = 1; i <= natts; i++) { sprintf(query + strlen(query), "$%d%s", i, (i < natts) ? ", " : ")"); ctypes[i - 1] = SPI_gettypeid(tupdesc, i); } /* Prepare plan for query */ pplan = SPI_prepare(query, natts, ctypes); if (pplan == NULL) elog(ERROR, "ttdummy (%s): SPI_prepare returned %s", relname, SPI_result_code_string(SPI_result)); if (SPI_keepplan(pplan)) elog(ERROR, "ttdummy (%s): SPI_keepplan failed", relname); splan = pplan; } ret = SPI_execp(splan, cvals, cnulls, 0); if (ret < 0) elog(ERROR, "ttdummy (%s): SPI_execp returned %d", relname, ret); /* Tuple to return to upper Executor ... */ if (newtuple) /* UPDATE */ rettuple = SPI_modifytuple(rel, trigtuple, 1, &(attnum[1]), &newoff, NULL); else /* DELETE */ rettuple = trigtuple; SPI_finish(); /* don't forget say Bye to SPI mgr */ pfree(relname); return PointerGetDatum(rettuple); }
Datum moddatetime(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; Trigger *trigger; /* to get trigger name */ int nargs; /* # of arguments */ int attnum; /* positional number of field to change */ Oid atttypid; /* type OID of field to change */ Datum newdt; /* The current datetime. */ char **args; /* arguments */ char *relname; /* triggered relation name */ Relation rel; /* triggered relation */ HeapTuple rettuple = NULL; TupleDesc tupdesc; /* tuple description */ if (!CALLED_AS_TRIGGER(fcinfo)) /* internal error */ elog(ERROR, "moddatetime: not fired by trigger manager"); if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) /* internal error */ elog(ERROR, "moddatetime: must be fired for row"); if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) /* internal error */ elog(ERROR, "moddatetime: must be fired before event"); if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) /* internal error */ elog(ERROR, "moddatetime: cannot process INSERT events"); else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) rettuple = trigdata->tg_newtuple; else /* internal error */ elog(ERROR, "moddatetime: cannot process DELETE events"); rel = trigdata->tg_relation; relname = SPI_getrelname(rel); trigger = trigdata->tg_trigger; nargs = trigger->tgnargs; if (nargs != 1) /* internal error */ elog(ERROR, "moddatetime (%s): A single argument was expected", relname); args = trigger->tgargs; /* must be the field layout? */ tupdesc = rel->rd_att; /* * This gets the position in the tuple of the field we want. args[0] being * the name of the field to update, as passed in from the trigger. */ attnum = SPI_fnumber(tupdesc, args[0]); /* * This is where we check to see if the field we are supposed to update * even exists. The above function must return -1 if name not found? */ if (attnum < 0) ereport(ERROR, (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), errmsg("\"%s\" has no attribute \"%s\"", relname, args[0]))); /* * Check the target field has an allowed type, and get the current * datetime as a value of that type. */ atttypid = SPI_gettypeid(tupdesc, attnum); if (atttypid == TIMESTAMPOID) newdt = DirectFunctionCall3(timestamp_in, CStringGetDatum("now"), ObjectIdGetDatum(InvalidOid), Int32GetDatum(-1)); else if (atttypid == TIMESTAMPTZOID) newdt = DirectFunctionCall3(timestamptz_in, CStringGetDatum("now"), ObjectIdGetDatum(InvalidOid), Int32GetDatum(-1)); else { ereport(ERROR, (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), errmsg("attribute \"%s\" of \"%s\" must be type TIMESTAMP or TIMESTAMPTZ", args[0], relname))); newdt = (Datum) 0; /* keep compiler quiet */ } /* 1 is the number of items in the arrays attnum and newdt. attnum is the positional number of the field to be updated. newdt is the new datetime stamp. NOTE that attnum and newdt are not arrays, but then a 1 element array is not an array any more then they are. Thus, they can be considered a one element array. */ rettuple = SPI_modifytuple(rel, rettuple, 1, &attnum, &newdt, NULL); if (rettuple == NULL) /* internal error */ elog(ERROR, "moddatetime (%s): %d returned by SPI_modifytuple", relname, SPI_result); /* Clean up */ pfree(relname); return PointerGetDatum(rettuple); }
Datum partition_insert_trigger(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; char *date_time; char child_table[sizeof(TABLE)+sizeof("2012_09_10")] = TABLE; int partition_field; Relation child_table_id; Oid child_table_oid; BulkInsertState bistate = GetBulkInsertState(); TupleTableSlot *slot; EState *estate = CreateExecutorState(); ResultRelInfo *resultRelInfo = makeNode(ResultRelInfo); List *recheckIndexes = NIL; /* make sure it's called as a trigger at all */ if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "partition_insert_trigger: not called by trigger manager"); /* Sanity checks */ if (!TRIGGER_FIRED_BY_INSERT(trigdata->tg_event) || !TRIGGER_FIRED_BEFORE(trigdata->tg_event)) elog(ERROR, "partition_insert_trigger: not called on insert before"); #ifdef DEBUG elog(INFO, "Trigger Called for: %s", SPI_getrelname(trigdata->tg_relation)); #endif // Get the field number for the partition partition_field = SPI_fnumber(trigdata->tg_relation->rd_att, PARTITION_COLUMN); // Get the value for the partition_field date_time = SPI_getvalue(trigdata->tg_trigtuple, trigdata->tg_relation->rd_att, partition_field); //make sure date was specified if (!date_time) elog(ERROR, "You cannot insert data without specifying a value for the column %s", PARTITION_COLUMN); #ifdef DEBUG elog(INFO, "Trying to insert date_time=%s", date_time); #endif //add date_time, child_table_2012_01_23 strncpy(child_table + sizeof(TABLE) -1 , date_time, 4); //2012 child_table[sizeof(TABLE) + 3] = SEPARATOR; //2012_ strncpy(child_table + sizeof(TABLE) + 4 , date_time + 5, 2); //2012_01 child_table[sizeof(TABLE) + 6] = SEPARATOR; //2012_01_ strncpy(child_table + sizeof(TABLE) + 7 , date_time + 8, 2); //2012_01_23 #ifdef DEBUG elog(INFO, "New table will be %s", child_table); #endif pfree(date_time); //if you care about triggers on the child tables, call ExecBRInsertTriggers //don't care, continue //get the OID of the table we are looking for to insert child_table_oid = RelnameGetRelid(child_table); //Look for child child_table if (child_table_oid == InvalidOid){ elog(INFO, "partition_insert_trigger: Invalid child table %s, inserting data to main table %s", child_table, TABLE); return PointerGetDatum(trigdata->tg_trigtuple); } //get the descriptor of the table we are looking for child_table_id = RelationIdGetRelation(child_table_oid); //Get the child relation descriptor if (child_table_id == NULL){ elog(ERROR, "partition_insert_trigger: Failed to locate relation for child table %s, inserting data to main table %s", child_table, TABLE); return PointerGetDatum(trigdata->tg_trigtuple); } //set the resultRelInfo resultRelInfo->ri_RangeTableIndex = 1; /* dummy */ resultRelInfo->ri_RelationDesc = child_table_id; //setup the estate, not sure why estate->es_result_relations = resultRelInfo; estate->es_num_result_relations = 1; estate->es_result_relation_info = resultRelInfo; /* Set up a tuple slot not sure why yet */ slot = MakeSingleTupleTableSlot(trigdata->tg_relation->rd_att); ExecStoreTuple(trigdata->tg_trigtuple, slot, InvalidBuffer, false); //heap_insert(child_table_id, trigdata->tg_trigtuple, GetCurrentCommandId(true), use_wal, bistate); simple_heap_insert(child_table_id, trigdata->tg_trigtuple); if (resultRelInfo->ri_NumIndices > 0) recheckIndexes = ExecInsertIndexTuples(slot, &(trigdata->tg_trigtuple->t_self), estate); // not sure if this would work CatalogUpdateIndexes(child_table_id, trigdata->tg_trigtuple) //free the used memory list_free(recheckIndexes); ExecDropSingleTupleTableSlot(slot); //saw this somewhere :P RelationClose(child_table_id); //Must be called to free the relation memory page FreeBulkInsertState(bistate); // ? not sure if needed ? //If return next line will add to the regular table //return PointerGetDatum(trigdata->tg_trigtuple); //Return Null data, still have to figure out to return a proper X rows affected return PointerGetDatum(NULL); }
/* table_log() trigger function for logging table changes parameter: - log table name (optional) return: - trigger data (for Pg) */ Datum table_log(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; int ret; char query[250 + NAMEDATALEN]; /* for getting table infos (250 chars (+ one times the length of all names) should be enough) */ int number_columns = 0; /* counts the number columns in the table */ int number_columns_log = 0; /* counts the number columns in the table */ char *orig_schema; char *log_schema; char *log_table; int use_session_user = 0; /* should we write the current (session) user to the log table? */ /* * Some checks first... */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "start table_log()"); #endif /* TABLE_LOG_DEBUG */ /* called by trigger manager? */ if (!CALLED_AS_TRIGGER(fcinfo)) { elog(ERROR, "table_log: not fired by trigger manager"); } /* must only be called for ROW trigger */ if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event)) { elog(ERROR, "table_log: can't process STATEMENT events"); } /* must only be called AFTER */ if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) { elog(ERROR, "table_log: must be fired after event"); } /* now connect to SPI manager */ ret = SPI_connect(); if (ret != SPI_OK_CONNECT) { elog(ERROR, "table_log: SPI_connect returned %d", ret); } /* get schema name for the table, in case we need it later */ orig_schema = get_namespace_name(RelationGetNamespace(trigdata->tg_relation)); #ifdef TABLE_LOG_DEBUG elog(NOTICE, "prechecks done, now getting original table attributes"); #endif /* TABLE_LOG_DEBUG */ number_columns = count_columns(trigdata->tg_relation->rd_att); if (number_columns < 1) { elog(ERROR, "table_log: number of columns in table is < 1, can this happen?"); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "number columns in orig table: %i", number_columns); #endif /* TABLE_LOG_DEBUG */ if (trigdata->tg_trigger->tgnargs > 3) { elog(ERROR, "table_log: too many arguments to trigger"); } /* name of the log schema */ if (trigdata->tg_trigger->tgnargs > 2) { /* check if a log schema argument is given, if yes, use it */ log_schema = trigdata->tg_trigger->tgargs[2]; } else { /* if no, use orig_schema */ log_schema = orig_schema; } /* should we write the current user? */ if (trigdata->tg_trigger->tgnargs > 1) { /* check if a second argument is given */ /* if yes, use it, if it is true */ if (atoi(trigdata->tg_trigger->tgargs[1]) == 1) { use_session_user = 1; #ifdef TABLE_LOG_DEBUG elog(NOTICE, "will write session user to 'trigger_user'"); #endif /* TABLE_LOG_DEBUG */ } } /* name of the log table */ if (trigdata->tg_trigger->tgnargs > 0) { /* check if a logtable argument is given */ /* if yes, use it */ log_table = (char *) palloc((strlen(trigdata->tg_trigger->tgargs[0]) + 2) * sizeof(char)); sprintf(log_table, "%s", trigdata->tg_trigger->tgargs[0]); } else { /* if no, use 'table name' + '_log' */ log_table = (char *) palloc((strlen(do_quote_ident(SPI_getrelname(trigdata->tg_relation))) + 5) * sizeof(char)); sprintf(log_table, "%s_log", SPI_getrelname(trigdata->tg_relation)); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "log table: %s", log_table); #endif /* TABLE_LOG_DEBUG */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "now check, if log table exists"); #endif /* TABLE_LOG_DEBUG */ /* get the number columns in the table */ snprintf(query, 249, "%s.%s", do_quote_ident(log_schema), do_quote_ident(log_table)); number_columns_log = count_columns(RelationNameGetTupleDesc(query)); if (number_columns_log < 1) { elog(ERROR, "could not get number columns in relation %s", log_table); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "number columns in log table: %i", number_columns_log); #endif /* TABLE_LOG_DEBUG */ /* check if the logtable has 3 (or now 4) columns more than our table */ /* +1 if we should write the session user */ if (use_session_user == 0) { /* without session user */ if (number_columns_log != number_columns + 3 && number_columns_log != number_columns + 4) { elog(ERROR, "number colums in relation %s(%d) does not match columns in %s(%d)", SPI_getrelname(trigdata->tg_relation), number_columns, log_table, number_columns_log); } } else { /* with session user */ if (number_columns_log != number_columns + 3 + 1 && number_columns_log != number_columns + 4 + 1) { elog(ERROR, "number colums in relation %s does not match columns in %s", SPI_getrelname(trigdata->tg_relation), log_table); } } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "log table OK"); #endif /* TABLE_LOG_DEBUG */ /* For each column in key ... */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "copy data ..."); #endif /* TABLE_LOG_DEBUG */ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) { /* trigger called from INSERT */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "mode: INSERT -> new"); #endif /* TABLE_LOG_DEBUG */ __table_log(trigdata, "INSERT", "new", trigdata->tg_trigtuple, number_columns, log_table, use_session_user, log_schema); } else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) { /* trigger called from UPDATE */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "mode: UPDATE -> old"); #endif /* TABLE_LOG_DEBUG */ __table_log(trigdata, "UPDATE", "old", trigdata->tg_trigtuple, number_columns, log_table, use_session_user, log_schema); #ifdef TABLE_LOG_DEBUG elog(NOTICE, "mode: UPDATE -> new"); #endif /* TABLE_LOG_DEBUG */ __table_log(trigdata, "UPDATE", "new", trigdata->tg_newtuple, number_columns, log_table, use_session_user, log_schema); } else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) { /* trigger called from DELETE */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "mode: DELETE -> old"); #endif /* TABLE_LOG_DEBUG */ __table_log(trigdata, "DELETE", "old", trigdata->tg_trigtuple, number_columns, log_table, use_session_user, log_schema); } else { elog(ERROR, "trigger fired by unknown event"); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "cleanup, trigger done"); #endif /* TABLE_LOG_DEBUG */ /* clean up */ pfree(log_table); /* close SPI connection */ SPI_finish(); /* return trigger data */ return PointerGetDatum(trigdata->tg_trigtuple); }
Datum funny_dup17(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; TransactionId *xid; int *level; bool *recursion; Relation rel; TupleDesc tupdesc; HeapTuple tuple; char *query, *fieldval, *fieldtype; char *when; int inserted; int selected = 0; int ret; if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "funny_dup17: not fired by trigger manager"); tuple = trigdata->tg_trigtuple; rel = trigdata->tg_relation; tupdesc = rel->rd_att; if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) { xid = &fd17b_xid; level = &fd17b_level; recursion = &fd17b_recursion; when = "BEFORE"; } else { xid = &fd17a_xid; level = &fd17a_level; recursion = &fd17a_recursion; when = "AFTER "; } if (!TransactionIdIsCurrentTransactionId(*xid)) { *xid = GetCurrentTransactionId(); *level = 0; *recursion = true; } if (*level == 17) { *recursion = false; return PointerGetDatum(tuple); } if (!(*recursion)) return PointerGetDatum(tuple); (*level)++; SPI_connect(); fieldval = SPI_getvalue(tuple, tupdesc, 1); fieldtype = SPI_gettype(tupdesc, 1); query = (char *) palloc(100 + NAMEDATALEN * 3 + strlen(fieldval) + strlen(fieldtype)); sprintf(query, "insert into %s select * from %s where %s = '%s'::%s", SPI_getrelname(rel), SPI_getrelname(rel), SPI_fname(tupdesc, 1), fieldval, fieldtype); if ((ret = SPI_exec(query, 0)) < 0) elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d", when, *level, ret); inserted = SPI_processed; sprintf(query, "select count (*) from %s where %s = '%s'::%s", SPI_getrelname(rel), SPI_fname(tupdesc, 1), fieldval, fieldtype); if ((ret = SPI_exec(query, 0)) < 0) elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (select ...) returned %d", when, *level, ret); if (SPI_processed > 0) { selected = DatumGetInt32(DirectFunctionCall1(int4in, CStringGetDatum(SPI_getvalue( SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1 )))); } elog(DEBUG4, "funny_dup17 (fired %s) on level %3d: %d/%d tuples inserted/selected", when, *level, inserted, selected); SPI_finish(); (*level)--; if (*level == 0) *xid = InvalidTransactionId; return PointerGetDatum(tuple); }
Datum autoinc(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; Trigger *trigger; /* to get trigger name */ int nargs; /* # of arguments */ int *chattrs; /* attnums of attributes to change */ int chnattrs = 0; /* # of above */ Datum *newvals; /* vals of above */ char **args; /* arguments */ char *relname; /* triggered relation name */ Relation rel; /* triggered relation */ HeapTuple rettuple = NULL; TupleDesc tupdesc; /* tuple description */ bool isnull; int i; if (!CALLED_AS_TRIGGER(fcinfo)) /* internal error */ elog(ERROR, "not fired by trigger manager"); if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) /* internal error */ elog(ERROR, "must be fired for row"); if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) /* internal error */ elog(ERROR, "must be fired before event"); if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) rettuple = trigdata->tg_trigtuple; else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) rettuple = trigdata->tg_newtuple; else /* internal error */ elog(ERROR, "cannot process DELETE events"); rel = trigdata->tg_relation; relname = SPI_getrelname(rel); trigger = trigdata->tg_trigger; nargs = trigger->tgnargs; if (nargs <= 0 || nargs % 2 != 0) /* internal error */ elog(ERROR, "autoinc (%s): even number gt 0 of arguments was expected", relname); args = trigger->tgargs; tupdesc = rel->rd_att; chattrs = (int *) palloc(nargs / 2 * sizeof(int)); newvals = (Datum *) palloc(nargs / 2 * sizeof(Datum)); for (i = 0; i < nargs;) { int attnum = SPI_fnumber(tupdesc, args[i]); int32 val; Datum seqname; if (attnum < 0) ereport(ERROR, (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), errmsg("\"%s\" has no attribute \"%s\"", relname, args[i]))); if (SPI_gettypeid(tupdesc, attnum) != INT4OID) ereport(ERROR, (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), errmsg("attribute \"%s\" of \"%s\" must be type INT4", args[i], relname))); val = DatumGetInt32(SPI_getbinval(rettuple, tupdesc, attnum, &isnull)); if (!isnull && val != 0) { i += 2; continue; } i++; chattrs[chnattrs] = attnum; seqname = CStringGetTextDatum(args[i]); newvals[chnattrs] = DirectFunctionCall1(nextval, seqname); /* nextval now returns int64; coerce down to int32 */ newvals[chnattrs] = Int32GetDatum((int32) DatumGetInt64(newvals[chnattrs])); if (DatumGetInt32(newvals[chnattrs]) == 0) { newvals[chnattrs] = DirectFunctionCall1(nextval, seqname); newvals[chnattrs] = Int32GetDatum((int32) DatumGetInt64(newvals[chnattrs])); } pfree(DatumGetTextP(seqname)); chnattrs++; i++; } if (chnattrs > 0) { rettuple = SPI_modifytuple(rel, rettuple, chnattrs, chattrs, newvals, NULL); if (rettuple == NULL) /* internal error */ elog(ERROR, "autoinc (%s): %d returned by SPI_modifytuple", relname, SPI_result); } pfree(relname); pfree(chattrs); pfree(newvals); return PointerGetDatum(rettuple); }
static PyObject * PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc, HeapTuple *rv) { TriggerData *tdata = (TriggerData *) fcinfo->context; PyObject *pltname, *pltevent, *pltwhen, *pltlevel, *pltrelid, *plttablename, *plttableschema; PyObject *pltargs, *pytnew, *pytold; PyObject *volatile pltdata = NULL; char *stroid; PG_TRY(); { pltdata = PyDict_New(); if (!pltdata) PLy_elog(ERROR, "could not create new dictionary while building trigger arguments"); pltname = PyString_FromString(tdata->tg_trigger->tgname); PyDict_SetItemString(pltdata, "name", pltname); Py_DECREF(pltname); stroid = DatumGetCString(DirectFunctionCall1(oidout, ObjectIdGetDatum(tdata->tg_relation->rd_id))); pltrelid = PyString_FromString(stroid); PyDict_SetItemString(pltdata, "relid", pltrelid); Py_DECREF(pltrelid); pfree(stroid); stroid = SPI_getrelname(tdata->tg_relation); plttablename = PyString_FromString(stroid); PyDict_SetItemString(pltdata, "table_name", plttablename); Py_DECREF(plttablename); pfree(stroid); stroid = SPI_getnspname(tdata->tg_relation); plttableschema = PyString_FromString(stroid); PyDict_SetItemString(pltdata, "table_schema", plttableschema); Py_DECREF(plttableschema); pfree(stroid); if (TRIGGER_FIRED_BEFORE(tdata->tg_event)) pltwhen = PyString_FromString("BEFORE"); else if (TRIGGER_FIRED_AFTER(tdata->tg_event)) pltwhen = PyString_FromString("AFTER"); else if (TRIGGER_FIRED_INSTEAD(tdata->tg_event)) pltwhen = PyString_FromString("INSTEAD OF"); else { elog(ERROR, "unrecognized WHEN tg_event: %u", tdata->tg_event); pltwhen = NULL; /* keep compiler quiet */ } PyDict_SetItemString(pltdata, "when", pltwhen); Py_DECREF(pltwhen); if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event)) { pltlevel = PyString_FromString("ROW"); PyDict_SetItemString(pltdata, "level", pltlevel); Py_DECREF(pltlevel); if (TRIGGER_FIRED_BY_INSERT(tdata->tg_event)) { pltevent = PyString_FromString("INSERT"); PyDict_SetItemString(pltdata, "old", Py_None); pytnew = PLyDict_FromTuple(&(proc->result), tdata->tg_trigtuple, tdata->tg_relation->rd_att); PyDict_SetItemString(pltdata, "new", pytnew); Py_DECREF(pytnew); *rv = tdata->tg_trigtuple; } else if (TRIGGER_FIRED_BY_DELETE(tdata->tg_event)) { pltevent = PyString_FromString("DELETE"); PyDict_SetItemString(pltdata, "new", Py_None); pytold = PLyDict_FromTuple(&(proc->result), tdata->tg_trigtuple, tdata->tg_relation->rd_att); PyDict_SetItemString(pltdata, "old", pytold); Py_DECREF(pytold); *rv = tdata->tg_trigtuple; } else if (TRIGGER_FIRED_BY_UPDATE(tdata->tg_event)) { pltevent = PyString_FromString("UPDATE"); pytnew = PLyDict_FromTuple(&(proc->result), tdata->tg_newtuple, tdata->tg_relation->rd_att); PyDict_SetItemString(pltdata, "new", pytnew); Py_DECREF(pytnew); pytold = PLyDict_FromTuple(&(proc->result), tdata->tg_trigtuple, tdata->tg_relation->rd_att); PyDict_SetItemString(pltdata, "old", pytold); Py_DECREF(pytold); *rv = tdata->tg_newtuple; } else { elog(ERROR, "unrecognized OP tg_event: %u", tdata->tg_event); pltevent = NULL; /* keep compiler quiet */ } PyDict_SetItemString(pltdata, "event", pltevent); Py_DECREF(pltevent); } else if (TRIGGER_FIRED_FOR_STATEMENT(tdata->tg_event)) { pltlevel = PyString_FromString("STATEMENT"); PyDict_SetItemString(pltdata, "level", pltlevel); Py_DECREF(pltlevel); PyDict_SetItemString(pltdata, "old", Py_None); PyDict_SetItemString(pltdata, "new", Py_None); *rv = NULL; if (TRIGGER_FIRED_BY_INSERT(tdata->tg_event)) pltevent = PyString_FromString("INSERT"); else if (TRIGGER_FIRED_BY_DELETE(tdata->tg_event)) pltevent = PyString_FromString("DELETE"); else if (TRIGGER_FIRED_BY_UPDATE(tdata->tg_event)) pltevent = PyString_FromString("UPDATE"); else if (TRIGGER_FIRED_BY_TRUNCATE(tdata->tg_event)) pltevent = PyString_FromString("TRUNCATE"); else { elog(ERROR, "unrecognized OP tg_event: %u", tdata->tg_event); pltevent = NULL; /* keep compiler quiet */ } PyDict_SetItemString(pltdata, "event", pltevent); Py_DECREF(pltevent); } else elog(ERROR, "unrecognized LEVEL tg_event: %u", tdata->tg_event); if (tdata->tg_trigger->tgnargs) { /* * all strings... */ int i; PyObject *pltarg; pltargs = PyList_New(tdata->tg_trigger->tgnargs); for (i = 0; i < tdata->tg_trigger->tgnargs; i++) { pltarg = PyString_FromString(tdata->tg_trigger->tgargs[i]); /* * stolen, don't Py_DECREF */ PyList_SetItem(pltargs, i, pltarg); } } else { Py_INCREF(Py_None); pltargs = Py_None; } PyDict_SetItemString(pltdata, "args", pltargs); Py_DECREF(pltargs); } PG_CATCH(); { Py_XDECREF(pltdata); PG_RE_THROW(); } PG_END_TRY(); return pltdata; }
/* * suppress_redundant_updates_trigger * * This trigger function will inhibit an update from being done * if the OLD and NEW records are identical. */ Datum suppress_redundant_updates_trigger(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; HeapTuple newtuple, oldtuple, rettuple; HeapTupleHeader newheader, oldheader; /* make sure it's called as a trigger */ if (!CALLED_AS_TRIGGER(fcinfo)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), errmsg("suppress_redundant_updates_trigger: must be called as trigger"))); /* and that it's called on update */ if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), errmsg("suppress_redundant_updates_trigger: must be called on update"))); /* and that it's called before update */ if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), errmsg("suppress_redundant_updates_trigger: must be called before update"))); /* and that it's called for each row */ if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), errmsg("suppress_redundant_updates_trigger: must be called for each row"))); /* get tuple data, set default result */ rettuple = newtuple = trigdata->tg_newtuple; oldtuple = trigdata->tg_trigtuple; newheader = newtuple->t_data; oldheader = oldtuple->t_data; /* * We are called before the OID, if any, has been transcribed from the old * tuple to the new (in heap_update). To avoid a bogus compare failure, * copy the OID now. But check that someone didn't already put another * OID value into newtuple. (That's not actually possible at present, but * maybe someday.) */ if (trigdata->tg_relation->rd_rel->relhasoids && !OidIsValid(HeapTupleHeaderGetOid(newheader))) HeapTupleHeaderSetOid(newheader, HeapTupleHeaderGetOid(oldheader)); /* if the tuple payload is the same ... */ if (newtuple->t_len == oldtuple->t_len && newheader->t_hoff == oldheader->t_hoff && (HeapTupleHeaderGetNatts(newheader) == HeapTupleHeaderGetNatts(oldheader)) && ((newheader->t_infomask & ~HEAP_XACT_MASK) == (oldheader->t_infomask & ~HEAP_XACT_MASK)) && memcmp(((char *) newheader) + offsetof(HeapTupleHeaderData, t_bits), ((char *) oldheader) + offsetof(HeapTupleHeaderData, t_bits), newtuple->t_len - offsetof(HeapTupleHeaderData, t_bits)) == 0) { /* ... then suppress the update */ rettuple = NULL; } return PointerGetDatum(rettuple); }