1 /* 2 * interface to SPI functions 3 * 4 * src/pl/plpython/plpy_spi.c 5 */ 6 7 #include "postgres.h" 8 9 #include <limits.h> 10 11 #include "access/htup_details.h" 12 #include "access/xact.h" 13 #include "catalog/pg_type.h" 14 #include "executor/spi.h" 15 #include "mb/pg_wchar.h" 16 #include "parser/parse_type.h" 17 #include "utils/memutils.h" 18 #include "utils/syscache.h" 19 20 #include "plpython.h" 21 22 #include "plpy_spi.h" 23 24 #include "plpy_elog.h" 25 #include "plpy_main.h" 26 #include "plpy_planobject.h" 27 #include "plpy_plpymodule.h" 28 #include "plpy_procedure.h" 29 #include "plpy_resultobject.h" 30 31 32 static PyObject *PLy_spi_execute_query(char *query, long limit); 33 static PyObject *PLy_spi_execute_fetch_result(SPITupleTable *tuptable, 34 uint64 rows, int status); 35 static void PLy_spi_exception_set(PyObject *excclass, ErrorData *edata); 36 37 38 /* prepare(query="select * from foo") 39 * prepare(query="select * from foo where bar = $1", params=["text"]) 40 * prepare(query="select * from foo where bar = $1", params=["text"], limit=5) 41 */ 42 PyObject * 43 PLy_spi_prepare(PyObject *self, PyObject *args) 44 { 45 PLyPlanObject *plan; 46 PyObject *list = NULL; 47 PyObject *volatile optr = NULL; 48 char *query; 49 PLyExecutionContext *exec_ctx = PLy_current_execution_context(); 50 volatile MemoryContext oldcontext; 51 volatile ResourceOwner oldowner; 52 volatile int nargs; 53 54 if (!PyArg_ParseTuple(args, "s|O:prepare", &query, &list)) 55 return NULL; 56 57 if (list && (!PySequence_Check(list))) 58 { 59 PLy_exception_set(PyExc_TypeError, 60 "second argument of plpy.prepare must be a sequence"); 61 return NULL; 62 } 63 64 if ((plan = (PLyPlanObject *) PLy_plan_new()) == NULL) 65 return NULL; 66 67 plan->mcxt = AllocSetContextCreate(TopMemoryContext, 68 "PL/Python plan context", 69 ALLOCSET_DEFAULT_SIZES); 70 oldcontext = MemoryContextSwitchTo(plan->mcxt); 71 72 nargs = list ? PySequence_Length(list) : 0; 73 74 plan->nargs = nargs; 75 plan->types = nargs ? palloc0(sizeof(Oid) * nargs) : NULL; 76 plan->values = nargs ? palloc0(sizeof(Datum) * nargs) : NULL; 77 plan->args = nargs ? palloc0(sizeof(PLyObToDatum) * nargs) : NULL; 78 79 MemoryContextSwitchTo(oldcontext); 80 81 oldcontext = CurrentMemoryContext; 82 oldowner = CurrentResourceOwner; 83 84 PLy_spi_subtransaction_begin(oldcontext, oldowner); 85 86 PG_TRY(); 87 { 88 int i; 89 90 for (i = 0; i < nargs; i++) 91 { 92 char *sptr; 93 Oid typeId; 94 int32 typmod; 95 96 optr = PySequence_GetItem(list, i); 97 if (PyString_Check(optr)) 98 sptr = PyString_AsString(optr); 99 else if (PyUnicode_Check(optr)) 100 sptr = PLyUnicode_AsString(optr); 101 else 102 { 103 ereport(ERROR, 104 (errmsg("plpy.prepare: type name at ordinal position %d is not a string", i))); 105 sptr = NULL; /* keep compiler quiet */ 106 } 107 108 /******************************************************** 109 * Resolve argument type names and then look them up by 110 * oid in the system cache, and remember the required 111 *information for input conversion. 112 ********************************************************/ 113 114 parseTypeString(sptr, &typeId, &typmod, false); 115 116 Py_DECREF(optr); 117 118 /* 119 * set optr to NULL, so we won't try to unref it again in case of 120 * an error 121 */ 122 optr = NULL; 123 124 plan->types[i] = typeId; 125 PLy_output_setup_func(&plan->args[i], plan->mcxt, 126 typeId, typmod, 127 exec_ctx->curr_proc); 128 } 129 130 pg_verifymbstr(query, strlen(query), false); 131 plan->plan = SPI_prepare(query, plan->nargs, plan->types); 132 if (plan->plan == NULL) 133 elog(ERROR, "SPI_prepare failed: %s", 134 SPI_result_code_string(SPI_result)); 135 136 /* transfer plan from procCxt to topCxt */ 137 if (SPI_keepplan(plan->plan)) 138 elog(ERROR, "SPI_keepplan failed"); 139 140 PLy_spi_subtransaction_commit(oldcontext, oldowner); 141 } 142 PG_CATCH(); 143 { 144 Py_DECREF(plan); 145 Py_XDECREF(optr); 146 147 PLy_spi_subtransaction_abort(oldcontext, oldowner); 148 return NULL; 149 } 150 PG_END_TRY(); 151 152 Assert(plan->plan != NULL); 153 return (PyObject *) plan; 154 } 155 156 /* execute(query="select * from foo", limit=5) 157 * execute(plan=plan, values=(foo, bar), limit=5) 158 */ 159 PyObject * 160 PLy_spi_execute(PyObject *self, PyObject *args) 161 { 162 char *query; 163 PyObject *plan; 164 PyObject *list = NULL; 165 long limit = 0; 166 167 if (PyArg_ParseTuple(args, "s|l", &query, &limit)) 168 return PLy_spi_execute_query(query, limit); 169 170 PyErr_Clear(); 171 172 if (PyArg_ParseTuple(args, "O|Ol", &plan, &list, &limit) && 173 is_PLyPlanObject(plan)) 174 return PLy_spi_execute_plan(plan, list, limit); 175 176 PLy_exception_set(PLy_exc_error, "plpy.execute expected a query or a plan"); 177 return NULL; 178 } 179 180 PyObject * 181 PLy_spi_execute_plan(PyObject *ob, PyObject *list, long limit) 182 { 183 volatile int nargs; 184 int i, 185 rv; 186 PLyPlanObject *plan; 187 volatile MemoryContext oldcontext; 188 volatile ResourceOwner oldowner; 189 PyObject *ret; 190 191 if (list != NULL) 192 { 193 if (!PySequence_Check(list) || PyString_Check(list) || PyUnicode_Check(list)) 194 { 195 PLy_exception_set(PyExc_TypeError, "plpy.execute takes a sequence as its second argument"); 196 return NULL; 197 } 198 nargs = PySequence_Length(list); 199 } 200 else 201 nargs = 0; 202 203 plan = (PLyPlanObject *) ob; 204 205 if (nargs != plan->nargs) 206 { 207 char *sv; 208 PyObject *so = PyObject_Str(list); 209 210 if (!so) 211 PLy_elog(ERROR, "could not execute plan"); 212 sv = PyString_AsString(so); 213 PLy_exception_set_plural(PyExc_TypeError, 214 "Expected sequence of %d argument, got %d: %s", 215 "Expected sequence of %d arguments, got %d: %s", 216 plan->nargs, 217 plan->nargs, nargs, sv); 218 Py_DECREF(so); 219 220 return NULL; 221 } 222 223 oldcontext = CurrentMemoryContext; 224 oldowner = CurrentResourceOwner; 225 226 PLy_spi_subtransaction_begin(oldcontext, oldowner); 227 228 PG_TRY(); 229 { 230 PLyExecutionContext *exec_ctx = PLy_current_execution_context(); 231 char *volatile nulls; 232 volatile int j; 233 234 if (nargs > 0) 235 nulls = palloc(nargs * sizeof(char)); 236 else 237 nulls = NULL; 238 239 for (j = 0; j < nargs; j++) 240 { 241 PLyObToDatum *arg = &plan->args[j]; 242 PyObject *elem; 243 244 elem = PySequence_GetItem(list, j); 245 PG_TRY(); 246 { 247 bool isnull; 248 249 plan->values[j] = PLy_output_convert(arg, elem, &isnull); 250 nulls[j] = isnull ? 'n' : ' '; 251 } 252 PG_CATCH(); 253 { 254 Py_DECREF(elem); 255 PG_RE_THROW(); 256 } 257 PG_END_TRY(); 258 Py_DECREF(elem); 259 } 260 261 rv = SPI_execute_plan(plan->plan, plan->values, nulls, 262 exec_ctx->curr_proc->fn_readonly, limit); 263 ret = PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv); 264 265 if (nargs > 0) 266 pfree(nulls); 267 268 PLy_spi_subtransaction_commit(oldcontext, oldowner); 269 } 270 PG_CATCH(); 271 { 272 int k; 273 274 /* 275 * cleanup plan->values array 276 */ 277 for (k = 0; k < nargs; k++) 278 { 279 if (!plan->args[k].typbyval && 280 (plan->values[k] != PointerGetDatum(NULL))) 281 { 282 pfree(DatumGetPointer(plan->values[k])); 283 plan->values[k] = PointerGetDatum(NULL); 284 } 285 } 286 287 PLy_spi_subtransaction_abort(oldcontext, oldowner); 288 return NULL; 289 } 290 PG_END_TRY(); 291 292 for (i = 0; i < nargs; i++) 293 { 294 if (!plan->args[i].typbyval && 295 (plan->values[i] != PointerGetDatum(NULL))) 296 { 297 pfree(DatumGetPointer(plan->values[i])); 298 plan->values[i] = PointerGetDatum(NULL); 299 } 300 } 301 302 if (rv < 0) 303 { 304 PLy_exception_set(PLy_exc_spi_error, 305 "SPI_execute_plan failed: %s", 306 SPI_result_code_string(rv)); 307 return NULL; 308 } 309 310 return ret; 311 } 312 313 static PyObject * 314 PLy_spi_execute_query(char *query, long limit) 315 { 316 int rv; 317 volatile MemoryContext oldcontext; 318 volatile ResourceOwner oldowner; 319 PyObject *ret = NULL; 320 321 oldcontext = CurrentMemoryContext; 322 oldowner = CurrentResourceOwner; 323 324 PLy_spi_subtransaction_begin(oldcontext, oldowner); 325 326 PG_TRY(); 327 { 328 PLyExecutionContext *exec_ctx = PLy_current_execution_context(); 329 330 pg_verifymbstr(query, strlen(query), false); 331 rv = SPI_execute(query, exec_ctx->curr_proc->fn_readonly, limit); 332 ret = PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv); 333 334 PLy_spi_subtransaction_commit(oldcontext, oldowner); 335 } 336 PG_CATCH(); 337 { 338 PLy_spi_subtransaction_abort(oldcontext, oldowner); 339 return NULL; 340 } 341 PG_END_TRY(); 342 343 if (rv < 0) 344 { 345 Py_XDECREF(ret); 346 PLy_exception_set(PLy_exc_spi_error, 347 "SPI_execute failed: %s", 348 SPI_result_code_string(rv)); 349 return NULL; 350 } 351 352 return ret; 353 } 354 355 static PyObject * 356 PLy_spi_execute_fetch_result(SPITupleTable *tuptable, uint64 rows, int status) 357 { 358 PLyResultObject *result; 359 PLyExecutionContext *exec_ctx = PLy_current_execution_context(); 360 volatile MemoryContext oldcontext; 361 362 result = (PLyResultObject *) PLy_result_new(); 363 if (!result) 364 { 365 SPI_freetuptable(tuptable); 366 return NULL; 367 } 368 Py_DECREF(result->status); 369 result->status = PyInt_FromLong(status); 370 371 if (status > 0 && tuptable == NULL) 372 { 373 Py_DECREF(result->nrows); 374 result->nrows = PyLong_FromUnsignedLongLong(rows); 375 } 376 else if (status > 0 && tuptable != NULL) 377 { 378 PLyDatumToOb ininfo; 379 MemoryContext cxt; 380 381 Py_DECREF(result->nrows); 382 result->nrows = PyLong_FromUnsignedLongLong(rows); 383 384 cxt = AllocSetContextCreate(CurrentMemoryContext, 385 "PL/Python temp context", 386 ALLOCSET_DEFAULT_SIZES); 387 388 /* Initialize for converting result tuples to Python */ 389 PLy_input_setup_func(&ininfo, cxt, RECORDOID, -1, 390 exec_ctx->curr_proc); 391 392 oldcontext = CurrentMemoryContext; 393 PG_TRY(); 394 { 395 MemoryContext oldcontext2; 396 397 if (rows) 398 { 399 uint64 i; 400 401 /* 402 * PyList_New() and PyList_SetItem() use Py_ssize_t for list 403 * size and list indices; so we cannot support a result larger 404 * than PY_SSIZE_T_MAX. 405 */ 406 if (rows > (uint64) PY_SSIZE_T_MAX) 407 ereport(ERROR, 408 (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), 409 errmsg("query result has too many rows to fit in a Python list"))); 410 411 Py_DECREF(result->rows); 412 result->rows = PyList_New(rows); 413 if (result->rows) 414 { 415 PLy_input_setup_tuple(&ininfo, tuptable->tupdesc, 416 exec_ctx->curr_proc); 417 418 for (i = 0; i < rows; i++) 419 { 420 PyObject *row = PLy_input_from_tuple(&ininfo, 421 tuptable->vals[i], 422 tuptable->tupdesc); 423 424 PyList_SetItem(result->rows, i, row); 425 } 426 } 427 } 428 429 /* 430 * Save tuple descriptor for later use by result set metadata 431 * functions. Save it in TopMemoryContext so that it survives 432 * outside of an SPI context. We trust that PLy_result_dealloc() 433 * will clean it up when the time is right. (Do this as late as 434 * possible, to minimize the number of ways the tupdesc could get 435 * leaked due to errors.) 436 */ 437 oldcontext2 = MemoryContextSwitchTo(TopMemoryContext); 438 result->tupdesc = CreateTupleDescCopy(tuptable->tupdesc); 439 MemoryContextSwitchTo(oldcontext2); 440 } 441 PG_CATCH(); 442 { 443 MemoryContextSwitchTo(oldcontext); 444 MemoryContextDelete(cxt); 445 Py_DECREF(result); 446 PG_RE_THROW(); 447 } 448 PG_END_TRY(); 449 450 MemoryContextDelete(cxt); 451 SPI_freetuptable(tuptable); 452 453 /* in case PyList_New() failed above */ 454 if (!result->rows) 455 { 456 Py_DECREF(result); 457 result = NULL; 458 } 459 } 460 461 return (PyObject *) result; 462 } 463 464 /* 465 * Utilities for running SPI functions in subtransactions. 466 * 467 * Usage: 468 * 469 * MemoryContext oldcontext = CurrentMemoryContext; 470 * ResourceOwner oldowner = CurrentResourceOwner; 471 * 472 * PLy_spi_subtransaction_begin(oldcontext, oldowner); 473 * PG_TRY(); 474 * { 475 * <call SPI functions> 476 * PLy_spi_subtransaction_commit(oldcontext, oldowner); 477 * } 478 * PG_CATCH(); 479 * { 480 * <do cleanup> 481 * PLy_spi_subtransaction_abort(oldcontext, oldowner); 482 * return NULL; 483 * } 484 * PG_END_TRY(); 485 * 486 * These utilities take care of restoring connection to the SPI manager and 487 * setting a Python exception in case of an abort. 488 */ 489 void 490 PLy_spi_subtransaction_begin(MemoryContext oldcontext, ResourceOwner oldowner) 491 { 492 BeginInternalSubTransaction(NULL); 493 /* Want to run inside function's memory context */ 494 MemoryContextSwitchTo(oldcontext); 495 } 496 497 void 498 PLy_spi_subtransaction_commit(MemoryContext oldcontext, ResourceOwner oldowner) 499 { 500 /* Commit the inner transaction, return to outer xact context */ 501 ReleaseCurrentSubTransaction(); 502 MemoryContextSwitchTo(oldcontext); 503 CurrentResourceOwner = oldowner; 504 } 505 506 void 507 PLy_spi_subtransaction_abort(MemoryContext oldcontext, ResourceOwner oldowner) 508 { 509 ErrorData *edata; 510 PLyExceptionEntry *entry; 511 PyObject *exc; 512 513 /* Save error info */ 514 MemoryContextSwitchTo(oldcontext); 515 edata = CopyErrorData(); 516 FlushErrorState(); 517 518 /* Abort the inner transaction */ 519 RollbackAndReleaseCurrentSubTransaction(); 520 MemoryContextSwitchTo(oldcontext); 521 CurrentResourceOwner = oldowner; 522 523 /* Look up the correct exception */ 524 entry = hash_search(PLy_spi_exceptions, &(edata->sqlerrcode), 525 HASH_FIND, NULL); 526 527 /* 528 * This could be a custom error code, if that's the case fallback to 529 * SPIError 530 */ 531 exc = entry ? entry->exc : PLy_exc_spi_error; 532 /* Make Python raise the exception */ 533 PLy_spi_exception_set(exc, edata); 534 FreeErrorData(edata); 535 } 536 537 /* 538 * Raise a SPIError, passing in it more error details, like the 539 * internal query and error position. 540 */ 541 static void 542 PLy_spi_exception_set(PyObject *excclass, ErrorData *edata) 543 { 544 PyObject *args = NULL; 545 PyObject *spierror = NULL; 546 PyObject *spidata = NULL; 547 548 args = Py_BuildValue("(s)", edata->message); 549 if (!args) 550 goto failure; 551 552 /* create a new SPI exception with the error message as the parameter */ 553 spierror = PyObject_CallObject(excclass, args); 554 if (!spierror) 555 goto failure; 556 557 spidata = Py_BuildValue("(izzzizzzzz)", edata->sqlerrcode, edata->detail, edata->hint, 558 edata->internalquery, edata->internalpos, 559 edata->schema_name, edata->table_name, edata->column_name, 560 edata->datatype_name, edata->constraint_name); 561 if (!spidata) 562 goto failure; 563 564 if (PyObject_SetAttrString(spierror, "spidata", spidata) == -1) 565 goto failure; 566 567 PyErr_SetObject(excclass, spierror); 568 569 Py_DECREF(args); 570 Py_DECREF(spierror); 571 Py_DECREF(spidata); 572 return; 573 574 failure: 575 Py_XDECREF(args); 576 Py_XDECREF(spierror); 577 Py_XDECREF(spidata); 578 elog(ERROR, "could not convert SPI error to Python exception"); 579 } 580