[PATCHES] SPI function to investigate query semantics
Here's a patch containing the function SPI_iterate_query_roots(...). I'm
optimistic so it's complete with documentation :-)
I think that this function is needed so that PL/lang authors like
myself have a way to investigate the semantics of a prepared query. For
me this is essential since I want to prevent that savepoint related
statements are executed using normal SQL so that I can enforce the use
of the methods stipulated by the connection interface.
I forsee that this might be of interest for other PL/lang authors as
well. With this patch in place, it will be possible to do things like
this (returning false is rejecting in this case since false terminates
the iteration):
static bool rejectTransactionCommand(Query* query, void* clientData)
{
return !(query-commandType == CMD_UTILITY
IsA(query-utilityStmt, TransactionStmt));
}
and then use that like:
result = !SPI_iterate_query_roots(ePlan, rejectTransactionCommand,
NULL);
The patch has no side effects since it's a pure addon.
Kind regards,
Thomas Hallgren
Index: doc/src/sgml/spi.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/spi.sgml,v
retrieving revision 1.35
diff -u -r1.35 spi.sgml
--- doc/src/sgml/spi.sgml 13 Sep 2004 20:05:25 - 1.35
+++ doc/src/sgml/spi.sgml 1 Dec 2004 19:05:28 -
@@ -1305,6 +1305,82 @@
!-- *** --
+refentry id=spi-spi-iterate-query-roots
+ refmeta
+ refentrytitleSPI_iterate_query_roots/refentrytitle
+ /refmeta
+
+ refnamediv
+ refnameSPI_iterate_query_roots/refname
+ refpurposeinvestigate the semantics of a query/refpurpose
+ /refnamediv
+
+ indextermprimarySPI_iterate_query_roots/primary/indexterm
+
+ refsynopsisdiv
+synopsis
+bool SPI_iterate_query_roots(void * parameterplan/parameter, QueryVisitor
parametercallback/parameter, void * parameterclientData/parameter)
+/synopsis
+ /refsynopsisdiv
+
+ refsect1
+ titleDescription/title
+
+ para
+The functionSPI_iterate_query_roots/function will invoke the
+symbolqueryVisitor/symbol callback once for each top level
+symbolQuery/symbol found in the supplied execution plan.
+The iteration is cancelled when a callback returns symbolfalse/symbol.
+If no callback returns symbolfalse/symbol, or if the plan is
+symbolNULL/symbol, the function returns symboltrue/symbol.
+ /para
+ /refsect1
+
+ refsect1
+ titleArguments/title
+
+ variablelist
+ varlistentry
+termliteralvoid * parameterplan/parameter/literal/term
+listitem
+ para
+ execution plan (returned by functionSPI_prepare/function)
+ /para
+/listitem
+ /varlistentry
+
+ varlistentry
+termliteralQueryVisitor
parametercallback/parameter/literal/term
+listitem
+ para
+ the callback to invoke for each query found
+ /para
+/listitem
+ /varlistentry
+
+ varlistentry
+termliteralvoid * parameterclientData/parameter/literal/term
+listitem
+ para
+ user defined data that will be passed on to the callback
+ /para
+/listitem
+ /varlistentry
+ /variablelist
+ /refsect1
+
+ refsect1
+ titleReturn Value/title
+ para
+symboltrue/symbol when all callbacks returned symboltrue/symbol or
+symbolfalse/symbol when a callback returned symbolfalse/symbol and
+thus terminated the iteration.
+ /para
+ /refsect1
+/refentry
+
+!-- *** --
+
refentry id=spi-spi-cursor-find
refmeta
refentrytitleSPI_cursor_find/refentrytitle
Index: src/backend/executor/spi.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/executor/spi.c,v
retrieving revision 1.132
diff -u -r1.132 spi.c
--- src/backend/executor/spi.c 16 Nov 2004 18:10:13 - 1.132
+++ src/backend/executor/spi.c 1 Dec 2004 19:05:29 -
@@ -1064,6 +1064,42 @@
return false;
}
+/**
+ * Invokes the queryVisitor callback for each top level Query found in an
+ * execution plan. The iteration is cancelled when a callback returns
+ * false. If no callbacks returns false, or if the plan is NULL, this
+ * function returns true.
+ *
+ * Arguments:
+ * plan An ExecutionPlan created by SPI_prepare
+ * queryVisitor The callback function
+ * clientDataUser defined data that will be passed on to the callback
+ * Returns: true if the plan is NULL or if all callback invocation returns true
+ */
+bool
+SPI_iterate_query_roots(void *plan, QueryVisitor queryVisitor, void
*clientData)
+{
+ _SPI_plan *spiplan = (_SPI_plan *) plan;
+
+ if (spiplan != NULL)
+ {
+ ListCell *query_list_list_item;
+ List *query_list_list = spiplan-qtlist;
+ foreach(query_list_list_item, query_list_list)
+ {
+ List *query_list = lfirst(query_list_list_item);
+ ListCell
Re: [PATCHES] SPI function to investigate query semantics
Tom Lane wrote: We haven't got one that will work from inside arbitrary functions --- DefineSavepoint and friends don't get it done by themselves, but expect you to call CommitTransactionCommand/StartTransactionCommand, and those functions tend to pull the rug out from under the executor. (I seem to recall trying to do it that way in the first attempt on plpgsql, and running into all kinds of memory management issues.) The existing PLs use BeginInternalSubTransaction, ReleaseCurrentSubTransaction, RollbackAndReleaseCurrentSubTransaction, but these are subset implementations only suited for exception-block-structured code. Thanks. I'll check that out and try to figure out how to use it. What are the future plans? For me it would work really well with something like Savepoint SPI_savepoint(const char* name); void SPI_releaseSavepoint(Savepoint sp); void SPI_rollbackSavepoint(Savepoint sp); The Savepoint structure could then hold information about call level etc. needed to ensure proper behaviour when nesting. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] SPI function to investigate query semantics
Thomas Hallgren [EMAIL PROTECTED] writes: What are the future plans? I haven't got any at the moment ;-). It would make sense to think about extending the SPI API along the lines you suggest, but I really am not clear on the implications. Right at the moment I'm focused on trying to push 8.0 out the door ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] SPI function to investigate query semantics
Thomas Hallgren [EMAIL PROTECTED] writes: I think that this function is needed so that PL/lang authors like myself have a way to investigate the semantics of a prepared query. Which you will do what with? I'm not sure I see the point of treating _SPI_plan as an opaque type while assuming you know what to do with a Query. For me this is essential since I want to prevent that savepoint related statements are executed using normal SQL so that I can enforce the use of the methods stipulated by the connection interface. You do realize that SPI_execute will reject TransactionStmt anyway? The example is therefore not very compelling ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] SPI function to investigate query semantics
Tom Lane wrote: Looks pretty rejectish to me... regards, tom lane Arrghh. Forget my patch. It's not possible to set savepoints at all using SPI! Here I was, thinking that only begin/commit/rollback was rejected (I trusted the documentation and did not dive into the code). A patch is needed for the documentation to clarify this :-) So what *is* the appropriate way of starting, releasing, and rolling back savepoints then? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] SPI function to investigate query semantics
Tom Lane wrote: You do realize that SPI_execute will reject TransactionStmt anyway? The example is therefore not very compelling ... It won't reject savepoint related statements and that's what the example is for. I want savepoints rejected unless they go through a specific method found on my connection object. From current discussions it seems similar functionality might be needed for other PL's. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] SPI function to investigate query semantics
Thomas Hallgren [EMAIL PROTECTED] writes:
Tom Lane wrote:
You do realize that SPI_execute will reject TransactionStmt anyway?
The example is therefore not very compelling ...
It won't reject savepoint related statements and that's what the example
is for.
Really?
if (queryTree-commandType == CMD_UTILITY)
{
...
else if (IsA(queryTree-utilityStmt, TransactionStmt))
{
res = SPI_ERROR_TRANSACTION;
goto fail;
}
}
Looks pretty rejectish to me...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] SPI function to investigate query semantics
Tom Lane wrote: Which you will do what with? I'm not sure I see the point of treating _SPI_plan as an opaque type while assuming you know what to do with a Query. What's different in that compared to the methods that use a Snapshot? The fact that I provided documentation? If so, ok remove the docs. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] SPI function to investigate query semantics
Thomas Hallgren [EMAIL PROTECTED] writes: So what *is* the appropriate way of starting, releasing, and rolling back savepoints then? We haven't got one that will work from inside arbitrary functions --- DefineSavepoint and friends don't get it done by themselves, but expect you to call CommitTransactionCommand/StartTransactionCommand, and those functions tend to pull the rug out from under the executor. (I seem to recall trying to do it that way in the first attempt on plpgsql, and running into all kinds of memory management issues.) The existing PLs use BeginInternalSubTransaction, ReleaseCurrentSubTransaction, RollbackAndReleaseCurrentSubTransaction, but these are subset implementations only suited for exception-block-structured code. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
