/* * dbms_stats_import * Import exported statistics from stdin or a file. * * Order of arguments: * 1) schema name * 2) relation oid * 3) attribute name * 4) absolute path of source file, or 'stdin' (case insensitive) */ Datum dbms_stats_import(PG_FUNCTION_ARGS) { char *nspname; char *relname; char *attname; char *filename; /* filename, or NULL for STDIN */ int ret; int i; uint32 r_num; HeapTuple *r_tups; TupleDesc r_tupdesc; SPIPlanPtr r_upd_plan = NULL; SPIPlanPtr r_ins_plan = NULL; SPIPlanPtr c_sel_plan = NULL; SPIPlanPtr c_del_plan = NULL; SPIPlanPtr c_ins_plan = NULL; /* get validated arguments */ get_args(fcinfo, &nspname, &relname, &attname, &filename); /* for debug use */ elog(DEBUG3, "%s() f=%s n=%s r=%s a=%s", __FUNCTION__, filename ? filename : "(null)", nspname ? nspname : "(null)", relname ? relname : "(null)", attname ? attname : "(null)"); /* connect to SPI */ ret = SPI_connect(); if (ret != SPI_OK_CONNECT) elog(ERROR, "pg_dbms_stats: SPI_connect => %d", ret); /* lock dummy statistics tables. */ spi_exec_utility("LOCK dbms_stats.relation_stats_locked" " IN SHARE UPDATE EXCLUSIVE MODE"); spi_exec_utility("LOCK dbms_stats.column_stats_locked" " IN SHARE UPDATE EXCLUSIVE MODE"); /* * Create a temp table to save the statistics to import. * This table should fit with the content of export files. */ spi_exec_utility("CREATE TEMP TABLE dbms_stats_work_stats (" "nspname name NOT NULL," "relname name NOT NULL," "relpages int4 NOT NULL," "reltuples float4 NOT NULL," #if PG_VERSION_NUM >= 90200 "relallvisible int4 NOT NULL," #endif "curpages int4 NOT NULL," "last_analyze timestamp with time zone," "last_autoanalyze timestamp with time zone," "attname name," "nspname_of_typename name," "typname name," "atttypmod int4," "stainherit bool," "stanullfrac float4," "stawidth int4," "stadistinct float4," "stakind1 int2," "stakind2 int2," "stakind3 int2," "stakind4 int2," #if PG_VERSION_NUM >= 90200 "stakind5 int2," #endif "staop1 oid," "staop2 oid," "staop3 oid," "staop4 oid," #if PG_VERSION_NUM >= 90200 "staop5 oid," #endif "stanumbers1 float4[]," "stanumbers2 float4[]," "stanumbers3 float4[]," "stanumbers4 float4[]," #if PG_VERSION_NUM >= 90200 "stanumbers5 float4[]," #endif "stavalues1 dbms_stats.anyarray," "stavalues2 dbms_stats.anyarray," "stavalues3 dbms_stats.anyarray," "stavalues4 dbms_stats.anyarray" #if PG_VERSION_NUM >= 90200 ",stavalues5 dbms_stats.anyarray" #endif ")"); /* load the statistics from export file to the temp table */ import_stats_from_file(filename, nspname, relname, attname); /* Determine the Oid of local table from the tablename and schemaname. */ ret = SPI_execute("SELECT DISTINCT w.nspname, w.relname, c.oid, " "w.relpages, w.reltuples, " "w.curpages, w.last_analyze, w.last_autoanalyze " #if PG_VERSION_NUM >= 90200 ",w.relallvisible " #endif "FROM pg_catalog.pg_class c " "JOIN pg_catalog.pg_namespace n " "ON (c.relnamespace = n.oid) " "RIGHT JOIN dbms_stats_work_stats w " "ON (w.relname = c.relname AND w.nspname = n.nspname) " "ORDER BY 1, 2", false, 0); if (ret != SPI_OK_SELECT) elog(ERROR, "pg_dbms_stats: SPI_execute => %d", ret); /* * If there is no record in the staging table after loading source and * deleting unnecessary records, we treat it as an error. */ if (SPI_processed == 0) elog(ERROR, "no per-table statistic data to be imported"); /* */ r_num = SPI_processed; r_tups = SPI_tuptable->vals; r_tupdesc = SPI_tuptable->tupdesc; for (i = 0; i < r_num; i++) { bool isnull; Datum w_nspname; Datum w_relname; Datum w_relid; Datum values[9]; char nulls[9] = {'t', 't', 't', 't', 't', 't', 't', 't', 't'}; Oid r_types[9] = {NAMEOID, NAMEOID, INT4OID, FLOAT4OID, INT4OID, TIMESTAMPTZOID, TIMESTAMPTZOID, OIDOID, INT4OID}; Oid c_types[5] = {OIDOID, INT2OID, NAMEOID, NAMEOID, NAMEOID}; uint32 c_num; TupleDesc c_tupdesc; HeapTuple *c_tups; int j; values[0] = w_nspname = SPI_getbinval(r_tups[i], r_tupdesc, 1, &isnull); values[1] = w_relname = SPI_getbinval(r_tups[i], r_tupdesc, 2, &isnull); values[7] = w_relid = SPI_getbinval(r_tups[i], r_tupdesc, 3, &isnull); if (isnull) { elog(WARNING, "relation \"%s.%s\" does not exist", DatumGetName(w_nspname)->data, DatumGetName(w_relname)->data); continue; } values[2] = SPI_getbinval(r_tups[i], r_tupdesc, 4, &isnull); values[3] = SPI_getbinval(r_tups[i], r_tupdesc, 5, &isnull); values[4] = SPI_getbinval(r_tups[i], r_tupdesc, 6, &isnull); values[5] = SPI_getbinval(r_tups[i], r_tupdesc, 7, &isnull); nulls[5] = isnull ? 'n' : 't'; values[6] = SPI_getbinval(r_tups[i], r_tupdesc, 8, &isnull); nulls[6] = isnull ? 'n' : 't'; values[8] = SPI_getbinval(r_tups[i], r_tupdesc, 9, &isnull); /* * First we try UPDATE with the oid. When no record matched, try * INSERT. We can't use DELETE-then-INSERT method because we have FK * on relation_stats_locked so DELETE would delete child records in * column_stats_locked undesirably. */ spi_exec_query("UPDATE dbms_stats.relation_stats_locked SET " "relname = quote_ident($1) || '.' || quote_ident($2), " "relpages = $3, reltuples = $4, " #if PG_VERSION_NUM >= 90200 "relallvisible = $9, " #endif "curpages = $5, last_analyze = $6, last_autoanalyze = $7 " "WHERE relid = $8", RELATION_PARAM_NUM, r_types, &r_upd_plan, values, nulls, SPI_OK_UPDATE); if (SPI_processed == 0) { spi_exec_query("INSERT INTO dbms_stats.relation_stats_locked " "(relname, relpages, reltuples, curpages, " "last_analyze, last_autoanalyze, relid" #if PG_VERSION_NUM >= 90200 ", relallvisible" #endif ") VALUES (quote_ident($1) || '.' || quote_ident($2), " "$3, $4, $5, $6, $7, $8" #if PG_VERSION_NUM >= 90200 ", $9" #endif ")", RELATION_PARAM_NUM, r_types, &r_ins_plan, values, nulls, SPI_OK_INSERT); /* If we failed to insert, we can't proceed. */ if (SPI_processed != 1) elog(ERROR, "failed to insert import data"); } elog(DEBUG2, "\"%s.%s\" relation statistic import", DatumGetName(w_nspname)->data, DatumGetName(w_relname)->data); /* * Determine the attnum of the attribute with given name, and load * statistics from temp table into dbms.column_stats_locked. */ spi_exec_query("SELECT w.stainherit, w.attname, a.attnum, " "w.nspname_of_typename, tn.nspname, " "w.typname, t.typname, w.atttypmod, a.atttypmod " "FROM pg_catalog.pg_class c " "JOIN pg_catalog.pg_namespace cn " "ON (cn.oid = c.relnamespace) " "JOIN pg_catalog.pg_attribute a " "ON (a.attrelid = c.oid) " "JOIN pg_catalog.pg_type t " "ON (t.oid = a.atttypid) " "JOIN pg_catalog.pg_namespace tn " "ON (tn.oid = t.typnamespace) " "RIGHT JOIN dbms_stats_work_stats w " "ON (w.nspname = cn.nspname AND w.relname = c.relname " "AND (w.attname = a.attname OR w.attname = '')) " "WHERE w.nspname = $1 AND w.relname = $2 " "AND a.attnum > 0" "ORDER BY 1, 3, 2", 2, r_types, &c_sel_plan, values, NULL, SPI_OK_SELECT); /* This query ought to return at least one record. */ if (SPI_processed == 0) elog(ERROR, "no per-column statistic data to be imported"); values[0] = w_relid; values[2] = w_nspname; values[3] = w_relname; c_num = SPI_processed; c_tups = SPI_tuptable->vals; c_tupdesc = SPI_tuptable->tupdesc; for (j = 0; j < c_num; j++) { char *w_typnamespace; char *a_typnamespace; char *w_typname; char *a_typname; int w_typmod; int a_typmod; /* * If we have only per-relation statistics in source, all of * column_stats_effective for per-column statistics are NULL. */ (void) SPI_getbinval(c_tups[j], c_tupdesc, 1, &isnull); if (isnull) continue; /* * If there is no column with given name, we skip the rest of * import process. */ values[4] = SPI_getbinval(c_tups[j], c_tupdesc, 2, &isnull); values[1] = SPI_getbinval(c_tups[j], c_tupdesc, 3, &isnull); if (isnull) { elog(WARNING, "column \"%s\" of \"%s.%s\" does not exist", DatumGetName(values[4])->data, DatumGetName(w_nspname)->data, DatumGetName(w_relname)->data); continue; } /* * If the destination column has different data type from source * column, we stop importing to avoid corrupted statistics. */ w_typnamespace = DatumGetName(SPI_getbinval(c_tups[j], c_tupdesc, 4, &isnull))->data; a_typnamespace = DatumGetName(SPI_getbinval(c_tups[j], c_tupdesc, 5, &isnull))->data; w_typname = DatumGetName(SPI_getbinval(c_tups[j], c_tupdesc, 6, &isnull))->data; a_typname = DatumGetName(SPI_getbinval(c_tups[j], c_tupdesc, 7, &isnull))->data; if (strcmp(w_typnamespace, a_typnamespace) != 0 || strcmp(w_typname, a_typname) != 0) { ereport(WARNING, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("column \"%s\" is of type \"%s.%s\"" " but import data is of type \"%s.%s\"", DatumGetName(values[4])->data, a_typnamespace, a_typname, w_typnamespace, w_typname))); continue; } /* * If the atttypmod of the destination column is different from the * one of source, column, we stop importing to avoid corrupted * statistics. */ w_typmod = DatumGetInt32(SPI_getbinval(c_tups[j], c_tupdesc, 8, &isnull)); a_typmod = DatumGetInt32(SPI_getbinval(c_tups[j], c_tupdesc, 9, &isnull)); if (w_typmod != a_typmod) { ereport(WARNING, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("column \"%s\" is of atttypmod %d" " but import data is of atttypmod %d", DatumGetName(values[4])->data, a_typmod, a_typmod))); continue; } /* * First delete old dummy statistics, and import new one. We use * DELETE-then-INSERT method here to simplify codes. */ spi_exec_query("DELETE FROM dbms_stats.column_stats_locked " "WHERE starelid = $1 AND staattnum = $2", 2, c_types, &c_del_plan, values, NULL, SPI_OK_DELETE); spi_exec_query("INSERT INTO dbms_stats.column_stats_locked " "SELECT $1, $2, " "stainherit, stanullfrac, stawidth, stadistinct, " "stakind1, stakind2, stakind3, stakind4, " #if PG_VERSION_NUM >= 90200 "stakind5, " #endif "staop1, staop2, staop3, staop4, " #if PG_VERSION_NUM >= 90200 "staop5, " #endif "stanumbers1, stanumbers2, stanumbers3, stanumbers4, " #if PG_VERSION_NUM >= 90200 "stanumbers5, " #endif "stavalues1, stavalues2, stavalues3, stavalues4 " #if PG_VERSION_NUM >= 90200 ", stavalues5 " #endif "FROM dbms_stats_work_stats " "WHERE nspname = $3 AND relname = $4 " "AND attname = $5 " "ORDER BY 3", 5, c_types, &c_ins_plan, values, NULL, SPI_OK_INSERT); elog(DEBUG2, "\"%s.%s.%s\" column statistic import", DatumGetName(w_nspname)->data, DatumGetName(w_relname)->data, DatumGetName(values[4])->data); } if (c_num == 0) elog(DEBUG2, "\"%s.%s\" column statistic no data", DatumGetName(w_nspname)->data, DatumGetName(w_relname)->data); } /* release the cached plan */ SPI_freeplan(r_upd_plan); SPI_freeplan(r_ins_plan); SPI_freeplan(c_sel_plan); SPI_freeplan(c_del_plan); SPI_freeplan(c_ins_plan); /* delete the temp table */ spi_exec_utility("DROP TABLE dbms_stats_work_stats"); /* disconnect SPI */ ret = SPI_finish(); if (ret != SPI_OK_FINISH) elog(ERROR, "pg_dbms_stats: SPI_finish => %d", ret); /* * Recover the protocol state because it has been invalidated by our * COPY-from-stdin. */ if (filename == NULL) pq_puttextmessage('C', "dbms_stats_import"); PG_RETURN_VOID(); }
static void worker_spi_main(Datum main_arg) { /* Register functions for SIGTERM/SIGHUP management */ pqsignal(SIGHUP, worker_spi_sighup); pqsignal(SIGTERM, worker_spi_sigterm); /* We're now ready to receive signals */ BackgroundWorkerUnblockSignals(); /* Connect to our database */ BackgroundWorkerInitializeConnection("postgres", NULL); while (!got_sigterm) { int ret; int rc; StringInfoData buf; /* * Background workers mustn't call usleep() or any direct equivalent: * instead, they may wait on their process latch, which sleeps as * necessary, but is awakened if postmaster dies. That way the * background process goes away immediately in an emergency. */ rc = WaitLatch(&MyProc->procLatch, WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, 1000L); ResetLatch(&MyProc->procLatch); /* emergency bailout if postmaster has died */ if (rc & WL_POSTMASTER_DEATH) proc_exit(1); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); initStringInfo(&buf); /* Build the query string */ appendStringInfo(&buf, "SELECT count(*) FROM pg_class;"); ret = SPI_execute(buf.data, true, 0); /* Some error messages in case of incorrect handling */ if (ret != SPI_OK_SELECT) elog(FATAL, "SPI_execute failed: error code %d", ret); if (SPI_processed > 0) { int32 count; bool isnull; count = DatumGetInt32(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull)); elog(LOG, "Currently %d relations in database", count); } SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); } proc_exit(0); }
static void kill_idle_main(Datum main_arg) { StringInfoData buf; /* Register functions for SIGTERM/SIGHUP management */ pqsignal(SIGHUP, kill_idle_sighup); pqsignal(SIGTERM, kill_idle_sigterm); /* We're now ready to receive signals */ BackgroundWorkerUnblockSignals(); /* Connect to a database */ BackgroundWorkerInitializeConnection("postgres", NULL); /* Build query for process */ initStringInfo(&buf); kill_idle_build_query(&buf); while (!got_sigterm) { int rc, ret, i; /* Wait necessary amount of time */ rc = WaitLatch(&MyProc->procLatch, WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, kill_max_idle_time * 1000L, PG_WAIT_EXTENSION); ResetLatch(&MyProc->procLatch); /* Emergency bailout if postmaster has died */ if (rc & WL_POSTMASTER_DEATH) proc_exit(1); /* Process signals */ if (got_sighup) { int old_interval; /* Save old value of kill interval */ old_interval = kill_max_idle_time; /* Process config file */ ProcessConfigFile(PGC_SIGHUP); got_sighup = false; ereport(LOG, (errmsg("bgworker kill_idle signal: processed SIGHUP"))); /* Rebuild query if necessary */ if (old_interval != kill_max_idle_time) { resetStringInfo(&buf); initStringInfo(&buf); kill_idle_build_query(&buf); } } if (got_sigterm) { /* Simply exit */ ereport(LOG, (errmsg("bgworker kill_idle signal: processed SIGTERM"))); proc_exit(0); } /* Process idle connection kill */ SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); pgstat_report_activity(STATE_RUNNING, buf.data); /* Statement start time */ SetCurrentStatementStartTimestamp(); /* Execute query */ ret = SPI_execute(buf.data, false, 0); /* Some error handling */ if (ret != SPI_OK_SELECT) elog(FATAL, "Error when trying to kill idle connections"); /* Do some processing and log stuff disconnected */ for (i = 0; i < SPI_processed; i++) { int32 pidValue; bool isnull; char *datname = NULL; char *usename = NULL; char *client_addr = NULL; /* Fetch values */ pidValue = DatumGetInt32(SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1, &isnull)); usename = DatumGetCString(SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 3, &isnull)); datname = DatumGetCString(SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 4, &isnull)); client_addr = DatumGetCString(SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 5, &isnull)); /* Log what has been disconnected */ elog(LOG, "Disconnected idle connection: PID %d %s/%s/%s", pidValue, datname ? datname : "none", usename ? usename : "none", client_addr ? client_addr : "none"); } SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); pgstat_report_activity(STATE_IDLE, NULL); } /* No problems, so clean exit */ proc_exit(0); }
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 latter use. */ pplan = SPI_saveplan(pplan); if (pplan == NULL) elog(ERROR, "timetravel (%s): SPI_saveplan returned %d", relname, SPI_result); 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); }
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): attributes %s and %s must be of abstime type", relname, args[0], args[1]); } 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 %d", relname, 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 */ { HeapTuple tmptuple; tmptuple = SPI_copytuple(trigtuple); rettuple = SPI_modifytuple(rel, tmptuple, 1, &(attnum[1]), &newoff, NULL); SPI_freetuple(tmptuple); } else /* DELETE */ rettuple = trigtuple; SPI_finish(); /* don't forget say Bye to SPI mgr */ pfree(relname); return PointerGetDatum(rettuple); }
Datum plpgsql_call_handler(PG_FUNCTION_ARGS) { PLpgSQL_function *func; PLpgSQL_execstate *save_cur_estate; Datum retval; int rc; /* * Connect to SPI manager */ if ((rc = SPI_connect()) != SPI_OK_CONNECT) elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc)); /* Find or compile the function */ func = plpgsql_compile(fcinfo, false); /* Must save and restore prior value of cur_estate */ save_cur_estate = func->cur_estate; /* Mark the function as busy, so it can't be deleted from under us */ func->use_count++; PG_TRY(); { /* * Determine if called as function or trigger and call appropriate * subhandler */ if (CALLED_AS_TRIGGER(fcinfo)) retval = PointerGetDatum(plpgsql_exec_trigger(func, (TriggerData *) fcinfo->context)); else if (CALLED_AS_EVENT_TRIGGER(fcinfo)) { plpgsql_exec_event_trigger(func, (EventTriggerData *) fcinfo->context); retval = (Datum) 0; } else retval = plpgsql_exec_function(func, fcinfo); } PG_CATCH(); { /* Decrement use-count, restore cur_estate, and propagate error */ func->use_count--; func->cur_estate = save_cur_estate; PG_RE_THROW(); } PG_END_TRY(); func->use_count--; func->cur_estate = save_cur_estate; /* * Disconnect from SPI manager */ if ((rc = SPI_finish()) != SPI_OK_FINISH) elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc)); return retval; }
Datum cdb_set_oid(PG_FUNCTION_ARGS) { int32 maxoid = PG_GETARG_INT32(0); char *tempFileName = tempnam( NULL, "TMPCP" ); /* elog(NOTICE, "tempFileName = %s", tempFileName ); */ StringInfoData buffer; initStringInfo( &buffer ); appendStringInfo( &buffer, "%u\t0\n\\.\n", maxoid ); FILE *fptr = fopen(tempFileName, "w"); if ( strlen(buffer.data) != fwrite( buffer.data, 1, strlen(buffer.data), fptr) ) { ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("SPI_execute failed in in temp file write in cdb_set_oid" ))); } fclose(fptr); pfree(buffer.data); buffer.data = NULL; if ( SPI_OK_CONNECT != SPI_connect() ) { ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("SPI_connect failed in cdb_set_oid" ))); } if ( SPI_OK_UTILITY != SPI_execute( "CREATE TEMPORARY TABLE pgdump_oid (dummy integer) WITH OIDS", false, 0 ) ) { ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("SPI_execute failed in cdb_set_oid" ))); } initStringInfo( &buffer ); appendStringInfo( &buffer, "COPY pgdump_oid WITH OIDS FROM '%s'", tempFileName ); if ( SPI_OK_UTILITY != SPI_execute( buffer.data, false, 0 ) ) { ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("SPI_execute failed in copy command in cdb_set_oid" ))); } remove( tempFileName ); pfree(buffer.data); if ( SPI_OK_UTILITY != SPI_execute( "DROP TABLE pgdump_oid", false, 0 ) ) { ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("SPI_execute failed in cdb_set_oid" ))); } SPI_finish(); PG_RETURN_BOOL(true); }
static STANDARDIZER * CreateStd(char *lextab, char *gaztab, char *rultab) { STANDARDIZER *std; LEXICON *lex; LEXICON *gaz; RULES *rules; int err; int SPIcode; DBG("Enter: CreateStd"); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "CreateStd: couldn't open a connection to SPI"); } std = std_init(); if (!std) elog(ERROR, "CreateStd: could not allocate memory (std)"); lex = lex_init(std->err_p); if (!lex) { std_free(std); SPI_finish(); elog(ERROR, "CreateStd: could not allocate memory (lex)"); } err = load_lex(lex, lextab); if (err == -1) { lex_free(lex); std_free(std); SPI_finish(); elog(ERROR, "CreateStd: failed to load '%s' for lexicon", lextab); } gaz = lex_init(std->err_p); if (!gaz) { lex_free(lex); std_free(std); SPI_finish(); elog(ERROR, "CreateStd: could not allocate memory (gaz)"); } err = load_lex(gaz, gaztab); if (err == -1) { lex_free(gaz); lex_free(lex); std_free(std); SPI_finish(); elog(ERROR, "CreateStd: failed to load '%s' for gazeteer", gaztab); } rules = rules_init(std->err_p); if (!rules) { lex_free(gaz); lex_free(lex); std_free(std); SPI_finish(); elog(ERROR, "CreateStd: could not allocate memory (rules)"); } err = load_rules(rules, rultab); if (err == -1) { rules_free(rules); lex_free(gaz); lex_free(lex); std_free(std); SPI_finish(); elog(ERROR, "CreateStd: failed to load '%s' for rules", rultab); } std_use_lex(std, lex); std_use_gaz(std, gaz); std_use_rules(std, rules); std_ready_standardizer(std); SPI_finish(); return std; }
Datum crosstab(PG_FUNCTION_ARGS) { char *sql = text_to_cstring(PG_GETARG_TEXT_PP(0)); ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; Tuplestorestate *tupstore; TupleDesc tupdesc; int call_cntr; int max_calls; AttInMetadata *attinmeta; SPITupleTable *spi_tuptable; TupleDesc spi_tupdesc; bool firstpass; char *lastrowid; int i; int num_categories; MemoryContext per_query_ctx; MemoryContext oldcontext; int ret; int proc; /* check to see if caller supports us returning a tuplestore */ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("set-valued function called in context that cannot accept a set"))); if (!(rsinfo->allowedModes & SFRM_Materialize)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("materialize mode required, but it is not " \ "allowed in this context"))); per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) /* internal error */ elog(ERROR, "crosstab: SPI_connect returned %d", ret); /* Retrieve the desired rows */ ret = SPI_execute(sql, true, 0); proc = SPI_processed; /* If no qualifying tuples, fall out early */ if (ret != SPI_OK_SELECT || proc <= 0) { SPI_finish(); rsinfo->isDone = ExprEndResult; PG_RETURN_NULL(); } spi_tuptable = SPI_tuptable; spi_tupdesc = spi_tuptable->tupdesc; /*---------- * The provided SQL query must always return three columns. * * 1. rowname * the label or identifier for each row in the final result * 2. category * the label or identifier for each column in the final result * 3. values * the value for each column in the final result *---------- */ if (spi_tupdesc->natts != 3) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("invalid source data SQL statement"), errdetail("The provided SQL must return 3 " "columns: rowid, category, and values."))); /* get a tuple descriptor for our result type */ switch (get_call_result_type(fcinfo, NULL, &tupdesc)) { case TYPEFUNC_COMPOSITE: /* success */ break; case TYPEFUNC_RECORD: /* failed to determine actual type of RECORD */ ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context " "that cannot accept type record"))); break; default: /* result type isn't composite */ elog(ERROR, "return type must be a row type"); break; } /* * Check that return tupdesc is compatible with the data we got from SPI, * at least based on number and type of attributes */ if (!compatCrosstabTupleDescs(tupdesc, spi_tupdesc)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("return and sql tuple descriptions are " \ "incompatible"))); /* * switch to long-lived memory context */ oldcontext = MemoryContextSwitchTo(per_query_ctx); /* make sure we have a persistent copy of the result tupdesc */ tupdesc = CreateTupleDescCopy(tupdesc); /* initialize our tuplestore in long-lived context */ tupstore = tuplestore_begin_heap(rsinfo->allowedModes & SFRM_Materialize_Random, false, work_mem); MemoryContextSwitchTo(oldcontext); /* * Generate attribute metadata needed later to produce tuples from raw C * strings */ attinmeta = TupleDescGetAttInMetadata(tupdesc); /* total number of tuples to be examined */ max_calls = proc; /* the return tuple always must have 1 rowid + num_categories columns */ num_categories = tupdesc->natts - 1; firstpass = true; lastrowid = NULL; for (call_cntr = 0; call_cntr < max_calls; call_cntr++) { bool skip_tuple = false; char **values; /* allocate and zero space */ values = (char **) palloc0((1 + num_categories) * sizeof(char *)); /* * now loop through the sql results and assign each value in sequence * to the next category */ for (i = 0; i < num_categories; i++) { HeapTuple spi_tuple; char *rowid; /* see if we've gone too far already */ if (call_cntr >= max_calls) break; /* get the next sql result tuple */ spi_tuple = spi_tuptable->vals[call_cntr]; /* get the rowid from the current sql result tuple */ rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1); /* * If this is the first pass through the values for this rowid, * set the first column to rowid */ if (i == 0) { xpstrdup(values[0], rowid); /* * Check to see if the rowid is the same as that of the last * tuple sent -- if so, skip this tuple entirely */ if (!firstpass && xstreq(lastrowid, rowid)) { xpfree(rowid); skip_tuple = true; break; } } /* * If rowid hasn't changed on us, continue building the output * tuple. */ if (xstreq(rowid, values[0])) { /* * Get the next category item value, which is always attribute * number three. * * Be careful to assign the value to the array index based on * which category we are presently processing. */ values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3); /* * increment the counter since we consume a row for each * category, but not for last pass because the outer loop will * do that for us */ if (i < (num_categories - 1)) call_cntr++; xpfree(rowid); } else { /* * We'll fill in NULLs for the missing values, but we need to * decrement the counter since this sql result row doesn't * belong to the current output tuple. */ call_cntr--; xpfree(rowid); break; } } if (!skip_tuple) { HeapTuple tuple; /* build the tuple and store it */ tuple = BuildTupleFromCStrings(attinmeta, values); tuplestore_puttuple(tupstore, tuple); heap_freetuple(tuple); } /* Remember current rowid */ xpfree(lastrowid); xpstrdup(lastrowid, values[0]); firstpass = false; /* Clean up */ for (i = 0; i < num_categories + 1; i++) if (values[i] != NULL) pfree(values[i]); pfree(values); } /* let the caller know we're sending back a tuplestore */ rsinfo->returnMode = SFRM_Materialize; rsinfo->setResult = tupstore; rsinfo->setDesc = tupdesc; /* release SPI related resources (and return to caller's context) */ SPI_finish(); return (Datum) 0; }
/* 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); }
/* * refresh_by_match_merge * * Refresh a materialized view with transactional semantics, while allowing * concurrent reads. * * This is called after a new version of the data has been created in a * temporary table. It performs a full outer join against the old version of * the data, producing "diff" results. This join cannot work if there are any * duplicated rows in either the old or new versions, in the sense that every * column would compare as equal between the two rows. It does work correctly * in the face of rows which have at least one NULL value, with all non-NULL * columns equal. The behavior of NULLs on equality tests and on UNIQUE * indexes turns out to be quite convenient here; the tests we need to make * are consistent with default behavior. If there is at least one UNIQUE * index on the materialized view, we have exactly the guarantee we need. * * The temporary table used to hold the diff results contains just the TID of * the old record (if matched) and the ROW from the new table as a single * column of complex record type (if matched). * * Once we have the diff table, we perform set-based DELETE and INSERT * operations against the materialized view, and discard both temporary * tables. * * Everything from the generation of the new data to applying the differences * takes place under cover of an ExclusiveLock, since it seems as though we * would want to prohibit not only concurrent REFRESH operations, but also * incremental maintenance. It also doesn't seem reasonable or safe to allow * SELECT FOR UPDATE or SELECT FOR SHARE on rows being updated or deleted by * this command. */ static void refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, int save_sec_context) { StringInfoData querybuf; Relation matviewRel; Relation tempRel; char *matviewname; char *tempname; char *diffname; TupleDesc tupdesc; bool foundUniqueIndex; List *indexoidlist; ListCell *indexoidscan; int16 relnatts; bool *usedForQual; initStringInfo(&querybuf); matviewRel = heap_open(matviewOid, NoLock); matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)), RelationGetRelationName(matviewRel)); tempRel = heap_open(tempOid, NoLock); tempname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(tempRel)), RelationGetRelationName(tempRel)); diffname = make_temptable_name_n(tempname, 2); relnatts = matviewRel->rd_rel->relnatts; usedForQual = (bool *) palloc0(sizeof(bool) * relnatts); /* Open SPI context. */ if (SPI_connect() != SPI_OK_CONNECT) elog(ERROR, "SPI_connect failed"); /* Analyze the temp table with the new contents. */ appendStringInfo(&querybuf, "ANALYZE %s", tempname); if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY) elog(ERROR, "SPI_exec failed: %s", querybuf.data); /* * We need to ensure that there are not duplicate rows without NULLs in * the new data set before we can count on the "diff" results. Check for * that in a way that allows showing the first duplicated row found. Even * after we pass this test, a unique index on the materialized view may * find a duplicate key problem. */ resetStringInfo(&querybuf); appendStringInfo(&querybuf, "SELECT newdata FROM %s newdata " "WHERE newdata IS NOT NULL AND EXISTS " "(SELECT * FROM %s newdata2 WHERE newdata2 IS NOT NULL " "AND newdata2 OPERATOR(pg_catalog.*=) newdata " "AND newdata2.ctid OPERATOR(pg_catalog.<>) " "newdata.ctid) LIMIT 1", tempname, tempname); if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT) elog(ERROR, "SPI_exec failed: %s", querybuf.data); if (SPI_processed > 0) { /* * Note that this ereport() is returning data to the user. Generally, * we would want to make sure that the user has been granted access to * this data. However, REFRESH MAT VIEW is only able to be run by the * owner of the mat view (or a superuser) and therefore there is no * need to check for access to data in the mat view. */ ereport(ERROR, (errcode(ERRCODE_CARDINALITY_VIOLATION), errmsg("new data for \"%s\" contains duplicate rows without any null columns", RelationGetRelationName(matviewRel)), errdetail("Row: %s", SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)))); } SetUserIdAndSecContext(relowner, save_sec_context | SECURITY_LOCAL_USERID_CHANGE); /* Start building the query for creating the diff table. */ resetStringInfo(&querybuf); appendStringInfo(&querybuf, "CREATE TEMP TABLE %s AS " "SELECT mv.ctid AS tid, newdata " "FROM %s mv FULL JOIN %s newdata ON (", diffname, matviewname, tempname); /* * Get the list of index OIDs for the table from the relcache, and look up * each one in the pg_index syscache. We will test for equality on all * columns present in all unique indexes which only reference columns and * include all rows. */ tupdesc = matviewRel->rd_att; foundUniqueIndex = false; indexoidlist = RelationGetIndexList(matviewRel); foreach(indexoidscan, indexoidlist) { Oid indexoid = lfirst_oid(indexoidscan); Relation indexRel; Form_pg_index indexStruct; indexRel = index_open(indexoid, RowExclusiveLock); indexStruct = indexRel->rd_index; /* * We're only interested if it is unique, valid, contains no * expressions, and is not partial. */ if (indexStruct->indisunique && IndexIsValid(indexStruct) && RelationGetIndexExpressions(indexRel) == NIL && RelationGetIndexPredicate(indexRel) == NIL) { int numatts = indexStruct->indnatts; int i; /* Add quals for all columns from this index. */ for (i = 0; i < numatts; i++) { int attnum = indexStruct->indkey.values[i]; Oid type; Oid op; const char *colname; /* * Only include the column once regardless of how many times * it shows up in how many indexes. */ if (usedForQual[attnum - 1]) continue; usedForQual[attnum - 1] = true; /* * Actually add the qual, ANDed with any others. */ if (foundUniqueIndex) appendStringInfoString(&querybuf, " AND "); colname = quote_identifier(NameStr((tupdesc->attrs[attnum - 1])->attname)); appendStringInfo(&querybuf, "newdata.%s ", colname); type = attnumTypeId(matviewRel, attnum); op = lookup_type_cache(type, TYPECACHE_EQ_OPR)->eq_opr; mv_GenerateOper(&querybuf, op); appendStringInfo(&querybuf, " mv.%s", colname); foundUniqueIndex = true; } } /* Keep the locks, since we're about to run DML which needs them. */ index_close(indexRel, NoLock); }
/* table_log_restore_table() restore a complete table based on the logging table parameter: - original table name - name of primary key in original table - logging table - name of primary key in logging table - restore table name - timestamp for restoring data - primary key to restore (only this key will be restored) (optional) - restore mode 0: restore from blank table (default) needs a complete logging table 1: restore from actual table backwards - dont create table temporarly 0: create restore table temporarly (default) 1: create restore table not temporarly return: not yet defined */ Datum table_log_restore_table(PG_FUNCTION_ARGS) { /* the original table name */ char *table_orig; /* the primary key in the original table */ char *table_orig_pkey; /* number columns in original table */ int table_orig_columns; /* the log table name */ char *table_log; /* the primary key in the log table (usually trigger_id) */ /* cannot be the same then then the pkey in the original table */ char *table_log_pkey; /* number columns in log table */ int table_log_columns; /* the restore table name */ char *table_restore; /* the timestamp in past */ Datum timestamp = PG_GETARG_DATUM(5); /* the single pkey, can be null (then all keys will be restored) */ char *search_pkey = ""; /* the restore method - 0: restore from blank table (default) needs a complete log table! - 1: restore from actual table backwards */ int method = 0; /* dont create restore table temporarly - 0: create restore table temporarly (default) - 1: dont create restore table temporarly */ int not_temporarly = 0; int ret, results, i, number_columns; char query[250 + NAMEDATALEN]; /* for getting table infos (250 chars (+ one times the length of all names) should be enough) */ int need_search_pkey = 0; /* does we have a single key to restore? */ char *tmp, *timestamp_string, *old_pkey_string = ""; char *trigger_mode, *trigger_tuple, *trigger_changed; SPITupleTable *spi_tuptable = NULL; /* for saving query results */ VarChar *return_name; /* memory for dynamic query */ int d_query_size = 250; /* start with 250 bytes */ char *d_query; char *d_query_start; /* memory for column names */ int col_query_size = 0; char *col_query; char *col_query_start; int col_pkey = 0; /* * Some checks first... */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "start table_log_restore_table()"); #endif /* TABLE_LOG_DEBUG */ /* does we have all arguments? */ if (PG_ARGISNULL(0)) { elog(ERROR, "table_log_restore_table: missing original table name"); } if (PG_ARGISNULL(1)) { elog(ERROR, "table_log_restore_table: missing primary key name for original table"); } if (PG_ARGISNULL(2)) { elog(ERROR, "table_log_restore_table: missing log table name"); } if (PG_ARGISNULL(3)) { elog(ERROR, "table_log_restore_table: missing primary key name for log table"); } if (PG_ARGISNULL(4)) { elog(ERROR, "table_log_restore_table: missing copy table name"); } if (PG_ARGISNULL(5)) { elog(ERROR, "table_log_restore_table: missing timestamp"); } /* first check number arguments to avoid an segfault */ if (PG_NARGS() >= 7) { /* if argument is given, check if not null */ if (!PG_ARGISNULL(6)) { /* yes, fetch it */ search_pkey = __table_log_varcharout((VarChar *)PG_GETARG_VARCHAR_P(6)); /* and check, if we have an argument */ if (strlen(search_pkey) > 0) { need_search_pkey = 1; #ifdef TABLE_LOG_DEBUG elog(NOTICE, "table_log_restore_table: will restore a single key"); #endif /* TABLE_LOG_DEBUG */ } } } /* same procedere here */ if (PG_NARGS() >= 8) { if (!PG_ARGISNULL(7)) { method = PG_GETARG_INT32(7); if (method > 0) { method = 1; } else { method = 0; } } } #ifdef TABLE_LOG_DEBUG if (method == 1) { elog(NOTICE, "table_log_restore_table: will restore from actual state backwards"); } else { elog(NOTICE, "table_log_restore_table: will restore from begin forward"); } #endif /* TABLE_LOG_DEBUG */ if (PG_NARGS() >= 9) { if (!PG_ARGISNULL(8)) { not_temporarly = PG_GETARG_INT32(8); if (not_temporarly > 0) { not_temporarly = 1; } else { not_temporarly = 0; } } } #ifdef TABLE_LOG_DEBUG if (not_temporarly == 1) { elog(NOTICE, "table_log_restore_table: dont create restore table temporarly"); } #endif /* TABLE_LOG_DEBUG */ /* get parameter */ table_orig = __table_log_varcharout((VarChar *)PG_GETARG_VARCHAR_P(0)); table_orig_pkey = __table_log_varcharout((VarChar *)PG_GETARG_VARCHAR_P(1)); table_log = __table_log_varcharout((VarChar *)PG_GETARG_VARCHAR_P(2)); table_log_pkey = __table_log_varcharout((VarChar *)PG_GETARG_VARCHAR_P(3)); table_restore = __table_log_varcharout((VarChar *)PG_GETARG_VARCHAR_P(4)); /* pkey of original table cannot be the same as of log table */ if (strcmp((const char *)table_orig_pkey, (const char *)table_log_pkey) == 0) { elog(ERROR, "pkey of logging table cannot be the pkey of the original table: %s <-> %s", table_orig_pkey, table_log_pkey); } /* Connect to SPI manager */ ret = SPI_connect(); if (ret != SPI_OK_CONNECT) { elog(ERROR, "table_log_restore_table: SPI_connect returned %d", ret); } /* check original table */ snprintf(query, 249, "SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = %s AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum", do_quote_literal(table_orig)); #ifdef TABLE_LOG_DEBUG_QUERY elog(NOTICE, "query: %s", query); #endif /* TABLE_LOG_DEBUG_QUERY */ ret = SPI_exec(query, 0); if (ret != SPI_OK_SELECT) { elog(ERROR, "could not check relation: %s", table_orig); } if (SPI_processed > 0) { table_orig_columns = SPI_processed; } else { elog(ERROR, "could not check relation: %s", table_orig); } /* check pkey in original table */ snprintf(query, 249, "SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname=%s AND c.relkind='r' AND a.attname=%s AND a.attnum > 0 AND a.attrelid = c.oid", do_quote_literal(table_orig), do_quote_literal(table_orig_pkey)); #ifdef TABLE_LOG_DEBUG_QUERY elog(NOTICE, "query: %s", query); #endif /* TABLE_LOG_DEBUG_QUERY */ ret = SPI_exec(query, 0); if (ret != SPI_OK_SELECT) { elog(ERROR, "could not check relation: %s", table_orig); } if (SPI_processed == 0) { elog(ERROR, "could not check relation: %s", table_orig); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "original table: OK (%i columns)", table_orig_columns); #endif /* TABLE_LOG_DEBUG */ /* check log table */ snprintf(query, 249, "SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = %s AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum", do_quote_literal(table_log)); #ifdef TABLE_LOG_DEBUG_QUERY elog(NOTICE, "query: %s", query); #endif /* TABLE_LOG_DEBUG_QUERY */ ret = SPI_exec(query, 0); if (ret != SPI_OK_SELECT) { elog(ERROR, "could not check relation [1]: %s", table_log); } if (SPI_processed > 0) { table_log_columns = SPI_processed; } else { elog(ERROR, "could not check relation [2]: %s", table_log); } /* check pkey in log table */ snprintf(query, 249, "SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname=%s AND c.relkind='r' AND a.attname=%s AND a.attnum > 0 AND a.attrelid = c.oid", do_quote_literal(table_log), do_quote_literal(table_log_pkey)); #ifdef TABLE_LOG_DEBUG_QUERY elog(NOTICE, "query: %s", query); #endif /* TABLE_LOG_DEBUG_QUERY */ ret = SPI_exec(query, 0); if (ret != SPI_OK_SELECT) { elog(ERROR, "could not check relation [3]: %s", table_log); } if (SPI_processed == 0) { elog(ERROR, "could not check relation [4]: %s", table_log); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "log table: OK (%i columns)", table_log_columns); #endif /* TABLE_LOG_DEBUG */ /* check restore table */ snprintf(query, 249, "SELECT pg_attribute.attname AS a FROM pg_class, pg_attribute WHERE pg_class.relname=%s AND pg_attribute.attnum > 0 AND pg_attribute.attrelid=pg_class.oid", do_quote_literal(table_restore)); #ifdef TABLE_LOG_DEBUG_QUERY elog(NOTICE, "query: %s", query); #endif /* TABLE_LOG_DEBUG_QUERY */ ret = SPI_exec(query, 0); if (ret != SPI_OK_SELECT) { elog(ERROR, "could not check relation: %s", table_restore); } if (SPI_processed > 0) { elog(ERROR, "restore table already exists: %s", table_restore); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "restore table: OK (doesnt exists)"); #endif /* TABLE_LOG_DEBUG */ /* now get all columns from original table */ snprintf(query, 249, "SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnum FROM pg_class c, pg_attribute a WHERE c.relname = %s AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum", do_quote_literal(table_orig)); #ifdef TABLE_LOG_DEBUG_QUERY elog(NOTICE, "query: %s", query); #endif /* TABLE_LOG_DEBUG_QUERY */ ret = SPI_exec(query, 0); if (ret != SPI_OK_SELECT) { elog(ERROR, "could not get columns from relation: %s", table_orig); } if (SPI_processed == 0) { elog(ERROR, "could not check relation: %s", table_orig); } results = SPI_processed; /* store number columns for later */ number_columns = SPI_processed; #ifdef TABLE_LOG_DEBUG elog(NOTICE, "number columns: %i", results); #endif /* TABLE_LOG_DEBUG */ for (i = 0; i < results; i++) { /* the column name */ tmp = SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1); col_query_size += strlen(do_quote_ident(tmp)) + 2; /* now check, if this is the pkey */ if (strcmp((const char *)tmp, (const char *)table_orig_pkey) == 0) { /* remember the (real) number */ col_pkey = i + 1; } } /* check if we have found the pkey */ if (col_pkey == 0) { elog(ERROR, "cannot find pkey (%s) in table %s", table_orig_pkey, table_orig); } /* allocate memory for string */ col_query_size += 10; col_query_start = (char *) palloc((col_query_size + 1) * sizeof(char)); col_query = col_query_start; for (i = 0; i < results; i++) { if (i > 0) { sprintf(col_query, ", "); col_query = col_query_start + strlen(col_query_start); } sprintf(col_query, "%s", do_quote_ident(SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1))); col_query = col_query_start + strlen(col_query_start); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "string for columns: %s", col_query_start); #endif /* TABLE_LOG_DEBUG */ /* create restore table */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "create restore table: %s", table_restore); #endif /* TABLE_LOG_DEBUG */ snprintf(query, 249, "SELECT * INTO "); /* per default create a temporary table */ if (not_temporarly == 0) { strcat(query, "TEMPORARY "); } strcat(query, "TABLE "); strncat(query, table_restore, 249); /* from which table? */ strncat(query, " FROM ", 249); strncat(query, table_orig, 249); if (need_search_pkey == 1) { /* only extract a specific key */ strncat(query, " WHERE ", 249); strncat(query, do_quote_ident(table_orig_pkey), 249); strncat(query, "=", 249); strncat(query, do_quote_literal(search_pkey), 249); } if (method == 0) { /* restore from begin (blank table) */ strncat(query, " LIMIT 0", 249); } #ifdef TABLE_LOG_DEBUG_QUERY elog(NOTICE, "query: %s", query); #endif /* TABLE_LOG_DEBUG_QUERY */ ret = SPI_exec(query, 0); if (ret != SPI_OK_SELINTO) { elog(ERROR, "could not check relation: %s", table_restore); } if (method == 1) { #ifdef TABLE_LOG_DEBUG elog(NOTICE, "%i rows copied", SPI_processed); #endif /* TABLE_LOG_DEBUG */ } /* get timestamp as string */ timestamp_string = DatumGetCString(DirectFunctionCall1(timestamptz_out, timestamp)); #ifdef TABLE_LOG_DEBUG if (method == 0) { elog(NOTICE, "need logs from start to timestamp: %s", timestamp_string); } else { elog(NOTICE, "need logs from end to timestamp: %s", timestamp_string); } #endif /* TABLE_LOG_DEBUG */ /* now build query for getting logs */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "build query for getting logs"); #endif /* TABLE_LOG_DEBUG */ d_query_size += d_query_size + strlen(col_query_start); if (need_search_pkey == 1) { /* add size of pkey and size of value */ d_query_size += strlen(do_quote_ident(table_orig_pkey)) * 2 + strlen(do_quote_literal(search_pkey)) + 3; } /* allocate memory for string */ d_query_size += 10; d_query_start = (char *) palloc((d_query_size + 1) * sizeof(char)); d_query = d_query_start; snprintf(d_query, d_query_size, "SELECT %s, trigger_mode, trigger_tuple, trigger_changed FROM %s WHERE ", col_query_start, do_quote_ident(table_log)); d_query = d_query_start + strlen(d_query_start); if (method == 0) { /* from start to timestamp */ snprintf(d_query, d_query_size, "trigger_changed <= %s ", do_quote_literal(timestamp_string)); } else { /* from now() backwards to timestamp */ snprintf(d_query, d_query_size, "trigger_changed >= %s ", do_quote_literal(timestamp_string)); } d_query = d_query_start + strlen(d_query_start); if (need_search_pkey == 1) { snprintf(d_query, d_query_size, "AND %s = %s ", do_quote_ident(table_orig_pkey), do_quote_literal(search_pkey)); d_query = d_query_start + strlen(d_query_start); } if (method == 0) { snprintf(d_query, d_query_size, "ORDER BY %s ASC", do_quote_ident(table_log_pkey)); } else { snprintf(d_query, d_query_size, "ORDER BY %s DESC", do_quote_ident(table_log_pkey)); } d_query = d_query_start + strlen(d_query_start); #ifdef TABLE_LOG_DEBUG_QUERY elog(NOTICE, "query: %s", d_query_start); #endif /* TABLE_LOG_DEBUG_QUERY */ ret = SPI_exec(d_query_start, 0); if (ret != SPI_OK_SELECT) { elog(ERROR, "could not get log data from table: %s", table_log); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "number log entries to restore: %i", SPI_processed); #endif /* TABLE_LOG_DEBUG */ results = SPI_processed; /* save results */ spi_tuptable = SPI_tuptable; /* go through all results */ for (i = 0; i < results; i++) { /* get tuple data */ trigger_mode = SPI_getvalue(spi_tuptable->vals[i], spi_tuptable->tupdesc, number_columns + 1); trigger_tuple = SPI_getvalue(spi_tuptable->vals[i], spi_tuptable->tupdesc, number_columns + 2); trigger_changed = SPI_getvalue(spi_tuptable->vals[i], spi_tuptable->tupdesc, number_columns + 3); /* check for update tuples we doesnt need */ if (strcmp((const char *)trigger_mode, (const char *)"UPDATE") == 0) { if (method == 0 && strcmp((const char *)trigger_tuple, (const char *)"old") == 0) { /* we need the old value of the pkey for the update */ old_pkey_string = SPI_getvalue(spi_tuptable->vals[i], spi_tuptable->tupdesc, col_pkey); #ifdef TABLE_LOG_DEBUG elog(NOTICE, "tuple old pkey: %s", old_pkey_string); #endif /* TABLE_LOG_DEBUG */ /* then skip this tuple */ continue; } if (method == 1 && strcmp((const char *)trigger_tuple, (const char *)"new") == 0) { /* we need the old value of the pkey for the update */ old_pkey_string = SPI_getvalue(spi_tuptable->vals[i], spi_tuptable->tupdesc, col_pkey); #ifdef TABLE_LOG_DEBUG elog(NOTICE, "tuple: old pkey: %s", old_pkey_string); #endif /* TABLE_LOG_DEBUG */ /* then skip this tuple */ continue; } } if (method == 0) { /* roll forward */ #ifdef TABLE_LOG_DEBUG elog(NOTICE, "tuple: %s %s %s", trigger_mode, trigger_tuple, trigger_changed); #endif /* TABLE_LOG_DEBUG */ if (strcmp((const char *)trigger_mode, (const char *)"INSERT") == 0) { __table_log_restore_table_insert(spi_tuptable, table_restore, table_orig_pkey, col_query_start, col_pkey, number_columns, i); } else if (strcmp((const char *)trigger_mode, (const char *)"UPDATE") == 0) { __table_log_restore_table_update(spi_tuptable, table_restore, table_orig_pkey, col_query_start, col_pkey, number_columns, i, old_pkey_string); } else if (strcmp((const char *)trigger_mode, (const char *)"DELETE") == 0) { __table_log_restore_table_delete(spi_tuptable, table_restore, table_orig_pkey, col_query_start, col_pkey, number_columns, i); } else { elog(ERROR, "unknown trigger_mode: %s", trigger_mode); } } else { /* roll back */ char rb_mode[10]; /* reverse the method */ if (strcmp((const char *)trigger_mode, (const char *)"INSERT") == 0) { sprintf(rb_mode, "DELETE"); } else if (strcmp((const char *)trigger_mode, (const char *)"UPDATE") == 0) { sprintf(rb_mode, "UPDATE"); } else if (strcmp((const char *)trigger_mode, (const char *)"DELETE") == 0) { sprintf(rb_mode, "INSERT"); } else { elog(ERROR, "unknown trigger_mode: %s", trigger_mode); } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "tuple: %s %s %s", rb_mode, trigger_tuple, trigger_changed); #endif /* TABLE_LOG_DEBUG */ if (strcmp((const char *)trigger_mode, (const char *)"INSERT") == 0) { __table_log_restore_table_delete(spi_tuptable, table_restore, table_orig_pkey, col_query_start, col_pkey, number_columns, i); } else if (strcmp((const char *)trigger_mode, (const char *)"UPDATE") == 0) { __table_log_restore_table_update(spi_tuptable, table_restore, table_orig_pkey, col_query_start, col_pkey, number_columns, i, old_pkey_string); } else if (strcmp((const char *)trigger_mode, (const char *)"DELETE") == 0) { __table_log_restore_table_insert(spi_tuptable, table_restore, table_orig_pkey, col_query_start, col_pkey, number_columns, i); } } } #ifdef TABLE_LOG_DEBUG elog(NOTICE, "table_log_restore_table() done, results in: %s", table_restore); #endif /* TABLE_LOG_DEBUG */ /* convert string to VarChar for result */ return_name = DatumGetVarCharP(DirectFunctionCall2(varcharin, CStringGetDatum(table_restore), Int32GetDatum(strlen(table_restore) + VARHDRSZ))); /* close SPI connection */ SPI_finish(); /* and return the name of the restore table */ PG_RETURN_VARCHAR_P(return_name); }
static void test_spi_exec_utility(int *passed, int *total) { int rc; volatile int caseno = 0; /* Initialize */ rc = SPI_connect(); if (rc != SPI_OK_CONNECT) elog(ERROR, "could not connect SPI: %s", SPI_result_code_string(rc)); /* * *-*-1 * - query error */ caseno++; BeginInternalSubTransaction("test"); PG_TRY(); { spi_exec_utility("RESET dummy_parameter"); elog(WARNING, "*-*-%d failed", caseno); ReleaseCurrentSubTransaction(); } PG_CATCH(); { elog(WARNING, "%s-%d ok", __FUNCTION__, caseno); (*passed)++; RollbackAndReleaseCurrentSubTransaction(); FlushErrorState(); SPI_restore_connection(); } PG_END_TRY(); /* * *-*-2 * - query success */ caseno++; BeginInternalSubTransaction("test"); PG_TRY(); { spi_exec_utility("RESET client_min_messages"); elog(WARNING, "%s-%d ok", __FUNCTION__, caseno); (*passed)++; ReleaseCurrentSubTransaction(); } PG_CATCH(); { elog(WARNING, "*-*-%d failed", caseno); RollbackAndReleaseCurrentSubTransaction(); SPI_restore_connection(); } PG_END_TRY(); /* report # of tests */ (*total) += caseno; /* Cleanup */ rc = SPI_finish(); if (rc != SPI_OK_FINISH && rc != SPI_ERROR_UNCONNECTED) elog(ERROR, "could not finish SPI: %s", SPI_result_code_string(rc)); }
static void test_spi_exec_query(int *passed, int *total) { int rc; volatile int caseno = 0; SPIPlanPtr ptr = NULL; SPIPlanPtr org_ptr; /* Initialize */ rc = SPI_connect(); if (rc != SPI_OK_CONNECT) elog(ERROR, "could not connect SPI: %s", SPI_result_code_string(rc)); /* * *-*-1 * - plan is not cached */ caseno++; BeginInternalSubTransaction("test"); PG_TRY(); { spi_exec_query("SELECT 1", 0, NULL, &ptr, NULL, NULL, SPI_OK_SELECT); if (ptr != NULL && SPI_processed == 1) { elog(WARNING, "%s-%d ok", __FUNCTION__, caseno); (*passed)++; } ReleaseCurrentSubTransaction(); } PG_CATCH(); { elog(WARNING, "*-*-%d failed", caseno); RollbackAndReleaseCurrentSubTransaction(); SPI_restore_connection(); } PG_END_TRY(); /* * *-*-2 * - plan is cached */ caseno++; BeginInternalSubTransaction("test"); PG_TRY(); { org_ptr = ptr; spi_exec_query(NULL, 0, NULL, &ptr, NULL, NULL, SPI_OK_SELECT); if (ptr == org_ptr && SPI_processed == 1) { elog(WARNING, "%s-%d ok", __FUNCTION__, caseno); (*passed)++; } ReleaseCurrentSubTransaction(); } PG_CATCH(); { elog(WARNING, "*-*-%d failed", caseno); RollbackAndReleaseCurrentSubTransaction(); FlushErrorState(); SPI_restore_connection(); } PG_END_TRY(); SPI_freeplan(ptr); ptr = NULL; /* * *-*-3 * - query error */ caseno++; BeginInternalSubTransaction("test"); PG_TRY(); { spi_exec_query("SELECT 1 / 0", 0, NULL, &ptr, NULL, NULL, SPI_OK_SELECT); elog(WARNING, "*-*-%d failed", caseno); ReleaseCurrentSubTransaction(); } PG_CATCH(); { elog(WARNING, "%s-%d ok", __FUNCTION__, caseno); (*passed)++; RollbackAndReleaseCurrentSubTransaction(); FlushErrorState(); SPI_restore_connection(); } PG_END_TRY(); SPI_freeplan(ptr); ptr = NULL; /* * *-*-4 * - query success */ caseno++; BeginInternalSubTransaction("test"); PG_TRY(); { spi_exec_query("SELECT 1", 0, NULL, &ptr, NULL, NULL, SPI_OK_SELECT); if (ptr != NULL && SPI_processed == 1) { elog(WARNING, "%s-%d ok", __FUNCTION__, caseno); (*passed)++; } ReleaseCurrentSubTransaction(); } PG_CATCH(); { elog(WARNING, "*-*-%d failed", caseno); PG_RE_THROW(); RollbackAndReleaseCurrentSubTransaction(); SPI_restore_connection(); } PG_END_TRY(); SPI_freeplan(ptr); ptr = NULL; /* report # of tests */ (*total) += caseno; /* Cleanup */ rc = SPI_finish(); if (rc != SPI_OK_FINISH && rc != SPI_ERROR_UNCONNECTED) elog(ERROR, "could not finish SPI: %s", SPI_result_code_string(rc)); }
Datum plpgsql_inline_handler(PG_FUNCTION_ARGS) { InlineCodeBlock *codeblock = (InlineCodeBlock *) DatumGetPointer(PG_GETARG_DATUM(0)); PLpgSQL_function *func; FunctionCallInfoData fake_fcinfo; FmgrInfo flinfo; EState *simple_eval_estate; Datum retval; int rc; Assert(IsA(codeblock, InlineCodeBlock)); /* * Connect to SPI manager */ if ((rc = SPI_connect()) != SPI_OK_CONNECT) elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc)); /* Compile the anonymous code block */ func = plpgsql_compile_inline(codeblock->source_text); /* Mark the function as busy, just pro forma */ func->use_count++; /* * Set up a fake fcinfo with just enough info to satisfy * plpgsql_exec_function(). In particular note that this sets things up * with no arguments passed. */ MemSet(&fake_fcinfo, 0, sizeof(fake_fcinfo)); MemSet(&flinfo, 0, sizeof(flinfo)); fake_fcinfo.flinfo = &flinfo; flinfo.fn_oid = InvalidOid; flinfo.fn_mcxt = CurrentMemoryContext; /* Create a private EState for simple-expression execution */ simple_eval_estate = CreateExecutorState(); /* And run the function */ PG_TRY(); { retval = plpgsql_exec_function(func, &fake_fcinfo, simple_eval_estate); } PG_CATCH(); { /* * We need to clean up what would otherwise be long-lived resources * accumulated by the failed DO block, principally cached plans for * statements (which can be flushed with plpgsql_free_function_memory) * and execution trees for simple expressions, which are in the * private EState. * * Before releasing the private EState, we must clean up any * simple_econtext_stack entries pointing into it, which we can do by * invoking the subxact callback. (It will be called again later if * some outer control level does a subtransaction abort, but no harm * is done.) We cheat a bit knowing that plpgsql_subxact_cb does not * pay attention to its parentSubid argument. */ plpgsql_subxact_cb(SUBXACT_EVENT_ABORT_SUB, GetCurrentSubTransactionId(), 0, NULL); /* Clean up the private EState */ FreeExecutorState(simple_eval_estate); /* Function should now have no remaining use-counts ... */ func->use_count--; Assert(func->use_count == 0); /* ... so we can free subsidiary storage */ plpgsql_free_function_memory(func); /* And propagate the error */ PG_RE_THROW(); } PG_END_TRY(); /* Clean up the private EState */ FreeExecutorState(simple_eval_estate); /* Function should now have no remaining use-counts ... */ func->use_count--; Assert(func->use_count == 0); /* ... so we can free subsidiary storage */ plpgsql_free_function_memory(func); /* * Disconnect from SPI manager */ if ((rc = SPI_finish()) != SPI_OK_FINISH) elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc)); return retval; }
/* * load up the categories hash table */ static HTAB * load_categories_hash(char *cats_sql, MemoryContext per_query_ctx) { HTAB *crosstab_hash; HASHCTL ctl; int ret; int proc; MemoryContext SPIcontext; /* initialize the category hash table */ MemSet(&ctl, 0, sizeof(ctl)); ctl.keysize = MAX_CATNAME_LEN; ctl.entrysize = sizeof(crosstab_HashEnt); ctl.hcxt = per_query_ctx; /* * use INIT_CATS, defined above as a guess of how many hash table entries * to create, initially */ crosstab_hash = hash_create("crosstab hash", INIT_CATS, &ctl, HASH_ELEM | HASH_CONTEXT); /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) /* internal error */ elog(ERROR, "load_categories_hash: SPI_connect returned %d", ret); /* Retrieve the category name rows */ ret = SPI_execute(cats_sql, true, 0); proc = SPI_processed; /* Check for qualifying tuples */ if ((ret == SPI_OK_SELECT) && (proc > 0)) { SPITupleTable *spi_tuptable = SPI_tuptable; TupleDesc spi_tupdesc = spi_tuptable->tupdesc; int i; /* * The provided categories SQL query must always return one column: * category - the label or identifier for each column */ if (spi_tupdesc->natts != 1) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("provided \"categories\" SQL must " \ "return 1 column of at least one row"))); for (i = 0; i < proc; i++) { crosstab_cat_desc *catdesc; char *catname; HeapTuple spi_tuple; /* get the next sql result tuple */ spi_tuple = spi_tuptable->vals[i]; /* get the category from the current sql result tuple */ catname = SPI_getvalue(spi_tuple, spi_tupdesc, 1); SPIcontext = MemoryContextSwitchTo(per_query_ctx); catdesc = (crosstab_cat_desc *) palloc(sizeof(crosstab_cat_desc)); catdesc->catname = catname; catdesc->attidx = i; /* Add the proc description block to the hashtable */ crosstab_HashTableInsert(crosstab_hash, catdesc); MemoryContextSwitchTo(SPIcontext); } } if (SPI_finish() != SPI_OK_FINISH) /* internal error */ elog(ERROR, "load_categories_hash: SPI_finish() failed"); return crosstab_hash; }
Datum plpgsql_validator(PG_FUNCTION_ARGS) { Oid funcoid = PG_GETARG_OID(0); HeapTuple tuple; Form_pg_proc proc; char functyptype; int numargs; Oid *argtypes; char **argnames; char *argmodes; bool is_dml_trigger = false; bool is_event_trigger = false; int i; if (!CheckFunctionValidatorAccess(fcinfo->flinfo->fn_oid, funcoid)) PG_RETURN_VOID(); /* Get the new function's pg_proc entry */ tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcoid)); if (!HeapTupleIsValid(tuple)) elog(ERROR, "cache lookup failed for function %u", funcoid); proc = (Form_pg_proc) GETSTRUCT(tuple); functyptype = get_typtype(proc->prorettype); /* Disallow pseudotype result */ /* except for TRIGGER, RECORD, VOID, or polymorphic */ if (functyptype == TYPTYPE_PSEUDO) { /* we assume OPAQUE with no arguments means a trigger */ if (proc->prorettype == TRIGGEROID || (proc->prorettype == OPAQUEOID && proc->pronargs == 0)) is_dml_trigger = true; else if (proc->prorettype == EVTTRIGGEROID) is_event_trigger = true; else if (proc->prorettype != RECORDOID && proc->prorettype != VOIDOID && !IsPolymorphicType(proc->prorettype)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("PL/pgSQL functions cannot return type %s", format_type_be(proc->prorettype)))); } /* Disallow pseudotypes in arguments (either IN or OUT) */ /* except for polymorphic */ numargs = get_func_arg_info(tuple, &argtypes, &argnames, &argmodes); for (i = 0; i < numargs; i++) { if (get_typtype(argtypes[i]) == TYPTYPE_PSEUDO) { if (!IsPolymorphicType(argtypes[i])) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("PL/pgSQL functions cannot accept type %s", format_type_be(argtypes[i])))); } } /* Postpone body checks if !check_function_bodies */ if (check_function_bodies) { FunctionCallInfoData fake_fcinfo; FmgrInfo flinfo; int rc; TriggerData trigdata; EventTriggerData etrigdata; /* * Connect to SPI manager (is this needed for compilation?) */ if ((rc = SPI_connect()) != SPI_OK_CONNECT) elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc)); /* * Set up a fake fcinfo with just enough info to satisfy * plpgsql_compile(). */ MemSet(&fake_fcinfo, 0, sizeof(fake_fcinfo)); MemSet(&flinfo, 0, sizeof(flinfo)); fake_fcinfo.flinfo = &flinfo; flinfo.fn_oid = funcoid; flinfo.fn_mcxt = CurrentMemoryContext; if (is_dml_trigger) { MemSet(&trigdata, 0, sizeof(trigdata)); trigdata.type = T_TriggerData; fake_fcinfo.context = (Node *) &trigdata; } else if (is_event_trigger) { MemSet(&etrigdata, 0, sizeof(etrigdata)); etrigdata.type = T_EventTriggerData; fake_fcinfo.context = (Node *) &etrigdata; } /* Test-compile the function */ plpgsql_compile(&fake_fcinfo, true); /* * Disconnect from SPI manager */ if ((rc = SPI_finish()) != SPI_OK_FINISH) elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc)); } ReleaseSysCache(tuple); PG_RETURN_VOID(); }
/* * create and populate the crosstab tuplestore using the provided source query */ static Tuplestorestate * get_crosstab_tuplestore(char *sql, HTAB *crosstab_hash, TupleDesc tupdesc, MemoryContext per_query_ctx, bool randomAccess) { Tuplestorestate *tupstore; int num_categories = hash_get_num_entries(crosstab_hash); AttInMetadata *attinmeta = TupleDescGetAttInMetadata(tupdesc); char **values; HeapTuple tuple; int ret; int proc; /* initialize our tuplestore (while still in query context!) */ tupstore = tuplestore_begin_heap(randomAccess, false, work_mem); /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) /* internal error */ elog(ERROR, "get_crosstab_tuplestore: SPI_connect returned %d", ret); /* Now retrieve the crosstab source rows */ ret = SPI_execute(sql, true, 0); proc = SPI_processed; /* Check for qualifying tuples */ if ((ret == SPI_OK_SELECT) && (proc > 0)) { SPITupleTable *spi_tuptable = SPI_tuptable; TupleDesc spi_tupdesc = spi_tuptable->tupdesc; int ncols = spi_tupdesc->natts; char *rowid; char *lastrowid = NULL; bool firstpass = true; int i, j; int result_ncols; if (num_categories == 0) { /* no qualifying category tuples */ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("provided \"categories\" SQL must " \ "return 1 column of at least one row"))); } /* * The provided SQL query must always return at least three columns: * * 1. rowname the label for each row - column 1 in the final result * 2. category the label for each value-column in the final result 3. * value the values used to populate the value-columns * * If there are more than three columns, the last two are taken as * "category" and "values". The first column is taken as "rowname". * Additional columns (2 thru N-2) are assumed the same for the same * "rowname", and are copied into the result tuple from the first time * we encounter a particular rowname. */ if (ncols < 3) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("invalid source data SQL statement"), errdetail("The provided SQL must return 3 " \ " columns; rowid, category, and values."))); result_ncols = (ncols - 2) + num_categories; /* Recheck to make sure we tuple descriptor still looks reasonable */ if (tupdesc->natts != result_ncols) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("invalid return type"), errdetail("Query-specified return " \ "tuple has %d columns but crosstab " \ "returns %d.", tupdesc->natts, result_ncols))); /* allocate space */ values = (char **) palloc(result_ncols * sizeof(char *)); /* and make sure it's clear */ memset(values, '\0', result_ncols * sizeof(char *)); for (i = 0; i < proc; i++) { HeapTuple spi_tuple; crosstab_cat_desc *catdesc; char *catname; /* get the next sql result tuple */ spi_tuple = spi_tuptable->vals[i]; /* get the rowid from the current sql result tuple */ rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1); /* * if we're on a new output row, grab the column values up to * column N-2 now */ if (firstpass || !xstreq(lastrowid, rowid)) { /* * a new row means we need to flush the old one first, unless * we're on the very first row */ if (!firstpass) { /* rowid changed, flush the previous output row */ tuple = BuildTupleFromCStrings(attinmeta, values); tuplestore_puttuple(tupstore, tuple); for (j = 0; j < result_ncols; j++) xpfree(values[j]); } values[0] = rowid; for (j = 1; j < ncols - 2; j++) values[j] = SPI_getvalue(spi_tuple, spi_tupdesc, j + 1); /* we're no longer on the first pass */ firstpass = false; } /* look up the category and fill in the appropriate column */ catname = SPI_getvalue(spi_tuple, spi_tupdesc, ncols - 1); if (catname != NULL) { crosstab_HashTableLookup(crosstab_hash, catname, catdesc); if (catdesc) values[catdesc->attidx + ncols - 2] = SPI_getvalue(spi_tuple, spi_tupdesc, ncols); } xpfree(lastrowid); xpstrdup(lastrowid, rowid); } /* flush the last output row */ tuple = BuildTupleFromCStrings(attinmeta, values); tuplestore_puttuple(tupstore, tuple); } if (SPI_finish() != SPI_OK_FINISH) /* internal error */ elog(ERROR, "get_crosstab_tuplestore: SPI_finish() failed"); tuplestore_donestoring(tupstore); return tupstore; }
/* * Arguments: * 0: queue_name text NOT NULL * 1: ev_id int8 if NULL take from SEQ * 2: ev_time timestamptz if NULL use now() * 3: ev_owner int4 * 4: ev_retry int4 * 5: ev_type text * 6: ev_data text * 7: ev_extra1 text * 8: ev_extra2 text * 9: ev_extra3 text * 10:ev_extra4 text */ Datum pgq_insert_event_raw(PG_FUNCTION_ARGS) { Datum values[11]; char nulls[11]; struct QueueState state; int64 ret_id; void *ins_plan; Datum ev_id, ev_time; int i, res; if (PG_NARGS() < 6) elog(ERROR, "Need at least 6 arguments"); if (PG_ARGISNULL(0)) elog(ERROR, "Queue name must not be NULL"); if (SPI_connect() < 0) elog(ERROR, "SPI_connect() failed"); init_cache(); load_queue_info(PG_GETARG_DATUM(0), &state); if (PG_ARGISNULL(1)) ev_id = state.next_event_id; else ev_id = PG_GETARG_DATUM(1); if (PG_ARGISNULL(2)) ev_time = DirectFunctionCall1(now, 0); else ev_time = PG_GETARG_DATUM(2); /* * Prepare arguments for INSERT */ values[0] = ev_id; nulls[0] = ' '; values[1] = ev_time; nulls[1] = ' '; for (i = 3; i < 11; i++) { int dst = i - 1; if (i >= PG_NARGS() || PG_ARGISNULL(i)) { values[dst] = (Datum)NULL; nulls[dst] = 'n'; } else { values[dst] = PG_GETARG_DATUM(i); nulls[dst] = ' '; } } /* * Perform INSERT into queue table. */ ins_plan = load_insert_plan(&state); res = SPI_execute_plan(ins_plan, values, nulls, false, 0); if (res != SPI_OK_INSERT) elog(ERROR, "Queue insert failed"); /* * ev_id cannot pass SPI_finish() */ ret_id = DatumGetInt64(ev_id); if (SPI_finish() < 0) elog(ERROR, "SPI_finish failed"); PG_RETURN_INT64(ret_id); }
void worker_spi_main(Datum main_arg) { int index = DatumGetInt32(main_arg); worktable *table; StringInfoData buf; char name[20]; table = palloc(sizeof(worktable)); sprintf(name, "schema%d", index); table->schema = pstrdup(name); table->name = pstrdup("counted"); /* Establish signal handlers before unblocking signals. */ pqsignal(SIGHUP, worker_spi_sighup); pqsignal(SIGTERM, worker_spi_sigterm); /* We're now ready to receive signals */ BackgroundWorkerUnblockSignals(); /* Connect to our database */ BackgroundWorkerInitializeConnection("postgres", NULL); elog(LOG, "%s initialized with %s.%s", MyBgworkerEntry->bgw_name, table->schema, table->name); initialize_worker_spi(table); /* * Quote identifiers passed to us. Note that this must be done after * initialize_worker_spi, because that routine assumes the names are not * quoted. * * Note some memory might be leaked here. */ table->schema = quote_identifier(table->schema); table->name = quote_identifier(table->name); initStringInfo(&buf); appendStringInfo(&buf, "WITH deleted AS (DELETE " "FROM %s.%s " "WHERE type = 'delta' RETURNING value), " "total AS (SELECT coalesce(sum(value), 0) as sum " "FROM deleted) " "UPDATE %s.%s " "SET value = %s.value + total.sum " "FROM total WHERE type = 'total' " "RETURNING %s.value", table->schema, table->name, table->schema, table->name, table->name, table->name); /* * Main loop: do this until the SIGTERM handler tells us to terminate */ while (!got_sigterm) { int ret; int rc; /* * Background workers mustn't call usleep() or any direct equivalent: * instead, they may wait on their process latch, which sleeps as * necessary, but is awakened if postmaster dies. That way the * background process goes away immediately in an emergency. */ rc = WaitLatch(MyLatch, WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, worker_spi_naptime * 1000L, PG_WAIT_EXTENSION); ResetLatch(MyLatch); /* emergency bailout if postmaster has died */ if (rc & WL_POSTMASTER_DEATH) proc_exit(1); /* * In case of a SIGHUP, just reload the configuration. */ if (got_sighup) { got_sighup = false; ProcessConfigFile(PGC_SIGHUP); } /* * Start a transaction on which we can run queries. Note that each * StartTransactionCommand() call should be preceded by a * SetCurrentStatementStartTimestamp() call, which sets both the time * for the statement we're about the run, and also the transaction * start time. Also, each other query sent to SPI should probably be * preceded by SetCurrentStatementStartTimestamp(), so that statement * start time is always up to date. * * The SPI_connect() call lets us run queries through the SPI manager, * and the PushActiveSnapshot() call creates an "active" snapshot * which is necessary for queries to have MVCC data to work on. * * The pgstat_report_activity() call makes our activity visible * through the pgstat views. */ SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); pgstat_report_activity(STATE_RUNNING, buf.data); /* We can now execute queries via SPI */ ret = SPI_execute(buf.data, false, 0); if (ret != SPI_OK_UPDATE_RETURNING) elog(FATAL, "cannot select from table %s.%s: error code %d", table->schema, table->name, ret); if (SPI_processed > 0) { bool isnull; int32 val; val = DatumGetInt32(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull)); if (!isnull) elog(LOG, "%s: count in %s.%s is now %d", MyBgworkerEntry->bgw_name, table->schema, table->name, val); } /* * And finish our transaction. */ SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); pgstat_report_stat(false); pgstat_report_activity(STATE_IDLE, NULL); } proc_exit(1); }
static struct brokerstate * local_amqp_get_bs(broker_id) { char sql[1024]; char host_copy[300] = ""; int tries = 0; struct brokerstate *bs = local_amqp_get_a_bs(broker_id); if(bs->conn) return bs; if(SPI_connect() == SPI_ERROR_CONNECT) return NULL; snprintf(sql, sizeof(sql), "SELECT host, port, vhost, username, password " " FROM amqp.broker " " WHERE broker_id = %d " " ORDER BY host DESC, port", broker_id); if(SPI_OK_SELECT == SPI_execute(sql, true, 100)) { tries = SPI_processed; retry: tries--; if(SPI_processed > 0) { struct timeval hb = { .tv_sec = 2UL, .tv_usec = 0UL }; amqp_rpc_reply_t *reply, s_reply; char *host, *vhost, *user, *pass; Datum port_datum; bool is_null; int port = 5672; bs->idx = (bs->idx + 1) % SPI_processed; host = SPI_getvalue(SPI_tuptable->vals[bs->idx], SPI_tuptable->tupdesc, 1); if(!host) host = "localhost"; port_datum = SPI_getbinval(SPI_tuptable->vals[bs->idx], SPI_tuptable->tupdesc, 2, &is_null); if(!is_null) port = DatumGetInt32(port_datum); vhost = SPI_getvalue(SPI_tuptable->vals[bs->idx], SPI_tuptable->tupdesc, 3); if(!vhost) vhost = "/"; user = SPI_getvalue(SPI_tuptable->vals[bs->idx], SPI_tuptable->tupdesc, 4); if(!user) user = "******"; pass = SPI_getvalue(SPI_tuptable->vals[bs->idx], SPI_tuptable->tupdesc, 5); if(!pass) pass = "******"; snprintf(host_copy, sizeof(host_copy), "%s:%d", host, port); bs->conn = amqp_new_connection(); if(!bs->conn) { SPI_finish(); return NULL; } bs->sockfd = amqp_open_socket(host, port, &hb); if(bs->sockfd < 0) { elog(WARNING, "amqp[%s] login socket/connect failed: %s", host_copy, strerror(-bs->sockfd)); goto busted; } amqp_set_sockfd(bs->conn, bs->sockfd); s_reply = amqp_login(bs->conn, vhost, 0, 131072, 0, AMQP_SASL_METHOD_PLAIN, user, pass); if(s_reply.reply_type != AMQP_RESPONSE_NORMAL) { elog(WARNING, "amqp[%s] login failed on broker %d", host_copy, broker_id); goto busted; } amqp_channel_open(bs->conn, 1); reply = amqp_get_rpc_reply(); if(reply->reply_type != AMQP_RESPONSE_NORMAL) { elog(WARNING, "amqp[%s] channel open failed on broker %d", host_copy, broker_id); goto busted; } amqp_channel_open(bs->conn, 2); reply = amqp_get_rpc_reply(); if(reply->reply_type != AMQP_RESPONSE_NORMAL) { elog(WARNING, "amqp[%s] channel open failed on broker %d", host_copy, broker_id); goto busted; } amqp_tx_select(bs->conn, 2, AMQP_EMPTY_TABLE); reply = amqp_get_rpc_reply(); if(reply->reply_type != AMQP_RESPONSE_NORMAL) { elog(WARNING, "amqp[%s] could not start tx mode on broker %d", host_copy, broker_id); goto busted; } } else { elog(WARNING, "amqp can't find broker %d", broker_id); } } else {
Datum tsquery_rewrite_query(PG_FUNCTION_ARGS) { TSQuery query = PG_GETARG_TSQUERY_COPY(0); text *in = PG_GETARG_TEXT_P(1); TSQuery rewrited = query; MemoryContext outercontext = CurrentMemoryContext; MemoryContext oldcontext; QTNode *tree; char *buf; SPIPlanPtr plan; Portal portal; bool isnull; int i; if (query->size == 0) { PG_FREE_IF_COPY(in, 1); PG_RETURN_POINTER(rewrited); } tree = QT2QTN(GETQUERY(query), GETOPERAND(query)); QTNTernary(tree); QTNSort(tree); buf = text_to_cstring(in); SPI_connect(); if ((plan = SPI_prepare(buf, 0, NULL)) == NULL) elog(ERROR, "SPI_prepare(\"%s\") failed", buf); if ((portal = SPI_cursor_open(NULL, plan, NULL, NULL, true)) == NULL) elog(ERROR, "SPI_cursor_open(\"%s\") failed", buf); SPI_cursor_fetch(portal, true, 100); if (SPI_tuptable == NULL || SPI_tuptable->tupdesc->natts != 2 || SPI_gettypeid(SPI_tuptable->tupdesc, 1) != TSQUERYOID || SPI_gettypeid(SPI_tuptable->tupdesc, 2) != TSQUERYOID) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ts_rewrite query must return two tsquery columns"))); while (SPI_processed > 0 && tree) { for (i = 0; i < SPI_processed && tree; i++) { Datum qdata = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1, &isnull); Datum sdata; if (isnull) continue; sdata = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 2, &isnull); if (!isnull) { TSQuery qtex = DatumGetTSQuery(qdata); TSQuery qtsubs = DatumGetTSQuery(sdata); QTNode *qex, *qsubs = NULL; if (qtex->size == 0) { if (qtex != (TSQuery) DatumGetPointer(qdata)) pfree(qtex); if (qtsubs != (TSQuery) DatumGetPointer(sdata)) pfree(qtsubs); continue; } qex = QT2QTN(GETQUERY(qtex), GETOPERAND(qtex)); QTNTernary(qex); QTNSort(qex); if (qtsubs->size) qsubs = QT2QTN(GETQUERY(qtsubs), GETOPERAND(qtsubs)); oldcontext = MemoryContextSwitchTo(outercontext); tree = findsubquery(tree, qex, qsubs, NULL); MemoryContextSwitchTo(oldcontext); QTNFree(qex); if (qtex != (TSQuery) DatumGetPointer(qdata)) pfree(qtex); QTNFree(qsubs); if (qtsubs != (TSQuery) DatumGetPointer(sdata)) pfree(qtsubs); if (tree) { /* ready the tree for another pass */ QTNClearFlags(tree, QTN_NOCHANGE); QTNSort(tree); } } } SPI_freetuptable(SPI_tuptable); SPI_cursor_fetch(portal, true, 100); } SPI_freetuptable(SPI_tuptable); SPI_cursor_close(portal); SPI_freeplan(plan); SPI_finish(); if (tree) { QTNBinary(tree); rewrited = QTN2QT(tree); QTNFree(tree); PG_FREE_IF_COPY(query, 0); } else { SET_VARSIZE(rewrited, HDRSIZETQ); rewrited->size = 0; } pfree(buf); PG_FREE_IF_COPY(in, 1); PG_RETURN_POINTER(rewrited); }
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; uint64 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: " UINT64_FORMAT "/%d tuples inserted/selected", when, *level, inserted, selected); SPI_finish(); (*level)--; if (*level == 0) *xid = InvalidTransactionId; return PointerGetDatum(tuple); }
/* * worker logic */ void wed_worker_main(Datum main_arg) { StringInfoData buf; /* Establish signal handlers before unblocking signals. */ pqsignal(SIGHUP, wed_worker_sighup); pqsignal(SIGTERM, wed_worker_sigterm); /* We're now ready to receive signals */ BackgroundWorkerUnblockSignals(); /* Connect to our database */ BackgroundWorkerInitializeConnection(wed_worker_db_name, NULL); elog(LOG, "%s initialized in: %s", MyBgworkerEntry->bgw_name, wed_worker_db_name); initStringInfo(&buf); appendStringInfo(&buf, "SELECT trcheck()"); /* * Main loop: do this until the SIGTERM handler tells us to terminate */ while (!got_sigterm) { int ret; int rc; /* * Background workers mustn't call usleep() or any direct equivalent: * instead, they may wait on their process latch, which sleeps as * necessary, but is awakened if postmaster dies. That way the * background process goes away immediately in an emergency. */ rc = WaitLatch(&MyProc->procLatch, WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, wed_worker_naptime * 1000L); ResetLatch(&MyProc->procLatch); /* emergency bailout if postmaster has died */ if (rc & WL_POSTMASTER_DEATH) proc_exit(1); /* * In case of a SIGHUP, just reload the configuration. */ if (got_sighup) { got_sighup = false; ProcessConfigFile(PGC_SIGHUP); } /* * Start a transaction on which we can run queries. Note that each * StartTransactionCommand() call should be preceded by a * SetCurrentStatementStartTimestamp() call, which sets both the time * for the statement we're about the run, and also the transaction * start time. Also, each other query sent to SPI should probably be * preceded by SetCurrentStatementStartTimestamp(), so that statement * start time is always up to date. * * The SPI_connect() call lets us run queries through the SPI manager, * and the PushActiveSnapshot() call creates an "active" snapshot * which is necessary for queries to have MVCC data to work on. * * The pgstat_report_activity() call makes our activity visible * through the pgstat views. */ SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); pgstat_report_activity(STATE_RUNNING, buf.data); /* We can now execute queries via SPI */ ret = SPI_execute(buf.data, false, 0); if (ret != SPI_OK_SELECT) elog(FATAL, "stored procedure trcheck() not found: error code %d", ret); elog(LOG, "%s : trcheck() done !", MyBgworkerEntry->bgw_name); /* * And finish our transaction. */ SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); pgstat_report_activity(STATE_IDLE, NULL); } proc_exit(1); }
/* * worker_merge_files_and_run_query creates a merge task table within the job's * schema, which should have already been created by the task tracker protocol. * It copies files in its task directory into this table. Then it runs final * query to create result table of the job. * * Note that here we followed a different approach to create a task table for merge * files than worker_merge_files_into_table(). In future we should unify these * two approaches. For this purpose creating a directory_fdw extension and using * it would make sense. Then we can merge files with a query or without query * through directory_fdw. */ Datum worker_merge_files_and_run_query(PG_FUNCTION_ARGS) { uint64 jobId = PG_GETARG_INT64(0); uint32 taskId = PG_GETARG_UINT32(1); text *createMergeTableQueryText = PG_GETARG_TEXT_P(2); text *createIntermediateTableQueryText = PG_GETARG_TEXT_P(3); const char *createMergeTableQuery = text_to_cstring(createMergeTableQueryText); const char *createIntermediateTableQuery = text_to_cstring(createIntermediateTableQueryText); StringInfo taskDirectoryName = TaskDirectoryName(jobId, taskId); StringInfo jobSchemaName = JobSchemaName(jobId); StringInfo intermediateTableName = TaskTableName(taskId); StringInfo mergeTableName = makeStringInfo(); StringInfo setSearchPathString = makeStringInfo(); bool schemaExists = false; int connected = 0; int setSearchPathResult = 0; int createMergeTableResult = 0; int createIntermediateTableResult = 0; int finished = 0; /* * If the schema for the job isn't already created by the task tracker * protocol, we fall to using the default 'public' schema. */ schemaExists = JobSchemaExists(jobSchemaName); if (!schemaExists) { resetStringInfo(jobSchemaName); appendStringInfoString(jobSchemaName, "public"); } appendStringInfo(setSearchPathString, SET_SEARCH_PATH_COMMAND, jobSchemaName->data); /* Add "public" to search path to access UDFs in public schema */ appendStringInfo(setSearchPathString, ",public"); connected = SPI_connect(); if (connected != SPI_OK_CONNECT) { ereport(ERROR, (errmsg("could not connect to SPI manager"))); } setSearchPathResult = SPI_exec(setSearchPathString->data, 0); if (setSearchPathResult < 0) { ereport(ERROR, (errmsg("execution was not successful \"%s\"", setSearchPathString->data))); } createMergeTableResult = SPI_exec(createMergeTableQuery, 0); if (createMergeTableResult < 0) { ereport(ERROR, (errmsg("execution was not successful \"%s\"", createMergeTableQuery))); } appendStringInfo(mergeTableName, "%s%s", intermediateTableName->data, MERGE_TABLE_SUFFIX); CopyTaskFilesFromDirectory(jobSchemaName, mergeTableName, taskDirectoryName); createIntermediateTableResult = SPI_exec(createIntermediateTableQuery, 0); if (createIntermediateTableResult < 0) { ereport(ERROR, (errmsg("execution was not successful \"%s\"", createIntermediateTableQuery))); } finished = SPI_finish(); if (finished != SPI_OK_FINISH) { ereport(ERROR, (errmsg("could not disconnect from SPI manager"))); } PG_RETURN_VOID(); }
Datum qhsrvaccount_monitor(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; TupleDesc tupdesc; HeapTuple rettuple; char opcode = 'X'; // U for update I for insert D for delete X for unknow /* make sure it's called as a trigger at all */ if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "qhsrvaccount_monitor: not called by trigger manager"); /* tuple to return to executor */ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)){ opcode = 'U'; rettuple = trigdata->tg_newtuple; }else{ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) { opcode = 'I'; }else if(TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)){ opcode = 'D'; } rettuple = trigdata->tg_trigtuple; } tupdesc = trigdata->tg_relation->rd_att; char notify[1024] = {0}; unsigned int notifylen = 0; unsigned int natts = trigdata->tg_relation->rd_att->natts; char* value ; unsigned int valuelen = 0; notify[0] = opcode; notify[1] = '^'; notifylen = 2; int i = 0; for(; i < natts; ++i){ value = SPI_getvalue(rettuple, tupdesc, i+1); if (value == NULL) { value = "$"; } valuelen = strlen(value); memcpy(notify+notifylen, value,valuelen); *(notify+notifylen+valuelen) = '^'; notifylen = notifylen+valuelen+1; } notify[--notifylen] = 0; elog(INFO, "%s", notify); int ret; char *tablename = " qhsrvaccount,"; int tablenamelen = strlen(tablename); if ((ret = SPI_connect()) < 0) elog(ERROR, "SPI connect return %d", ret); /* get number of rows in table */ char notifycmd[1024] = {0}; unsigned int notifycmdlen = sizeof("NOTIFY")-1; memcpy(notifycmd, "NOTIFY", notifycmdlen); notifycmd[notifycmdlen++] = ' '; memcpy(¬ifycmd[notifycmdlen], tablename, tablenamelen); notifycmdlen += tablenamelen; notifycmd[notifycmdlen++] = '\''; memcpy(¬ifycmd[notifycmdlen], notify, notifylen); notifycmdlen += notifylen; notifycmd[notifycmdlen++] = '\''; elog(INFO, "%s", notifycmd); ret = SPI_exec(notifycmd,0); if (ret < 0) elog(ERROR, " SPI_exec returned %d", ret); SPI_finish(); return PointerGetDatum(rettuple); }
Datum xpath_table(PG_FUNCTION_ARGS) { /* Function parameters */ char *pkeyfield = text_to_cstring(PG_GETARG_TEXT_PP(0)); char *xmlfield = text_to_cstring(PG_GETARG_TEXT_PP(1)); char *relname = text_to_cstring(PG_GETARG_TEXT_PP(2)); char *xpathset = text_to_cstring(PG_GETARG_TEXT_PP(3)); char *condition = text_to_cstring(PG_GETARG_TEXT_PP(4)); /* SPI (input tuple) support */ SPITupleTable *tuptable; HeapTuple spi_tuple; TupleDesc spi_tupdesc; /* Output tuple (tuplestore) support */ Tuplestorestate *tupstore = NULL; TupleDesc ret_tupdesc; HeapTuple ret_tuple; ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; AttInMetadata *attinmeta; MemoryContext per_query_ctx; MemoryContext oldcontext; char **values; xmlChar **xpaths; char *pos; const char *pathsep = "|"; int numpaths; int ret; int proc; int i; int j; int rownr; /* For issuing multiple rows from one original * document */ bool had_values; /* To determine end of nodeset results */ StringInfoData query_buf; /* We only have a valid tuple description in table function mode */ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("set-valued function called in context that cannot accept a set"))); if (rsinfo->expectedDesc == NULL) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("xpath_table must be called as a table function"))); /* * We want to materialise because it means that we don't have to carry * libxml2 parser state between invocations of this function */ if (!(rsinfo->allowedModes & SFRM_Materialize)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("xpath_table requires Materialize mode, but it is not " "allowed in this context"))); /* * The tuplestore must exist in a higher context than this function call * (per_query_ctx is used) */ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; oldcontext = MemoryContextSwitchTo(per_query_ctx); /* * Create the tuplestore - work_mem is the max in-memory size before a * file is created on disk to hold it. */ tupstore = tuplestore_begin_heap(rsinfo->allowedModes & SFRM_Materialize_Random, false, work_mem); MemoryContextSwitchTo(oldcontext); /* get the requested return tuple description */ ret_tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); /* must have at least one output column (for the pkey) */ if (ret_tupdesc->natts < 1) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("xpath_table must have at least one output column"))); /* * At the moment we assume that the returned attributes make sense for the * XPath specififed (i.e. we trust the caller). It's not fatal if they get * it wrong - the input function for the column type will raise an error * if the path result can't be converted into the correct binary * representation. */ attinmeta = TupleDescGetAttInMetadata(ret_tupdesc); /* Set return mode and allocate value space. */ rsinfo->returnMode = SFRM_Materialize; rsinfo->setDesc = ret_tupdesc; values = (char **) palloc(ret_tupdesc->natts * sizeof(char *)); xpaths = (xmlChar **) palloc(ret_tupdesc->natts * sizeof(xmlChar *)); /* * Split XPaths. xpathset is a writable CString. * * Note that we stop splitting once we've done all needed for tupdesc */ numpaths = 0; pos = xpathset; while (numpaths < (ret_tupdesc->natts - 1)) { xpaths[numpaths++] = (xmlChar *) pos; pos = strstr(pos, pathsep); if (pos != NULL) { *pos = '\0'; pos++; } else break; } /* Now build query */ initStringInfo(&query_buf); /* Build initial sql statement */ appendStringInfo(&query_buf, "SELECT %s, %s FROM %s WHERE %s", pkeyfield, xmlfield, relname, condition); if ((ret = SPI_connect()) < 0) elog(ERROR, "xpath_table: SPI_connect returned %d", ret); if ((ret = SPI_exec(query_buf.data, 0)) != SPI_OK_SELECT) elog(ERROR, "xpath_table: SPI execution failed for query %s", query_buf.data); proc = SPI_processed; /* elog(DEBUG1,"xpath_table: SPI returned %d rows",proc); */ tuptable = SPI_tuptable; spi_tupdesc = tuptable->tupdesc; /* Switch out of SPI context */ MemoryContextSwitchTo(oldcontext); /* * Check that SPI returned correct result. If you put a comma into one of * the function parameters, this will catch it when the SPI query returns * e.g. 3 columns. */ if (spi_tupdesc->natts != 2) { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("expression returning multiple columns is not valid in parameter list"), errdetail("Expected two columns in SPI result, got %d.", spi_tupdesc->natts))); } /* * Setup the parser. This should happen after we are done evaluating the * query, in case it calls functions that set up libxml differently. */ pgxml_parser_init(); /* For each row i.e. document returned from SPI */ for (i = 0; i < proc; i++) { char *pkey; char *xmldoc; xmlDocPtr doctree; xmlXPathContextPtr ctxt; xmlXPathObjectPtr res; xmlChar *resstr; xmlXPathCompExprPtr comppath; /* Extract the row data as C Strings */ spi_tuple = tuptable->vals[i]; pkey = SPI_getvalue(spi_tuple, spi_tupdesc, 1); xmldoc = SPI_getvalue(spi_tuple, spi_tupdesc, 2); /* * Clear the values array, so that not-well-formed documents return * NULL in all columns. Note that this also means that spare columns * will be NULL. */ for (j = 0; j < ret_tupdesc->natts; j++) values[j] = NULL; /* Insert primary key */ values[0] = pkey; /* Parse the document */ if (xmldoc) doctree = xmlParseMemory(xmldoc, strlen(xmldoc)); else /* treat NULL as not well-formed */ doctree = NULL; if (doctree == NULL) { /* not well-formed, so output all-NULL tuple */ ret_tuple = BuildTupleFromCStrings(attinmeta, values); tuplestore_puttuple(tupstore, ret_tuple); heap_freetuple(ret_tuple); } else { /* New loop here - we have to deal with nodeset results */ rownr = 0; do { /* Now evaluate the set of xpaths. */ had_values = false; for (j = 0; j < numpaths; j++) { ctxt = xmlXPathNewContext(doctree); ctxt->node = xmlDocGetRootElement(doctree); /* compile the path */ comppath = xmlXPathCompile(xpaths[j]); if (comppath == NULL) { xmlFreeDoc(doctree); xml_ereport(ERROR, ERRCODE_EXTERNAL_ROUTINE_EXCEPTION, "XPath Syntax Error"); } /* Now evaluate the path expression. */ res = xmlXPathCompiledEval(comppath, ctxt); xmlXPathFreeCompExpr(comppath); if (res != NULL) { switch (res->type) { case XPATH_NODESET: /* We see if this nodeset has enough nodes */ if (res->nodesetval != NULL && rownr < res->nodesetval->nodeNr) { resstr = xmlXPathCastNodeToString(res->nodesetval->nodeTab[rownr]); had_values = true; } else resstr = NULL; break; case XPATH_STRING: resstr = xmlStrdup(res->stringval); break; default: elog(NOTICE, "unsupported XQuery result: %d", res->type); resstr = xmlStrdup((const xmlChar *) "<unsupported/>"); } /* * Insert this into the appropriate column in the * result tuple. */ values[j + 1] = (char *) resstr; } xmlXPathFreeContext(ctxt); } /* Now add the tuple to the output, if there is one. */ if (had_values) { ret_tuple = BuildTupleFromCStrings(attinmeta, values); tuplestore_puttuple(tupstore, ret_tuple); heap_freetuple(ret_tuple); } rownr++; } while (had_values); } xmlFreeDoc(doctree); if (pkey) pfree(pkey); if (xmldoc) pfree(xmldoc); } tuplestore_donestoring(tupstore); SPI_finish(); rsinfo->setResult = tupstore; /* * SFRM_Materialize mode expects us to return a NULL Datum. The actual * tuples are in our tuplestore and passed back through rsinfo->setResult. * rsinfo->setDesc is set to the tuple description that we actually used * to build our tuples with, so the caller can verify we did what it was * expecting. */ return (Datum) 0; }
/** * Entry point for sql function wci.read(..., wci.returnfloat); */ Datum wciReadFloat(PG_FUNCTION_ARGS) { FuncCallContext * funcctx; struct ReadStore * store = NULL; if ( SRF_IS_FIRSTCALL() ) { purgeAllCaches(); funcctx = SRF_FIRSTCALL_INIT(); if (SPI_OK_CONNECT != SPI_connect()) { ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR), errmsg( "unable to connect to SPI procedure"))); } store = (struct ReadStore *) palloc(sizeof(struct ReadStore)); ReadStoreFloatReturnInit(store); funcctx->user_fctx = (void *) store; runWciReadFloatQueryFloat(store, fcinfo); MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); TupleDesc tupdesc; if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg( "function returning record called in context " "that cannot accept type record"))); funcctx->tuple_desc = BlessTupleDesc(tupdesc); MemoryContextSwitchTo(oldcontext); } funcctx = SRF_PERCALL_SETUP(); // return another item: store = (struct ReadStore *) funcctx->user_fctx; if ( store->returnMode == ReturningFromFloatTable ) { if ( getNextRowFromFloatTable(store) ) { MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); HeapTuple tuple = heap_form_tuple(funcctx->tuple_desc, store->values, store->isNull); Datum result = HeapTupleGetDatum(tuple); MemoryContextSwitchTo(oldcontext); SRF_RETURN_NEXT(funcctx, result); } else runWciReadFloatQueryGrid(store, funcctx, fcinfo); // sets store->returnMode to ReturningFromGridTable } if ( store->returnMode == ReturningFromGridTable ) { if ( ! getNextRowFromGridTable(store) ) { // if nextRow returned false, we are done ReadStoreDelete(store); SPI_finish(); SRF_RETURN_DONE(funcctx); } // Create return Datum MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); HeapTuple tuple = heap_form_tuple(funcctx->tuple_desc, store->values, store->isNull); Datum result = HeapTupleGetDatum(tuple); MemoryContextSwitchTo(oldcontext); SRF_RETURN_NEXT(funcctx, result); } // Never reached elog(ERROR, "Code flow reached an unexpeced point: %s:%d", __FILE__, __LINE__); SRF_RETURN_DONE(funcctx); }
Datum exec_sql_using(PG_FUNCTION_ARGS) { HeapTuple procedureTuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(fcinfo->flinfo->fn_oid)); if (!HeapTupleIsValid(procedureTuple)) ereport(ERROR, (errmsg("cache lookup failed for function %u", fcinfo->flinfo->fn_oid))); Oid* types = NULL; char** names = NULL; char* modes = NULL; int nargs = get_func_arg_info(procedureTuple, &types, &names, &modes); Oid resultTypeOid; TupleDesc tupleDesc; TypeFuncClass resultType = get_call_result_type(fcinfo, &resultTypeOid, &tupleDesc); bool returnTypeIsByValue; int16 returnTypeLen; get_typlenbyval(resultTypeOid, &returnTypeLen, &returnTypeIsByValue); if (resultType != TYPEFUNC_SCALAR && resultType != TYPEFUNC_COMPOSITE) ereport(ERROR, ( errmsg("function \"%s\" has indeterminable result type", format_procedure(fcinfo->flinfo->fn_oid)) )); bool returnVoid = resultTypeOid == VOIDOID; ReleaseSysCache(procedureTuple); if (nargs < 2) ereport(ERROR, ( errmsg("function \"%s\" has less than 2 arguments", format_procedure(fcinfo->flinfo->fn_oid)) )); else if (modes != NULL) for (int i = 0; i < nargs; i++) { if (modes[i] != PROARGMODE_IN) ereport(ERROR, ( errmsg("function \"%s\" has non-IN arguments", format_procedure(fcinfo->flinfo->fn_oid)) )); } else if (PG_ARGISNULL(0)) ereport(ERROR, ( errmsg("function \"%s\" called with NULL as first argument", format_procedure(fcinfo->flinfo->fn_oid)) )); char* stmt = NULL; if (types[0] == TEXTOID) stmt = DatumGetCString( DirectFunctionCall1(textout, PG_GETARG_DATUM(0))); else if (types[0] == VARCHAROID) stmt = DatumGetCString( DirectFunctionCall1(varcharout, PG_GETARG_DATUM(0))); else ereport(ERROR, ( errmsg("function \"%s\" does not have a leading VARCHAR/TEXT " "argument", format_procedure(fcinfo->flinfo->fn_oid)) )); char* nulls = NULL; for (int i = 1; i < nargs; i++) if (PG_ARGISNULL(i)) { if (nulls == NULL) { nulls = palloc0(sizeof(char) * (nargs - 1)); memset(nulls, ' ', nargs - 1); } nulls[i - 1] = 'n'; } SPI_connect(); SPIPlanPtr plan = SPI_prepare(stmt, nargs - 1, &types[1]); if (plan == NULL) ereport(ERROR, ( errmsg("function \"%s\" could not obtain execution plan for " "SQL statement", format_procedure(fcinfo->flinfo->fn_oid)) )); int result = SPI_execute_plan(plan, &fcinfo->arg[1], nulls, false, returnVoid ? 0 : 1); Datum returnValue = 0; bool returnNull = false; if (!returnVoid) { if (result != SPI_OK_SELECT && result != SPI_OK_INSERT_RETURNING && result != SPI_OK_DELETE_RETURNING && result == SPI_OK_UPDATE_RETURNING) ereport(ERROR, ( errmsg("function \"%s\" could not obtain result from query", format_procedure(fcinfo->flinfo->fn_oid)) )); else if (SPI_tuptable->tupdesc->natts != 1) ereport(ERROR, ( errmsg("function \"%s\" retrieved more than one column from " "query", format_procedure(fcinfo->flinfo->fn_oid)) )); else if (resultTypeOid != SPI_gettypeid(SPI_tuptable->tupdesc, 1)) ereport(ERROR, ( errmsg("function \"%s\" has different return type OID than " "what query returned", format_procedure(fcinfo->flinfo->fn_oid)) )); /* It is important to copy the value into the upper executor context, * i.e., the memory context that was current when SPI_connect was * called */ returnValue = SPI_getbinval(SPI_copytuple(SPI_tuptable->vals[0]), SPI_tuptable->tupdesc, 1, &returnNull); } SPI_freeplan(plan); if (nulls) pfree(nulls); SPI_finish(); if (result < 0) ereport(ERROR, ( errmsg("function \"%s\" encountered error %d during SQL execution", format_procedure(fcinfo->flinfo->fn_oid), result) )); if (returnVoid) PG_RETURN_VOID(); else if (returnNull) PG_RETURN_NULL(); else return returnValue; }
/* * refresh_by_match_merge * * Refresh a materialized view with transactional semantics, while allowing * concurrent reads. * * This is called after a new version of the data has been created in a * temporary table. It performs a full outer join against the old version of * the data, producing "diff" results. This join cannot work if there are any * duplicated rows in either the old or new versions, in the sense that every * column would compare as equal between the two rows. It does work correctly * in the face of rows which have at least one NULL value, with all non-NULL * columns equal. The behavior of NULLs on equality tests and on UNIQUE * indexes turns out to be quite convenient here; the tests we need to make * are consistent with default behavior. If there is at least one UNIQUE * index on the materialized view, we have exactly the guarantee we need. * * The temporary table used to hold the diff results contains just the TID of * the old record (if matched) and the ROW from the new table as a single * column of complex record type (if matched). * * Once we have the diff table, we perform set-based DELETE and INSERT * operations against the materialized view, and discard both temporary * tables. * * Everything from the generation of the new data to applying the differences * takes place under cover of an ExclusiveLock, since it seems as though we * would want to prohibit not only concurrent REFRESH operations, but also * incremental maintenance. It also doesn't seem reasonable or safe to allow * SELECT FOR UPDATE or SELECT FOR SHARE on rows being updated or deleted by * this command. */ static void refresh_by_match_merge(Oid matviewOid, Oid tempOid) { StringInfoData querybuf; Relation matviewRel; Relation tempRel; char *matviewname; char *tempname; char *diffname; TupleDesc tupdesc; bool foundUniqueIndex; List *indexoidlist; ListCell *indexoidscan; int16 relnatts; bool *usedForQual; Oid save_userid; int save_sec_context; int save_nestlevel; initStringInfo(&querybuf); matviewRel = heap_open(matviewOid, NoLock); matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)), RelationGetRelationName(matviewRel)); tempRel = heap_open(tempOid, NoLock); tempname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(tempRel)), RelationGetRelationName(tempRel)); diffname = make_temptable_name_n(tempname, 2); relnatts = matviewRel->rd_rel->relnatts; usedForQual = (bool *) palloc0(sizeof(bool) * relnatts); /* Open SPI context. */ if (SPI_connect() != SPI_OK_CONNECT) elog(ERROR, "SPI_connect failed"); /* Analyze the temp table with the new contents. */ appendStringInfo(&querybuf, "ANALYZE %s", tempname); if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY) elog(ERROR, "SPI_exec failed: %s", querybuf.data); /* * We need to ensure that there are not duplicate rows without NULLs in * the new data set before we can count on the "diff" results. Check for * that in a way that allows showing the first duplicated row found. Even * after we pass this test, a unique index on the materialized view may * find a duplicate key problem. */ resetStringInfo(&querybuf); appendStringInfo(&querybuf, "SELECT newdata FROM %s newdata " "WHERE newdata IS NOT NULL AND EXISTS " "(SELECT * FROM %s newdata2 WHERE newdata2 IS NOT NULL " "AND newdata2 OPERATOR(pg_catalog.=) newdata " "AND newdata2.ctid OPERATOR(pg_catalog.<>) " "newdata.ctid) LIMIT 1", tempname, tempname); if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT) elog(ERROR, "SPI_exec failed: %s", querybuf.data); if (SPI_processed > 0) { ereport(ERROR, (errcode(ERRCODE_CARDINALITY_VIOLATION), errmsg("new data for \"%s\" contains duplicate rows without any NULL columns", RelationGetRelationName(matviewRel)), errdetail("Row: %s", SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)))); } /* Start building the query for creating the diff table. */ resetStringInfo(&querybuf); appendStringInfo(&querybuf, "CREATE TEMP TABLE %s AS " "SELECT mv.ctid AS tid, newdata " "FROM %s mv FULL JOIN %s newdata ON (", diffname, matviewname, tempname); /* * Get the list of index OIDs for the table from the relcache, and look up * each one in the pg_index syscache. We will test for equality on all * columns present in all unique indexes which only reference columns and * include all rows. */ tupdesc = matviewRel->rd_att; foundUniqueIndex = false; indexoidlist = RelationGetIndexList(matviewRel); foreach(indexoidscan, indexoidlist) { Oid indexoid = lfirst_oid(indexoidscan); Relation indexRel; HeapTuple indexTuple; Form_pg_index indexStruct; indexRel = index_open(indexoid, RowExclusiveLock); indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid)); if (!HeapTupleIsValid(indexTuple)) /* should not happen */ elog(ERROR, "cache lookup failed for index %u", indexoid); indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); /* We're only interested if it is unique and valid. */ if (indexStruct->indisunique && IndexIsValid(indexStruct)) { int numatts = indexStruct->indnatts; int i; /* Skip any index on an expression. */ if (RelationGetIndexExpressions(indexRel) != NIL) { index_close(indexRel, NoLock); ReleaseSysCache(indexTuple); continue; } /* Skip partial indexes. */ if (RelationGetIndexPredicate(indexRel) != NIL) { index_close(indexRel, NoLock); ReleaseSysCache(indexTuple); continue; } /* Hold the locks, since we're about to run DML which needs them. */ index_close(indexRel, NoLock); /* Add quals for all columns from this index. */ for (i = 0; i < numatts; i++) { int attnum = indexStruct->indkey.values[i]; Oid type; Oid op; const char *colname; /* * Only include the column once regardless of how many times * it shows up in how many indexes. * * This is also useful later to omit columns which can not * have changed from the SET clause of the UPDATE statement. */ if (usedForQual[attnum - 1]) continue; usedForQual[attnum - 1] = true; /* * Actually add the qual, ANDed with any others. */ if (foundUniqueIndex) appendStringInfoString(&querybuf, " AND "); colname = quote_identifier(NameStr((tupdesc->attrs[attnum - 1])->attname)); appendStringInfo(&querybuf, "newdata.%s ", colname); type = attnumTypeId(matviewRel, attnum); op = lookup_type_cache(type, TYPECACHE_EQ_OPR)->eq_opr; mv_GenerateOper(&querybuf, op); appendStringInfo(&querybuf, " mv.%s", colname); foundUniqueIndex = true; } } ReleaseSysCache(indexTuple); }