2 * interface to SPI functions
4 * src/pl/plpython/plpy_spi.c
11 #include "access/xact.h"
12 #include "catalog/pg_type.h"
13 #include "executor/spi.h"
14 #include "mb/pg_wchar.h"
15 #include "parser/parse_type.h"
16 #include "plpy_elog.h"
17 #include "plpy_main.h"
18 #include "plpy_planobject.h"
19 #include "plpy_plpymodule.h"
20 #include "plpy_resultobject.h"
23 #include "utils/memutils.h"
25 static PyObject
*PLy_spi_execute_query(char *query
, long limit
);
26 static PyObject
*PLy_spi_execute_fetch_result(SPITupleTable
*tuptable
,
27 uint64 rows
, int status
);
28 static void PLy_spi_exception_set(PyObject
*excclass
, ErrorData
*edata
);
31 /* prepare(query="select * from foo")
32 * prepare(query="select * from foo where bar = $1", params=["text"])
33 * prepare(query="select * from foo where bar = $1", params=["text"], limit=5)
36 PLy_spi_prepare(PyObject
*self
, PyObject
*args
)
39 PyObject
*list
= NULL
;
40 PyObject
*volatile optr
= NULL
;
42 PLyExecutionContext
*exec_ctx
= PLy_current_execution_context();
43 volatile MemoryContext oldcontext
;
44 volatile ResourceOwner oldowner
;
47 if (!PyArg_ParseTuple(args
, "s|O:prepare", &query
, &list
))
50 if (list
&& (!PySequence_Check(list
)))
52 PLy_exception_set(PyExc_TypeError
,
53 "second argument of plpy.prepare must be a sequence");
57 if ((plan
= (PLyPlanObject
*) PLy_plan_new()) == NULL
)
60 plan
->mcxt
= AllocSetContextCreate(TopMemoryContext
,
61 "PL/Python plan context",
62 ALLOCSET_DEFAULT_SIZES
);
63 oldcontext
= MemoryContextSwitchTo(plan
->mcxt
);
65 nargs
= list
? PySequence_Length(list
) : 0;
68 plan
->types
= nargs
? palloc0(sizeof(Oid
) * nargs
) : NULL
;
69 plan
->values
= nargs
? palloc0(sizeof(Datum
) * nargs
) : NULL
;
70 plan
->args
= nargs
? palloc0(sizeof(PLyObToDatum
) * nargs
) : NULL
;
72 MemoryContextSwitchTo(oldcontext
);
74 oldcontext
= CurrentMemoryContext
;
75 oldowner
= CurrentResourceOwner
;
77 PLy_spi_subtransaction_begin(oldcontext
, oldowner
);
83 for (i
= 0; i
< nargs
; i
++)
89 optr
= PySequence_GetItem(list
, i
);
90 if (PyUnicode_Check(optr
))
91 sptr
= PLyUnicode_AsString(optr
);
95 (errmsg("plpy.prepare: type name at ordinal position %d is not a string", i
)));
96 sptr
= NULL
; /* keep compiler quiet */
99 /********************************************************
100 * Resolve argument type names and then look them up by
101 * oid in the system cache, and remember the required
102 *information for input conversion.
103 ********************************************************/
105 (void) parseTypeString(sptr
, &typeId
, &typmod
, NULL
);
110 * set optr to NULL, so we won't try to unref it again in case of
115 plan
->types
[i
] = typeId
;
116 PLy_output_setup_func(&plan
->args
[i
], plan
->mcxt
,
118 exec_ctx
->curr_proc
);
121 pg_verifymbstr(query
, strlen(query
), false);
122 plan
->plan
= SPI_prepare(query
, plan
->nargs
, plan
->types
);
123 if (plan
->plan
== NULL
)
124 elog(ERROR
, "SPI_prepare failed: %s",
125 SPI_result_code_string(SPI_result
));
127 /* transfer plan from procCxt to topCxt */
128 if (SPI_keepplan(plan
->plan
))
129 elog(ERROR
, "SPI_keepplan failed");
131 PLy_spi_subtransaction_commit(oldcontext
, oldowner
);
138 PLy_spi_subtransaction_abort(oldcontext
, oldowner
);
143 Assert(plan
->plan
!= NULL
);
144 return (PyObject
*) plan
;
147 /* execute(query="select * from foo", limit=5)
148 * execute(plan=plan, values=(foo, bar), limit=5)
151 PLy_spi_execute(PyObject
*self
, PyObject
*args
)
155 PyObject
*list
= NULL
;
158 if (PyArg_ParseTuple(args
, "s|l", &query
, &limit
))
159 return PLy_spi_execute_query(query
, limit
);
163 if (PyArg_ParseTuple(args
, "O|Ol", &plan
, &list
, &limit
) &&
164 is_PLyPlanObject(plan
))
165 return PLy_spi_execute_plan(plan
, list
, limit
);
167 PLy_exception_set(PLy_exc_error
, "plpy.execute expected a query or a plan");
172 PLy_spi_execute_plan(PyObject
*ob
, PyObject
*list
, long limit
)
178 volatile MemoryContext oldcontext
;
179 volatile ResourceOwner oldowner
;
184 if (!PySequence_Check(list
) || PyUnicode_Check(list
))
186 PLy_exception_set(PyExc_TypeError
, "plpy.execute takes a sequence as its second argument");
189 nargs
= PySequence_Length(list
);
194 plan
= (PLyPlanObject
*) ob
;
196 if (nargs
!= plan
->nargs
)
199 PyObject
*so
= PyObject_Str(list
);
202 PLy_elog(ERROR
, "could not execute plan");
203 sv
= PLyUnicode_AsString(so
);
204 PLy_exception_set_plural(PyExc_TypeError
,
205 "Expected sequence of %d argument, got %d: %s",
206 "Expected sequence of %d arguments, got %d: %s",
208 plan
->nargs
, nargs
, sv
);
214 oldcontext
= CurrentMemoryContext
;
215 oldowner
= CurrentResourceOwner
;
217 PLy_spi_subtransaction_begin(oldcontext
, oldowner
);
221 PLyExecutionContext
*exec_ctx
= PLy_current_execution_context();
222 char *volatile nulls
;
226 nulls
= palloc(nargs
* sizeof(char));
230 for (j
= 0; j
< nargs
; j
++)
232 PLyObToDatum
*arg
= &plan
->args
[j
];
235 elem
= PySequence_GetItem(list
, j
);
240 plan
->values
[j
] = PLy_output_convert(arg
, elem
, &isnull
);
241 nulls
[j
] = isnull
? 'n' : ' ';
250 rv
= SPI_execute_plan(plan
->plan
, plan
->values
, nulls
,
251 exec_ctx
->curr_proc
->fn_readonly
, limit
);
252 ret
= PLy_spi_execute_fetch_result(SPI_tuptable
, SPI_processed
, rv
);
257 PLy_spi_subtransaction_commit(oldcontext
, oldowner
);
264 * cleanup plan->values array
266 for (k
= 0; k
< nargs
; k
++)
268 if (!plan
->args
[k
].typbyval
&&
269 (plan
->values
[k
] != PointerGetDatum(NULL
)))
271 pfree(DatumGetPointer(plan
->values
[k
]));
272 plan
->values
[k
] = PointerGetDatum(NULL
);
276 PLy_spi_subtransaction_abort(oldcontext
, oldowner
);
281 for (i
= 0; i
< nargs
; i
++)
283 if (!plan
->args
[i
].typbyval
&&
284 (plan
->values
[i
] != PointerGetDatum(NULL
)))
286 pfree(DatumGetPointer(plan
->values
[i
]));
287 plan
->values
[i
] = PointerGetDatum(NULL
);
293 PLy_exception_set(PLy_exc_spi_error
,
294 "SPI_execute_plan failed: %s",
295 SPI_result_code_string(rv
));
303 PLy_spi_execute_query(char *query
, long limit
)
306 volatile MemoryContext oldcontext
;
307 volatile ResourceOwner oldowner
;
308 PyObject
*ret
= NULL
;
310 oldcontext
= CurrentMemoryContext
;
311 oldowner
= CurrentResourceOwner
;
313 PLy_spi_subtransaction_begin(oldcontext
, oldowner
);
317 PLyExecutionContext
*exec_ctx
= PLy_current_execution_context();
319 pg_verifymbstr(query
, strlen(query
), false);
320 rv
= SPI_execute(query
, exec_ctx
->curr_proc
->fn_readonly
, limit
);
321 ret
= PLy_spi_execute_fetch_result(SPI_tuptable
, SPI_processed
, rv
);
323 PLy_spi_subtransaction_commit(oldcontext
, oldowner
);
327 PLy_spi_subtransaction_abort(oldcontext
, oldowner
);
335 PLy_exception_set(PLy_exc_spi_error
,
336 "SPI_execute failed: %s",
337 SPI_result_code_string(rv
));
345 PLy_spi_execute_fetch_result(SPITupleTable
*tuptable
, uint64 rows
, int status
)
347 PLyResultObject
*result
;
348 PLyExecutionContext
*exec_ctx
= PLy_current_execution_context();
349 volatile MemoryContext oldcontext
;
351 result
= (PLyResultObject
*) PLy_result_new();
354 SPI_freetuptable(tuptable
);
357 Py_DECREF(result
->status
);
358 result
->status
= PyLong_FromLong(status
);
360 if (status
> 0 && tuptable
== NULL
)
362 Py_DECREF(result
->nrows
);
363 result
->nrows
= PyLong_FromUnsignedLongLong(rows
);
365 else if (status
> 0 && tuptable
!= NULL
)
370 Py_DECREF(result
->nrows
);
371 result
->nrows
= PyLong_FromUnsignedLongLong(rows
);
373 cxt
= AllocSetContextCreate(CurrentMemoryContext
,
374 "PL/Python temp context",
375 ALLOCSET_DEFAULT_SIZES
);
377 /* Initialize for converting result tuples to Python */
378 PLy_input_setup_func(&ininfo
, cxt
, RECORDOID
, -1,
379 exec_ctx
->curr_proc
);
381 oldcontext
= CurrentMemoryContext
;
384 MemoryContext oldcontext2
;
391 * PyList_New() and PyList_SetItem() use Py_ssize_t for list
392 * size and list indices; so we cannot support a result larger
393 * than PY_SSIZE_T_MAX.
395 if (rows
> (uint64
) PY_SSIZE_T_MAX
)
397 (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED
),
398 errmsg("query result has too many rows to fit in a Python list")));
400 Py_DECREF(result
->rows
);
401 result
->rows
= PyList_New(rows
);
404 PLy_input_setup_tuple(&ininfo
, tuptable
->tupdesc
,
405 exec_ctx
->curr_proc
);
407 for (i
= 0; i
< rows
; i
++)
409 PyObject
*row
= PLy_input_from_tuple(&ininfo
,
414 PyList_SetItem(result
->rows
, i
, row
);
420 * Save tuple descriptor for later use by result set metadata
421 * functions. Save it in TopMemoryContext so that it survives
422 * outside of an SPI context. We trust that PLy_result_dealloc()
423 * will clean it up when the time is right. (Do this as late as
424 * possible, to minimize the number of ways the tupdesc could get
425 * leaked due to errors.)
427 oldcontext2
= MemoryContextSwitchTo(TopMemoryContext
);
428 result
->tupdesc
= CreateTupleDescCopy(tuptable
->tupdesc
);
429 MemoryContextSwitchTo(oldcontext2
);
433 MemoryContextSwitchTo(oldcontext
);
434 MemoryContextDelete(cxt
);
440 MemoryContextDelete(cxt
);
441 SPI_freetuptable(tuptable
);
443 /* in case PyList_New() failed above */
451 return (PyObject
*) result
;
455 PLy_commit(PyObject
*self
, PyObject
*args
)
457 MemoryContext oldcontext
= CurrentMemoryContext
;
458 PLyExecutionContext
*exec_ctx
= PLy_current_execution_context();
464 /* was cleared at transaction end, reset pointer */
465 exec_ctx
->scratch_ctx
= NULL
;
470 PLyExceptionEntry
*entry
;
473 /* Save error info */
474 MemoryContextSwitchTo(oldcontext
);
475 edata
= CopyErrorData();
478 /* was cleared at transaction end, reset pointer */
479 exec_ctx
->scratch_ctx
= NULL
;
481 /* Look up the correct exception */
482 entry
= hash_search(PLy_spi_exceptions
, &(edata
->sqlerrcode
),
486 * This could be a custom error code, if that's the case fallback to
489 exc
= entry
? entry
->exc
: PLy_exc_spi_error
;
490 /* Make Python raise the exception */
491 PLy_spi_exception_set(exc
, edata
);
492 FreeErrorData(edata
);
502 PLy_rollback(PyObject
*self
, PyObject
*args
)
504 MemoryContext oldcontext
= CurrentMemoryContext
;
505 PLyExecutionContext
*exec_ctx
= PLy_current_execution_context();
511 /* was cleared at transaction end, reset pointer */
512 exec_ctx
->scratch_ctx
= NULL
;
517 PLyExceptionEntry
*entry
;
520 /* Save error info */
521 MemoryContextSwitchTo(oldcontext
);
522 edata
= CopyErrorData();
525 /* was cleared at transaction end, reset pointer */
526 exec_ctx
->scratch_ctx
= NULL
;
528 /* Look up the correct exception */
529 entry
= hash_search(PLy_spi_exceptions
, &(edata
->sqlerrcode
),
533 * This could be a custom error code, if that's the case fallback to
536 exc
= entry
? entry
->exc
: PLy_exc_spi_error
;
537 /* Make Python raise the exception */
538 PLy_spi_exception_set(exc
, edata
);
539 FreeErrorData(edata
);
549 * Utilities for running SPI functions in subtransactions.
553 * MemoryContext oldcontext = CurrentMemoryContext;
554 * ResourceOwner oldowner = CurrentResourceOwner;
556 * PLy_spi_subtransaction_begin(oldcontext, oldowner);
559 * <call SPI functions>
560 * PLy_spi_subtransaction_commit(oldcontext, oldowner);
565 * PLy_spi_subtransaction_abort(oldcontext, oldowner);
570 * These utilities take care of restoring connection to the SPI manager and
571 * setting a Python exception in case of an abort.
574 PLy_spi_subtransaction_begin(MemoryContext oldcontext
, ResourceOwner oldowner
)
576 BeginInternalSubTransaction(NULL
);
577 /* Want to run inside function's memory context */
578 MemoryContextSwitchTo(oldcontext
);
582 PLy_spi_subtransaction_commit(MemoryContext oldcontext
, ResourceOwner oldowner
)
584 /* Commit the inner transaction, return to outer xact context */
585 ReleaseCurrentSubTransaction();
586 MemoryContextSwitchTo(oldcontext
);
587 CurrentResourceOwner
= oldowner
;
591 PLy_spi_subtransaction_abort(MemoryContext oldcontext
, ResourceOwner oldowner
)
594 PLyExceptionEntry
*entry
;
597 /* Save error info */
598 MemoryContextSwitchTo(oldcontext
);
599 edata
= CopyErrorData();
602 /* Abort the inner transaction */
603 RollbackAndReleaseCurrentSubTransaction();
604 MemoryContextSwitchTo(oldcontext
);
605 CurrentResourceOwner
= oldowner
;
607 /* Look up the correct exception */
608 entry
= hash_search(PLy_spi_exceptions
, &(edata
->sqlerrcode
),
612 * This could be a custom error code, if that's the case fallback to
615 exc
= entry
? entry
->exc
: PLy_exc_spi_error
;
616 /* Make Python raise the exception */
617 PLy_spi_exception_set(exc
, edata
);
618 FreeErrorData(edata
);
622 * Raise a SPIError, passing in it more error details, like the
623 * internal query and error position.
626 PLy_spi_exception_set(PyObject
*excclass
, ErrorData
*edata
)
628 PyObject
*args
= NULL
;
629 PyObject
*spierror
= NULL
;
630 PyObject
*spidata
= NULL
;
632 args
= Py_BuildValue("(s)", edata
->message
);
636 /* create a new SPI exception with the error message as the parameter */
637 spierror
= PyObject_CallObject(excclass
, args
);
641 spidata
= Py_BuildValue("(izzzizzzzz)", edata
->sqlerrcode
, edata
->detail
, edata
->hint
,
642 edata
->internalquery
, edata
->internalpos
,
643 edata
->schema_name
, edata
->table_name
, edata
->column_name
,
644 edata
->datatype_name
, edata
->constraint_name
);
648 if (PyObject_SetAttrString(spierror
, "spidata", spidata
) == -1)
651 PyErr_SetObject(excclass
, spierror
);
660 Py_XDECREF(spierror
);
662 elog(ERROR
, "could not convert SPI error to Python exception");