/*
* 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);
}
