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 true); 424 425 PyList_SetItem(result->rows, i, row); 426 } 427 } 428 } 429 430 /* 431 * Save tuple descriptor for later use by result set metadata 432 * functions. Save it in TopMemoryContext so that it survives 433 * outside of an SPI context. We trust that PLy_result_dealloc() 434 * will clean it up when the time is right. (Do this as late as 435 * possible, to minimize the number of ways the tupdesc could get 436 * leaked due to errors.) 437 */ 438 oldcontext2 = MemoryContextSwitchTo(TopMemoryContext); 439 result->tupdesc = CreateTupleDescCopy(tuptable->tupdesc); 440 MemoryContextSwitchTo(oldcontext2); 441 } 442 PG_CATCH(); 443 { 444 MemoryContextSwitchTo(oldcontext); 445 MemoryContextDelete(cxt); 446 Py_DECREF(result); 447 PG_RE_THROW(); 448 } 449 PG_END_TRY(); 450 451 MemoryContextDelete(cxt); 452 SPI_freetuptable(tuptable); 453 454 /* in case PyList_New() failed above */ 455 if (!result->rows) 456 { 457 Py_DECREF(result); 458 result = NULL; 459 } 460 } 461 462 return (PyObject *) result; 463 } 464 465 /* 466 * Utilities for running SPI functions in subtransactions. 467 * 468 * Usage: 469 * 470 * MemoryContext oldcontext = CurrentMemoryContext; 471 * ResourceOwner oldowner = CurrentResourceOwner; 472 * 473 * PLy_spi_subtransaction_begin(oldcontext, oldowner); 474 * PG_TRY(); 475 * { 476 * <call SPI functions> 477 * PLy_spi_subtransaction_commit(oldcontext, oldowner); 478 * } 479 * PG_CATCH(); 480 * { 481 * <do cleanup> 482 * PLy_spi_subtransaction_abort(oldcontext, oldowner); 483 * return NULL; 484 * } 485 * PG_END_TRY(); 486 * 487 * These utilities take care of restoring connection to the SPI manager and 488 * setting a Python exception in case of an abort. 489 */ 490 void 491 PLy_spi_subtransaction_begin(MemoryContext oldcontext, ResourceOwner oldowner) 492 { 493 BeginInternalSubTransaction(NULL); 494 /* Want to run inside function's memory context */ 495 MemoryContextSwitchTo(oldcontext); 496 } 497 498 void 499 PLy_spi_subtransaction_commit(MemoryContext oldcontext, ResourceOwner oldowner) 500 { 501 /* Commit the inner transaction, return to outer xact context */ 502 ReleaseCurrentSubTransaction(); 503 MemoryContextSwitchTo(oldcontext); 504 CurrentResourceOwner = oldowner; 505 } 506 507 void 508 PLy_spi_subtransaction_abort(MemoryContext oldcontext, ResourceOwner oldowner) 509 { 510 ErrorData *edata; 511 PLyExceptionEntry *entry; 512 PyObject *exc; 513 514 /* Save error info */ 515 MemoryContextSwitchTo(oldcontext); 516 edata = CopyErrorData(); 517 FlushErrorState(); 518 519 /* Abort the inner transaction */ 520 RollbackAndReleaseCurrentSubTransaction(); 521 MemoryContextSwitchTo(oldcontext); 522 CurrentResourceOwner = oldowner; 523 524 /* Look up the correct exception */ 525 entry = hash_search(PLy_spi_exceptions, &(edata->sqlerrcode), 526 HASH_FIND, NULL); 527 528 /* 529 * This could be a custom error code, if that's the case fallback to 530 * SPIError 531 */ 532 exc = entry ? entry->exc : PLy_exc_spi_error; 533 /* Make Python raise the exception */ 534 PLy_spi_exception_set(exc, edata); 535 FreeErrorData(edata); 536 } 537 538 /* 539 * Raise a SPIError, passing in it more error details, like the 540 * internal query and error position. 541 */ 542 static void 543 PLy_spi_exception_set(PyObject *excclass, ErrorData *edata) 544 { 545 PyObject *args = NULL; 546 PyObject *spierror = NULL; 547 PyObject *spidata = NULL; 548 549 args = Py_BuildValue("(s)", edata->message); 550 if (!args) 551 goto failure; 552 553 /* create a new SPI exception with the error message as the parameter */ 554 spierror = PyObject_CallObject(excclass, args); 555 if (!spierror) 556 goto failure; 557 558 spidata = Py_BuildValue("(izzzizzzzz)", edata->sqlerrcode, edata->detail, edata->hint, 559 edata->internalquery, edata->internalpos, 560 edata->schema_name, edata->table_name, edata->column_name, 561 edata->datatype_name, edata->constraint_name); 562 if (!spidata) 563 goto failure; 564 565 if (PyObject_SetAttrString(spierror, "spidata", spidata) == -1) 566 goto failure; 567 568 PyErr_SetObject(excclass, spierror); 569 570 Py_DECREF(args); 571 Py_DECREF(spierror); 572 Py_DECREF(spidata); 573 return; 574 575 failure: 576 Py_XDECREF(args); 577 Py_XDECREF(spierror); 578 Py_XDECREF(spidata); 579 elog(ERROR, "could not convert SPI error to Python exception"); 580 } 581