/* * Initialize workspace for a worker process: create the schema if it doesn't * already exist. */ static void initialize_worker_spi(worktable *table) { int ret; int ntup; bool isnull; StringInfoData buf; SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); pgstat_report_activity(STATE_RUNNING, "initializing spi_worker schema"); /* XXX could we use CREATE SCHEMA IF NOT EXISTS? */ initStringInfo(&buf); appendStringInfo(&buf, "select count(*) from pg_namespace where nspname = '%s'", table->schema); ret = SPI_execute(buf.data, true, 0); if (ret != SPI_OK_SELECT) elog(FATAL, "SPI_execute failed: error code %d", ret); if (SPI_processed != 1) elog(FATAL, "not a singleton result"); ntup = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull)); if (isnull) elog(FATAL, "null result"); if (ntup == 0) { resetStringInfo(&buf); appendStringInfo(&buf, "CREATE SCHEMA \"%s\" " "CREATE TABLE \"%s\" (" " type text CHECK (type IN ('total', 'delta')), " " value integer)" "CREATE UNIQUE INDEX \"%s_unique_total\" ON \"%s\" (type) " "WHERE type = 'total'", table->schema, table->name, table->name, table->name); /* set statement start time */ SetCurrentStatementStartTimestamp(); ret = SPI_execute(buf.data, false, 0); if (ret != SPI_OK_UTILITY) elog(FATAL, "failed to create my schema"); } SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); pgstat_report_activity(STATE_IDLE, NULL); }
Datum cdb_get_oid(PG_FUNCTION_ARGS) { int result; if ( SPI_OK_CONNECT != SPI_connect() ) { ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("SPI_connect failed in cdb_eget_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_get_oid" ))); } if ( SPI_OK_INSERT != SPI_execute( "insert into pgdump_oid values(0)", false, 0 ) ) { ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("SPI_execute failed to insert a row into pgdump_oid in cdb_get_oid" ))); } if ( SPI_OK_SELECT != SPI_execute( "select oid from pgdump_oid", false, 0 ) ) { ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("SPI_execute failed in cdb_get_oid" ))); } if ( SPI_processed == 0 ) ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("No rows in pgdump_oid in cdb_get_oid" ))); TupleDesc tupdesc = SPI_tuptable->tupdesc; result = atoi( SPI_getvalue( SPI_tuptable->vals[0], tupdesc, 1)); 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_get_oid" ))); } SPI_finish(); PG_RETURN_INT32(result); }
/* * This function will set a string in shared memory which is the name of the database to connect to * the next time the background worker restarts. Because a bgworker can only connect to one database * at a time, and some catalogs and stats are scoped to the current database, the bg worker * periodically restarts to collect latest stats from another database. * */ int set_next_db_target(void) { int retval, processed; StringInfoData buf; SPITupleTable *coltuptable; char* next_db_target; SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); /* get sorted list of databases, find one after target_db*/ initStringInfo(&buf); appendStringInfo(&buf, "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1') AND datallowconn IS TRUE AND datname > '%s' ORDER BY datname ASC LIMIT 1;", target_db ); retval = SPI_execute(buf.data, false, 0); if (retval != SPI_OK_SELECT) { elog(FATAL, "Database information collection failed"); // FAIL RETURN 1 } processed = SPI_processed; if(processed == 0) { //No matching records so pick first database. resetStringInfo(&buf); appendStringInfoString(&buf, "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1') AND datallowconn IS TRUE ORDER BY datname ASC LIMIT 1;" ); retval = SPI_execute(buf.data, false, 0); if (retval != SPI_OK_SELECT) { elog(FATAL, "Database information collection failed"); // FAIL RETURN 1 } } coltuptable = SPI_tuptable; next_db_target = SPI_getvalue(coltuptable->vals[0], coltuptable->tupdesc, 1); // elog(LOG, "NEXTDB TARGET: %s", next_db_target); //print next target db strcpy(pgsampler_state->next_db, next_db_target); SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); return 0; }
/* * Returns a count of the number of non-template databases from the catalog. */ int get_database_count(void) { int retval, processed; StringInfoData buf; SPITupleTable *coltuptable; int database_count = 0; SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); initStringInfo(&buf); appendStringInfo(&buf, "SELECT count(*) FROM pg_database WHERE datname NOT IN ('template0', 'template1') AND datallowconn IS TRUE;"); retval = SPI_execute(buf.data, false, 0); if (retval != SPI_OK_SELECT) { elog(FATAL, "Database information collection failed"); // FAIL RETURN 1 } processed = SPI_processed; if (processed > 0) { coltuptable = SPI_tuptable; database_count = atoi(SPI_getvalue(coltuptable->vals[0], coltuptable->tupdesc, 1)); } SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); return database_count; }
/* * Create indexes on shreded data * @return true if succed */ bool create_indexes_on_tables(void) { bool result = false; SPI_connect(); if (SPI_execute("CREATE INDEX attr_tab_all_index ON attribute_table (name, did, pre_order); " "CREATE INDEX attr_tab_range_index ON element_table USING gist (range_i(pre_order, (pre_order+size)));" "CREATE INDEX did_tab_name_index ON xml_documents_table (name); " "CREATE INDEX elem_tab_all_index ON element_table (name, did, pre_order, size); " "CREATE INDEX elem_tab_range_index ON element_table USING gist (range(pre_order, (pre_order+size)));" "CREATE INDEX text_tab_index ON text_table (parent_id,did);" , false, 0) == SPI_ERROR_PARAM) { ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg("Can not get ID of lastly inserted XML document"))); } result = true; SPI_finish(); return result; }
/* simple execute */ void execute(int expected, const char *sql) { int ret = SPI_execute(sql, false, 0); if EXEC_FAILED(ret, expected) elog(ERROR, "query failed: (sql=%s, code=%d, expected=%d)", sql, ret, expected); }
Oid getIntOid() { Oid out; int ret; bool isnull; bool do_pop = false; do_pop = _SPI_conn(); /* * Get OID of our internal data type. This is necessary because record_in and * record_out need it. */ if ( (ret = SPI_execute("SELECT 'variant._variant'::regtype::oid", true, 1)) != SPI_OK_SELECT ) elog( ERROR, "SPI_execute returned %s", SPI_result_code_string(ret)); /* Don't need to copy the tuple because Oid is pass by value */ out = DatumGetObjectId( heap_getattr(SPI_tuptable->vals[0], 1, SPI_tuptable->tupdesc, &isnull) ); /* Remember this frees everything palloc'd since our connect/push call */ _SPI_disc(do_pop); return out; }
struct PlaceSpecification * getPlaceSpecificationFromDatabase(long long placeid) { const char * query = build_placeSpecQuery(placeid); if ( SPI_execute(query, true, 1) != SPI_OK_SELECT ) ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg( "Error when performing placeid query"))); if ( SPI_processed < 1 ) ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg( "unable to find placespecification"))); else if ( SPI_processed > 1 ) ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg( "too many placespecifications returned!"))); HeapTuple placeRow = * SPI_tuptable->vals; struct PlaceSpecification * ret = (struct PlaceSpecification *) malloc(sizeof(struct PlaceSpecification)); ret->startX_ = DatumGetFloat4( SPI_getbinval(placeRow, SPI_tuptable->tupdesc, 1, NULL) ); ret->startY_ = DatumGetFloat4( SPI_getbinval(placeRow, SPI_tuptable->tupdesc, 2, NULL) ); ret->xNumber_ = DatumGetInt32( SPI_getbinval(placeRow, SPI_tuptable->tupdesc, 3, NULL) ); ret->yNumber_ = DatumGetInt32( SPI_getbinval(placeRow, SPI_tuptable->tupdesc, 4, NULL) ); ret->xIncrement_ = DatumGetFloat4( SPI_getbinval(placeRow, SPI_tuptable->tupdesc, 5, NULL) ); ret->yIncrement_ = DatumGetFloat4( SPI_getbinval(placeRow, SPI_tuptable->tupdesc, 6, NULL) ); ret->srid_ = DatumGetInt32( SPI_getbinval(placeRow, SPI_tuptable->tupdesc, 7, NULL) ); char * projDef = SPI_getvalue(placeRow, SPI_tuptable->tupdesc, 8); ret->projDefinition_ = strdup(projDef); pfree(projDef); return ret; }
long long * all_referenced_files(int * countOut) { char query[128]; snprintf(query, 128, "SELECT file_id FROM "WDB_SCHEMA".file_blob"); SPI_connect(); int result = SPI_execute(query, true, 0); if ( SPI_OK_SELECT != result ) ereport(ERROR, (errcode( ERRCODE_RAISE_EXCEPTION ), errmsg("Error when reading from file_blob"))); * countOut = SPI_processed; long long * ret = (long long *) SPI_palloc(sizeof(long long) * (* countOut)); int i; for ( i = 0; i < * countOut; ++ i ) { bool isNull; // unused Datum d = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1, & isNull); ret[i] = DatumGetInt64(d); } SPI_finish(); return ret; }
char *lookup_field_mapping(MemoryContext cxt, Oid tableRelId, char *fieldname) { char *definition = NULL; StringInfo query; SPI_connect(); query = makeStringInfo(); appendStringInfo(query, "select definition from zdb_mappings where table_name = %d::regclass and field_name = %s;", tableRelId, TextDatumGetCString(DirectFunctionCall1(quote_literal, CStringGetTextDatum(fieldname)))); if (SPI_execute(query->data, true, 2) != SPI_OK_SELECT) elog(ERROR, "Problem looking up analysis thing with query: %s", query->data); if (SPI_processed > 1) { elog(ERROR, "Too many mappings found"); } else if (SPI_processed == 1) { char *json = SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1); Size len = strlen(json); definition = (char *) MemoryContextAllocZero(cxt, (Size) len + 1); memcpy(definition, json, len); } SPI_finish(); return definition; }
char *lookup_analysis_thing(MemoryContext cxt, char *thing) { char *definition = ""; StringInfo query; SPI_connect(); query = makeStringInfo(); appendStringInfo(query, "select (to_json(name) || ':' || definition) from %s;", TextDatumGetCString(DirectFunctionCall1(quote_ident, CStringGetTextDatum(thing)))); if (SPI_execute(query->data, true, 0) != SPI_OK_SELECT) elog(ERROR, "Problem looking up analysis thing with query: %s", query->data); if (SPI_processed > 0) { StringInfo json = makeStringInfo(); int i; for (i = 0; i < SPI_processed; i++) { if (i > 0) appendStringInfoCharMacro(json, ','); appendStringInfo(json, "%s", SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1)); } definition = (char *) MemoryContextAllocZero(cxt, (Size) json->len + 1); memcpy(definition, json->data, json->len); } SPI_finish(); return definition; }
/* * Create tables as storage of shreded data * @param none * @return true/false */ Datum create_xmlindex_tables(PG_FUNCTION_ARGS) { StringInfoData query; initStringInfo(&query); appendStringInfo(&query, "CREATE TABLE xml_documents_table " "(did serial not null, " "name text, " "value xml," "xdb_sequence int default 0); " "CREATE TABLE attribute_table " "(name text, " "did int not null, " "pre_order int not null, " "size int not null, " "depth int, " "parent_id int, " "prev_id int, " "value text," "PRIMARY KEY (did,pre_order)); " "CREATE TABLE element_table " "(name text, " "did int not null, " "pre_order int not null, " "size int not null, " "depth int, " "parent_id int, " "prev_id int, " "child_id int, " "attr_id int, " "PRIMARY KEY (did,pre_order,size));" "CREATE TABLE text_table " "(did int not null, " "pre_order int not null, " "depth int not null, " "parent_id int, " "prev_id int, " "value text, " "PRIMARY KEY (pre_order, did));" ); SPI_connect(); if (SPI_execute(query.data, false, 0) == SPI_ERROR_ARGUMENT) { ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg("invalid query"))); } // TODO add foreign key to xml_documents_table SPI_finish(); create_indexes_on_tables(); PG_RETURN_BOOL(true); }
char *lookup_primary_key(char *schemaName, char *tableName, bool failOnMissing) { StringInfo sql = makeStringInfo(); char *keyname; SPI_connect(); appendStringInfo(sql, "SELECT column_name FROM information_schema.key_column_usage WHERE table_schema = '%s' AND table_name = '%s'", schemaName, tableName); SPI_execute(sql->data, true, 1); if (SPI_processed == 0) { if (failOnMissing) elog(ERROR, "Cannot find primary key column for: %s.%s", schemaName, tableName); else { SPI_finish(); return NULL; } } keyname = SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1); if (keyname == NULL) elog(ERROR, "Primary Key field is null for: %s.%s", schemaName, tableName); keyname = MemoryContextStrdup(TopTransactionContext, keyname); SPI_finish(); return keyname; }
bool type_is_domain(char *type_name, Oid *base_type) { bool rc; StringInfo query; SPI_connect(); query = makeStringInfo(); appendStringInfo(query, "SELECT typtype = 'd', typbasetype FROM pg_type WHERE typname = %s", TextDatumGetCString(DirectFunctionCall1(quote_literal, CStringGetTextDatum(type_name)))); if (SPI_execute(query->data, true, 1) != SPI_OK_SELECT) elog(ERROR, "Problem determing if %s is a domain with query: %s", type_name, query->data); if (SPI_processed == 0) { rc = false; } else { bool isnull; Datum d; d = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull); rc = isnull || DatumGetBool(d); d = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 2, &isnull); *base_type = isnull ? InvalidOid : DatumGetObjectId(d); } SPI_finish(); return rc; }
static void getExtensionLoadPath() { MemoryContext curr; Datum dtm; bool isnull; /* * Check whether sqlj.loadpath exists before querying it. I would more * happily just PG_CATCH() the error and compare to ERRCODE_UNDEFINED_TABLE * but what's required to make that work right is "not terribly well * documented, but the exception-block handling in plpgsql provides a * working model" and that code is a lot more fiddly than you would guess. */ if ( InvalidOid == get_relname_relid("loadpath", GetSysCacheOid1(NAMESPACENAME, CStringGetDatum("sqlj"))) ) return; SPI_connect(); curr = CurrentMemoryContext; if ( SPI_OK_SELECT == SPI_execute( "SELECT path, exnihilo FROM sqlj.loadpath", true, 1) && 1 == SPI_processed ) { MemoryContextSwitchTo(TopMemoryContext); pljavaLoadPath = (char const *)SPI_getvalue( SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1); MemoryContextSwitchTo(curr); dtm = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 2, &isnull); if ( isnull ) elog(ERROR, "defect in CREATE EXTENSION script"); extensionExNihilo = DatumGetBool(dtm); } SPI_finish(); }
/* * Ensure that the environment is sane. * This involves checking the Postgresql version, and if in network mode * also establishing a connection to a receiver. */ int ensure_valid_environment(void) { StringInfoData buf; int retval; char* pgversion; SPITupleTable *coltuptable; SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); /* Ensure compatible version */ pgstat_report_activity(STATE_RUNNING, "verifying compatible postgres version"); initStringInfo(&buf); appendStringInfo(&buf, "select version();" ); retval = SPI_execute(buf.data, false, 0); if (retval != SPI_OK_SELECT) { elog(FATAL, "Unable to query postgres version %d", retval); SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); return 1; } coltuptable = SPI_tuptable; pgversion = SPI_getvalue(coltuptable->vals[0], coltuptable->tupdesc, 1); if(strstr(pgversion, "PostgreSQL 9.3") == NULL) { elog(FATAL, "Unsupported Postgresql version"); SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); return 1; } SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); /* * Attempt to establish a connection if the output mode is network. */ if (strcmp(output_mode, "network") == 0) { retval = establish_connection(); if (retval == 2) { elog(LOG, "Error : Failed to connect to antenna please check domain is available from host."); } } //TODO verify logging directory is accessible when csv mode. elog(LOG, "Pgsampler Initialized"); return 0; }
/* * Performs Actual Query execution */ int Persistent_ExecuteQuery(char const *query, bool readOnlyQuery) { StringInfoData sqlstmt; int ret; int proc = 0; Assert (query); Insist(connected); /*Initializations*/ sqlstmt.data = NULL; /* Assemble our query string */ initStringInfo(&sqlstmt); appendStringInfo(&sqlstmt,"%s",query); PG_TRY(); { /*XXX: Need to set the snapshot here. Reason - Unknown*/ ActiveSnapshot = SnapshotNow; /* Run the query. */ ret = SPI_execute(sqlstmt.data, readOnlyQuery, 0); proc = SPI_processed; if (ret > 0 && SPI_tuptable != NULL) { TupleDesc tupdesc = SPI_tuptable->tupdesc; SPITupleTable* tuptable = SPI_tuptable; int i,j; char localbuf[8192]; for (j = 0; j< proc; j++) { HeapTuple tuple = tuptable->vals[j]; for (i = 1, localbuf[0] = '\0'; i <= tupdesc->natts; i++) { snprintf(localbuf + strlen (localbuf), sizeof(localbuf) - strlen(localbuf), " %s%s", SPI_getvalue(tuple, tupdesc, i), (i == tupdesc->natts) ? " " : " |"); } elog (LOG, "==>: %s", localbuf); } } } /* Clean up in case of error. */ PG_CATCH(); { pfree(sqlstmt.data); PG_RE_THROW(); } PG_END_TRY(); pfree(sqlstmt.data); return proc; }
static void execute_pg_settings_logger(config_log_objects *objects) { int ret; bool isnull; StringInfoData buf; SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); pgstat_report_activity(STATE_RUNNING, "executing configuration logger function"); initStringInfo(&buf); appendStringInfo( &buf, "SELECT %s.%s()", config_log_schema, objects->function_name ); ret = SPI_execute(buf.data, false, 0); if (ret != SPI_OK_SELECT) { elog(FATAL, "SPI_execute failed: error code %d", ret); } if (SPI_processed != 1) { elog(FATAL, "not a singleton result"); } log_info("pg_settings_logger() executed"); if(DatumGetBool(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull))) { log_info("Configuration changes recorded"); } else { log_info("No configuration changes detected"); } SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); pgstat_report_activity(STATE_IDLE, NULL); }
void worker_main(Datum arg) { int ret; StringInfoData buf; uint32 segment = UInt32GetDatum(arg); /* Setup signal handlers */ pqsignal(SIGHUP, worker_sighup); pqsignal(SIGTERM, worker_sigterm); /* Allow signals */ BackgroundWorkerUnblockSignals(); initialize_worker(segment); /* Connect to the database */ BackgroundWorkerInitializeConnection(job->datname, job->rolname); elog(LOG, "%s initialized running job id %d", MyBgworkerEntry->bgw_name, job->job_id); pgstat_report_appname(MyBgworkerEntry->bgw_name); /* Initialize the query text */ initStringInfo(&buf); appendStringInfo(&buf, "SELECT * FROM %s.%s(%d, NULL)", job_run_function.schema, job_run_function.name, job->job_id); /* Initialize the SPI subsystem */ SetCurrentStatementStartTimestamp(); StartTransactionCommand(); SPI_connect(); PushActiveSnapshot(GetTransactionSnapshot()); pgstat_report_activity(STATE_RUNNING, buf.data); SetCurrentStatementStartTimestamp(); /* And run the query */ ret = SPI_execute(buf.data, true, 0); if (ret < 0) elog(FATAL, "errors while executing %s", buf.data); /* Commmit the transaction */ SPI_finish(); PopActiveSnapshot(); CommitTransactionCommand(); pgstat_report_activity(STATE_IDLE, NULL); proc_exit(0); }
Oid *find_all_zdb_indexes(int *many) { Oid *indexes = NULL; int i; SPI_connect(); SPI_execute("select oid from pg_class where relam = (select oid from pg_am where amname = 'zombodb');", true, 0); *many = SPI_processed; if (SPI_processed > 0) { indexes = (Oid *) MemoryContextAlloc(TopTransactionContext, sizeof(Oid) * SPI_processed); for (i = 0; i < SPI_processed; i++) indexes[i] = (Oid) atoi(SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1)); } SPI_finish(); return indexes; }
static void update_gp_master_mirroring(char *str) { volatile bool connected = false; volatile bool resetModsDML = false; PG_TRY(); { StringInfoData sql; initStringInfo(&sql); appendStringInfo(&sql, "update gp_master_mirroring set " "summary_state = '%s', detail_state = null," "log_time = current_timestamp, error_message = null", str); if (SPI_OK_CONNECT != SPI_connect()) elog(ERROR, "cannot connect via SPI"); connected = true; if (!allowSystemTableModsDML) { allowSystemTableModsDML = true; resetModsDML = true; } if (SPI_execute(sql.data, false, 0) < 0) elog(ERROR, "cannot update gp_master_mirroring"); if (resetModsDML) allowSystemTableModsDML = false; } PG_CATCH(); { if (connected) SPI_finish(); if (resetModsDML) allowSystemTableModsDML = false; PG_RE_THROW(); } PG_END_TRY(); SPI_finish(); }
char * getNamedGeometryAsWKT(const char * locationName) { char * query = build_placeNameQuery(locationName); if ( SPI_execute(query, true, 1) != SPI_OK_SELECT ) ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg( "Error when performing placename query"))); pfree(query); if ( SPI_processed == 0 ) return NULL; // ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg( // "unable to find place name"))); else if ( SPI_processed != 1 ) ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg( "too many place names returned!"))); return SPI_getvalue(* SPI_tuptable->vals, SPI_tuptable->tupdesc, 1); }
static PyObject * PLy_spi_execute_query(char *query, long limit) { int rv; volatile MemoryContext oldcontext; volatile ResourceOwner oldowner; PyObject *ret = NULL; oldcontext = CurrentMemoryContext; oldowner = CurrentResourceOwner; PLy_spi_subtransaction_begin(oldcontext, oldowner); PG_TRY(); { PLyExecutionContext *exec_ctx = PLy_current_execution_context(); pg_verifymbstr(query, strlen(query), false); rv = SPI_execute(query, exec_ctx->curr_proc->fn_readonly, limit); ret = PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv); PLy_spi_subtransaction_commit(oldcontext, oldowner); } PG_CATCH(); { PLy_spi_subtransaction_abort(oldcontext, oldowner); return NULL; } PG_END_TRY(); if (rv < 0) { Py_XDECREF(ret); PLy_exception_set(PLy_exc_spi_error, "SPI_execute failed: %s", SPI_result_code_string(rv)); return NULL; } return ret; }
long getDocumentCount() { int spiResultCode; Datum countDatum; bool isNull; long count; SPI_connect(); spiResultCode = SPI_execute("SELECT COUNT(*)::integer FROM CardVectors", 1, 1); if(spiResultCode == SPI_OK_SELECT) { countDatum = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isNull); count = DatumGetInt32(countDatum); } else { ereport(ERROR, (errmsg("Unable to query CardVectors table. Does it exist?"))); count = -1; } SPI_finish(); return count; }
Oid *findZDBIndexes(Oid relid, int *many) { Oid *indexes = NULL; StringInfo sql; int i; SPI_connect(); sql = makeStringInfo(); appendStringInfo(sql, "select indexrelid " "from pg_index " "where indrelid = %d " " and indclass[0] = (select oid from pg_opclass where opcmethod = (select oid from pg_am where amname = 'zombodb') and opcname = 'zombodb_tid_ops')", relid); SPI_execute(sql->data, true, 1); *many = SPI_processed; if (SPI_processed > 0) { indexes = (Oid *) MemoryContextAlloc(TopTransactionContext, sizeof(Oid) * SPI_processed); for (i = 0; i < SPI_processed; i++) indexes[i] = (Oid) atoi(SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1)); } SPI_finish(); return indexes; }
/* * 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; }
/* * 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 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; }
static Tuplestorestate * build_tuplestore_recursively(char *key_fld, char *parent_key_fld, char *relname, char *orderby_fld, char *branch_delim, char *start_with, char *branch, int level, int *serial, int max_depth, bool show_branch, bool show_serial, MemoryContext per_query_ctx, AttInMetadata *attinmeta, Tuplestorestate *tupstore) { TupleDesc tupdesc = attinmeta->tupdesc; int ret; int proc; int serial_column; StringInfoData sql; char **values; char *current_key; char *current_key_parent; char current_level[INT32_STRLEN]; char serial_str[INT32_STRLEN]; char *current_branch; HeapTuple tuple; if (max_depth > 0 && level > max_depth) return tupstore; initStringInfo(&sql); /* Build initial sql statement */ if (!show_serial) { appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s", key_fld, parent_key_fld, relname, parent_key_fld, quote_literal_cstr(start_with), key_fld, key_fld, parent_key_fld); serial_column = 0; } else { appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s ORDER BY %s", key_fld, parent_key_fld, relname, parent_key_fld, quote_literal_cstr(start_with), key_fld, key_fld, parent_key_fld, orderby_fld); serial_column = 1; } if (show_branch) values = (char **) palloc((CONNECTBY_NCOLS + serial_column) * sizeof(char *)); else values = (char **) palloc((CONNECTBY_NCOLS_NOBRANCH + serial_column) * sizeof(char *)); /* First time through, do a little setup */ if (level == 0) { /* root value is the one we initially start with */ values[0] = start_with; /* root value has no parent */ values[1] = NULL; /* root level is 0 */ sprintf(current_level, "%d", level); values[2] = current_level; /* root branch is just starting root value */ if (show_branch) values[3] = start_with; /* root starts the serial with 1 */ if (show_serial) { sprintf(serial_str, "%d", (*serial)++); if (show_branch) values[4] = serial_str; else values[3] = serial_str; } /* construct the tuple */ tuple = BuildTupleFromCStrings(attinmeta, values); /* now store it */ tuplestore_puttuple(tupstore, tuple); /* increment level */ level++; } /* Retrieve the desired rows */ ret = SPI_execute(sql.data, true, 0); proc = SPI_processed; /* Check for qualifying tuples */ if ((ret == SPI_OK_SELECT) && (proc > 0)) { HeapTuple spi_tuple; SPITupleTable *tuptable = SPI_tuptable; TupleDesc spi_tupdesc = tuptable->tupdesc; int i; StringInfoData branchstr; StringInfoData chk_branchstr; StringInfoData chk_current_key; /* First time through, do a little more setup */ if (level == 0) { /* * Check that return tupdesc is compatible with the one we got * from the query, but only at level 0 -- no need to check more * than once */ if (!compatConnectbyTupleDescs(tupdesc, spi_tupdesc)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("invalid return type"), errdetail("Return and SQL tuple descriptions are " \ "incompatible."))); } initStringInfo(&branchstr); initStringInfo(&chk_branchstr); initStringInfo(&chk_current_key); for (i = 0; i < proc; i++) { /* initialize branch for this pass */ appendStringInfo(&branchstr, "%s", branch); appendStringInfo(&chk_branchstr, "%s%s%s", branch_delim, branch, branch_delim); /* get the next sql result tuple */ spi_tuple = tuptable->vals[i]; /* get the current key and parent */ current_key = SPI_getvalue(spi_tuple, spi_tupdesc, 1); appendStringInfo(&chk_current_key, "%s%s%s", branch_delim, current_key, branch_delim); current_key_parent = pstrdup(SPI_getvalue(spi_tuple, spi_tupdesc, 2)); /* get the current level */ sprintf(current_level, "%d", level); /* check to see if this key is also an ancestor */ if (strstr(chk_branchstr.data, chk_current_key.data)) elog(ERROR, "infinite recursion detected"); /* OK, extend the branch */ appendStringInfo(&branchstr, "%s%s", branch_delim, current_key); current_branch = branchstr.data; /* build a tuple */ values[0] = pstrdup(current_key); values[1] = current_key_parent; values[2] = current_level; if (show_branch) values[3] = current_branch; if (show_serial) { sprintf(serial_str, "%d", (*serial)++); if (show_branch) values[4] = serial_str; else values[3] = serial_str; } tuple = BuildTupleFromCStrings(attinmeta, values); xpfree(current_key); xpfree(current_key_parent); /* store the tuple for later use */ tuplestore_puttuple(tupstore, tuple); heap_freetuple(tuple); /* recurse using current_key_parent as the new start_with */ tupstore = build_tuplestore_recursively(key_fld, parent_key_fld, relname, orderby_fld, branch_delim, values[0], current_branch, level + 1, serial, max_depth, show_branch, show_serial, per_query_ctx, attinmeta, tupstore); /* reset branch for next pass */ resetStringInfo(&branchstr); resetStringInfo(&chk_branchstr); resetStringInfo(&chk_current_key); } xpfree(branchstr.data); xpfree(chk_branchstr.data); xpfree(chk_current_key.data); } return tupstore; }
HV * plperl_spi_exec(char *query, int limit) { HV *ret_hv; /* * Execute the query inside a sub-transaction, so we can cope with errors * sanely */ MemoryContext oldcontext = CurrentMemoryContext; ResourceOwner oldowner = CurrentResourceOwner; BeginInternalSubTransaction(NULL); /* Want to run inside function's memory context */ MemoryContextSwitchTo(oldcontext); PG_TRY(); { int spi_rv; spi_rv = SPI_execute(query, plperl_current_prodesc->fn_readonly, limit); ret_hv = plperl_spi_execute_fetch_result(SPI_tuptable, SPI_processed, spi_rv); /* Commit the inner transaction, return to outer xact context */ ReleaseCurrentSubTransaction(); MemoryContextSwitchTo(oldcontext); CurrentResourceOwner = oldowner; /* * AtEOSubXact_SPI() should not have popped any SPI context, but just * in case it did, make sure we remain connected. */ SPI_restore_connection(); } PG_CATCH(); { ErrorData *edata; /* Save error info */ MemoryContextSwitchTo(oldcontext); edata = CopyErrorData(); FlushErrorState(); /* Abort the inner transaction */ RollbackAndReleaseCurrentSubTransaction(); MemoryContextSwitchTo(oldcontext); CurrentResourceOwner = oldowner; /* * If AtEOSubXact_SPI() popped any SPI context of the subxact, it will * have left us in a disconnected state. We need this hack to return * to connected state. */ SPI_restore_connection(); /* Punt the error to Perl */ croak("%s", edata->message); /* Can't get here, but keep compiler quiet */ return NULL; } PG_END_TRY(); return ret_hv; }