/**
* @fn Datum repack_apply(PG_FUNCTION_ARGS)
* @brief Apply operations in log table into temp table.
*
* repack_apply(sql_peek, sql_insert, sql_delete, sql_update, sql_pop, count)
*
* @param sql_peek SQL to pop tuple from log table.
* @param sql_insert SQL to insert into temp table.
* @param sql_delete SQL to delete from temp table.
* @param sql_update SQL to update temp table.
* @param sql_pop SQL to bulk-delete tuples from log table.
* @param count Max number of operations, or no count iff <=0.
* @retval Number of performed operations.
*/
Datum
repack_apply(PG_FUNCTION_ARGS)
{
#define DEFAULT_PEEK_COUNT 1000
const char *sql_peek = PG_GETARG_CSTRING(0);
const char *sql_insert = PG_GETARG_CSTRING(1);
const char *sql_delete = PG_GETARG_CSTRING(2);
const char *sql_update = PG_GETARG_CSTRING(3);
/* sql_pop, the fourth arg, will be used in the loop below */
int32 count = PG_GETARG_INT32(5);
SPIPlanPtr plan_peek = NULL;
SPIPlanPtr plan_insert = NULL;
SPIPlanPtr plan_delete = NULL;
SPIPlanPtr plan_update = NULL;
uint32 n, i;
Oid argtypes_peek[1] = { INT4OID };
Datum values_peek[1];
const char nulls_peek[1] = { 0 };
StringInfoData sql_pop;
initStringInfo(&sql_pop);
/* authority check */
must_be_superuser("repack_apply");
/* connect to SPI manager */
repack_init();
/* peek tuple in log */
plan_peek = repack_prepare(sql_peek, 1, argtypes_peek);
for (n = 0;;)
{
int ntuples;
SPITupleTable *tuptable;
TupleDesc desc;
Oid argtypes[3]; /* id, pk, row */
Datum values[3]; /* id, pk, row */
bool nulls[3]; /* id, pk, row */
/* peek tuple in log */
if (count <= 0)
values_peek[0] = Int32GetDatum(DEFAULT_PEEK_COUNT);
else
values_peek[0] = Int32GetDatum(Min(count - n, DEFAULT_PEEK_COUNT));
execute_plan(SPI_OK_SELECT, plan_peek, values_peek, nulls_peek);
if (SPI_processed <= 0)
break;
/* copy tuptable because we will call other sqls. */
ntuples = SPI_processed;
tuptable = SPI_tuptable;
desc = tuptable->tupdesc;
argtypes[0] = SPI_gettypeid(desc, 1); /* id */
argtypes[1] = SPI_gettypeid(desc, 2); /* pk */
argtypes[2] = SPI_gettypeid(desc, 3); /* row */
resetStringInfo(&sql_pop);
appendStringInfoString(&sql_pop, PG_GETARG_CSTRING(4));
for (i = 0; i < ntuples; i++, n++)
{
HeapTuple tuple;
char *pkid;
tuple = tuptable->vals[i];
values[0] = SPI_getbinval(tuple, desc, 1, &nulls[0]);
values[1] = SPI_getbinval(tuple, desc, 2, &nulls[1]);
values[2] = SPI_getbinval(tuple, desc, 3, &nulls[2]);
pkid = SPI_getvalue(tuple, desc, 1);
Assert(pkid != NULL);
if (nulls[1])
{
/* INSERT */
if (plan_insert == NULL)
plan_insert = repack_prepare(sql_insert, 1, &argtypes[2]);
execute_plan(SPI_OK_INSERT, plan_insert, &values[2], (nulls[2] ? "n" : " "));
}
else if (nulls[2])
{
/* DELETE */
if (plan_delete == NULL)
plan_delete = repack_prepare(sql_delete, 1, &argtypes[1]);
execute_plan(SPI_OK_DELETE, plan_delete, &values[1], (nulls[1] ? "n" : " "));
}
else
{
/* UPDATE */
if (plan_update == NULL)
plan_update = repack_prepare(sql_update, 2, &argtypes[1]);
execute_plan(SPI_OK_UPDATE, plan_update, &values[1], (nulls[1] ? "n" : " "));
}
/* Add the primary key ID of each row from the log
* table we have processed so far to this
* DELETE ... IN (...) query string, so we
* can delete all the rows we have processed at-once.
*/
if (i == 0)
appendStringInfoString(&sql_pop, pkid);
else
appendStringInfo(&sql_pop, ",%s", pkid);
pfree(pkid);
}
/* i must be > 0 (and hence we must have some rows to delete)
* since SPI_processed > 0
*/
Assert(i > 0);
appendStringInfoString(&sql_pop, ");");
/* Bulk delete of processed rows from the log table */
execute(SPI_OK_DELETE, sql_pop.data);
SPI_freetuptable(tuptable);
}
SPI_finish();
PG_RETURN_INT32(n);
}
/**
* @fn Datum repack_apply(PG_FUNCTION_ARGS)
* @brief Apply operations in log table into temp table.
*
* repack_apply(sql_peek, sql_insert, sql_delete, sql_update, sql_pop, count)
*
* @param sql_peek SQL to pop tuple from log table.
* @param sql_insert SQL to insert into temp table.
* @param sql_delete SQL to delete from temp table.
* @param sql_update SQL to update temp table.
* @param sql_pop SQL to delete tuple from log table.
* @param count Max number of operations, or no count iff <=0.
* @retval Number of performed operations.
*/
Datum
repack_apply(PG_FUNCTION_ARGS)
{
#define DEFAULT_PEEK_COUNT 1000
const char *sql_peek = PG_GETARG_CSTRING(0);
const char *sql_insert = PG_GETARG_CSTRING(1);
const char *sql_delete = PG_GETARG_CSTRING(2);
const char *sql_update = PG_GETARG_CSTRING(3);
const char *sql_pop = PG_GETARG_CSTRING(4);
int32 count = PG_GETARG_INT32(5);
SPIPlanPtr plan_peek = NULL;
SPIPlanPtr plan_insert = NULL;
SPIPlanPtr plan_delete = NULL;
SPIPlanPtr plan_update = NULL;
SPIPlanPtr plan_pop = NULL;
uint32 n, i;
Oid argtypes_peek[1] = { INT4OID };
Datum values_peek[1];
bool nulls_peek[1] = { 0 };
/* authority check */
must_be_superuser("repack_apply");
/* connect to SPI manager */
repack_init();
/* peek tuple in log */
plan_peek = repack_prepare(sql_peek, 1, argtypes_peek);
for (n = 0;;)
{
int ntuples;
SPITupleTable *tuptable;
TupleDesc desc;
Oid argtypes[3]; /* id, pk, row */
Datum values[3]; /* id, pk, row */
bool nulls[3]; /* id, pk, row */
/* peek tuple in log */
if (count == 0)
values_peek[0] = Int32GetDatum(DEFAULT_PEEK_COUNT);
else
values_peek[0] = Int32GetDatum(Min(count - n, DEFAULT_PEEK_COUNT));
execute_plan(SPI_OK_SELECT, plan_peek, values_peek, nulls_peek);
if (SPI_processed <= 0)
break;
/* copy tuptable because we will call other sqls. */
ntuples = SPI_processed;
tuptable = SPI_tuptable;
desc = tuptable->tupdesc;
argtypes[0] = SPI_gettypeid(desc, 1); /* id */
argtypes[1] = SPI_gettypeid(desc, 2); /* pk */
argtypes[2] = SPI_gettypeid(desc, 3); /* row */
for (i = 0; i < ntuples; i++, n++)
{
HeapTuple tuple;
tuple = tuptable->vals[i];
values[0] = SPI_getbinval(tuple, desc, 1, &nulls[0]);
values[1] = SPI_getbinval(tuple, desc, 2, &nulls[1]);
values[2] = SPI_getbinval(tuple, desc, 3, &nulls[2]);
if (nulls[1])
{
/* INSERT */
if (plan_insert == NULL)
plan_insert = repack_prepare(sql_insert, 1, &argtypes[2]);
execute_plan(SPI_OK_INSERT, plan_insert, &values[2], &nulls[2]);
}
else if (nulls[2])
{
/* DELETE */
if (plan_delete == NULL)
plan_delete = repack_prepare(sql_delete, 1, &argtypes[1]);
execute_plan(SPI_OK_DELETE, plan_delete, &values[1], &nulls[1]);
}
else
{
/* UPDATE */
if (plan_update == NULL)
plan_update = repack_prepare(sql_update, 2, &argtypes[1]);
execute_plan(SPI_OK_UPDATE, plan_update, &values[1], &nulls[1]);
}
}
/* delete tuple in log */
if (plan_pop == NULL)
plan_pop = repack_prepare(sql_pop, 1, argtypes);
execute_plan(SPI_OK_DELETE, plan_pop, values, nulls);
SPI_freetuptable(tuptable);
}
SPI_finish();
PG_RETURN_INT32(n);
}
