Thread: Preventing some SQL commands

In a PL language it's sometimes desirable to prevent execution of some 
commands. I would like to prevent the commands "begin [work or 
transaction]", "commit", and "rollback",  completely and I would like to 
force the user to use explicit methods for the savepoint methods.

I wonder if there's any way to extract the nature of a command from the 
execution plan returned by SPI_prepare. If not, would it be very 
difficult to add? It doesn't feel optimal to add a home brewed parser 
that parses the statements prior to prepare just to find out if I they 
should prevented.

One approach could be to extend the CmdType enum. Perhaps something like 
this:

typedef enum CmdType
{   CMD_UNKNOWN,   CMD_SELECT,                    /* select stmt (formerly retrieve) */   CMD_UPDATE,
/* update stmt (formerly replace) */   CMD_INSERT,                    /* insert stmt (formerly append) */   CMD_DELETE,
 CMD_TRANSACTION,   /* begin, commit, rollback */   CMD_SAVEPOINT,         /* savepoint, rollback to savepoint, release

savepoint */   CMD_UTILITY,                /* cmds like create, destroy, copy,                                * vacuum,
etc.*/   CMD_NOTHING                    /* dummy command for instead nothing 
 
rules                                * with qual */
} CmdType;

and then add a SPI function

CmdType SPI_get_command_type(void* executionPlan)

What do you think? It would certanly help PL/Java add safe and efficient 
savepoint management and the other PL's are likely to share my concerns.

Regards,
Thomas Hallgren

Thomas Hallgren <thhal@mailblocks.com> writes:
> In a PL language it's sometimes desirable to prevent execution of some 
> commands. I would like to prevent the commands "begin [work or 
> transaction]", "commit", and "rollback",  completely and I would like to 
> force the user to use explicit methods for the savepoint methods.

If you are executing through SPI then those operations are disallowed
already.

> I wonder if there's any way to extract the nature of a command from the 
> execution plan returned by SPI_prepare. If not, would it be very 
> difficult to add?

The main problem with this proposal is the erroneous assumption that
there can be only one command in a SPI plan.
        regards, tom lane

On Sun, 2004-11-21 at 16:55 +0100, Thomas Hallgren wrote:
> In a PL language it's sometimes desirable to prevent execution of some
> commands. I would like to prevent the commands "begin [work or
> transaction]", "commit", and "rollback",  completely and I would like to
> force the user to use explicit methods for the savepoint methods.

I implemented my own SPI; not for this purpose, but I could [may] use it
for that.
This, of course, would also allow mapping specific utility commands to
my internal methods; instead of inhibiting them (tho, I don't do this
[yet, perhaps]).

> I wonder if there's any way to extract the nature of a command from the
> execution plan returned by SPI_prepare. If not, would it be very
> difficult to add? It doesn't feel optimal to add a home brewed parser
> that parses the statements prior to prepare just to find out if I they
> should prevented.
[snip]
> CmdType SPI_get_command_type(void* executionPlan)

[I see Tom's reply, but considering I already wrote this; here it is
anyways.]

Hrm, I can't help but think it would be better just to allow
fetching/access to the Node tag, (e.g. T_TransactionStmt) as opposed to
creating a new command type.

NodeTag SPI_get_utility_tag(void *execPlan);

Well, something like that. I suppose it would somehow need to handle
compound queries.

Perhaps a filter operation would be a better idea.
Passing a function pointer like:

bool (*SPI_UtilityFilter) (NodeTag aStmt);
To a "void SPI_FilterUtilities(void *execPlan, SPI_UtilityFilter fp)".

Throwing an error if deemed necessary by the pointed to function.

Although, I'm inclined to think that if you require this sort of
flexibility you should probably think about writing your own SPI.


While a ways from complete/stable, my Python "SPI":
http://gborg.postgresql.org/project/postgrespy/cvs/co.php/imp/src/query.c?r=HEAD
http://gborg.postgresql.org/project/postgrespy/cvs/co.php/imp/src/portal.c?r=HEAD

--
Regards,       James William Pye

James William Pye wrote:

>Although, I'm inclined to think that if you require this sort of
>flexibility you should probably think about writing your own SPI.
>  
>
I think it's far better if we all focus our efforts to improve on the 
PostgreSQL SPI. That way, all PL's will benefit. That's the reason I 
submitted the SPI_is_cursor_plan, SPI_getargtypeid, and SPI_getargcount 
functions.

Regards,
Thomas Hallgren

Tom Lane wrote:

>>... I would like to prevent the commands "begin [work or 
>>transaction]", "commit", and "rollback",  completely
>>
>If you are executing through SPI then those operations are disallowed
>already.
>  
>
Ah, yes I had forgotten that. One problem less to solve.

>The main problem with this proposal is the erroneous assumption that
>there can be only one command in a SPI plan.
>  
>
Ok, so let the function return a list of CmdType's terminated by the 
CMD_UNKNOWN (i assume that's an invalid value). The list should be 
pfree'd by the caller:

CmdType* SPI_get_command_types(void* executionPlan)

how about that?

Regards,
Thomas Hallgren

James William Pye

>bool (*SPI_UtilityFilter) (NodeTag aStmt);
>To a "void SPI_FilterUtilities(void *execPlan, SPI_UtilityFilter fp)".
>
>Throwing an error if deemed necessary by the pointed to function.
>  
>
After browsing the code a bit more, I realize that the above suggestion 
is superior to my own. It doesn't require a memory allocation and it's 
very close to what I'd like to have. I'd like it a bit more generic to 
allow arbitrary perusal of Query attributes. Like this to be more precise:

typedef bool (*QueryVisitor)(Query* query, void *clientData);

bool
SPI_traverse_query_roots(void *plan, QueryVisitor queryVisitor, void* 
clientData)
{   List     *query_list_list = ((_SPI_plan*)plan)->qtlist;   ListCell *query_list_list_item;
   foreach(query_list_list_item, query_list_list)   {       List     *query_list = lfirst(query_list_list_item);
ListCell*query_list_item;
 
       foreach(query_list_item, query_list)       {           if(!queryVisitor((Query *)lfirst(query_list_item),
clientData))              return false;       }   }   return true;
 
}

This will allow me to implement a QueryVisitor like so:

static bool detectTransactCommands(Query* query, void* clientData)
{   return !(query->commandType == CMD_UTILITY &&           IsA(query->utilityStmt, TransactionStmt));
}

and then test using:

if(!SPI_traverse_query_roots(myPlan, detectTransactCommands, null))   /* Handle error here */

Any chance a patch containing the SPI_traverse_query_roots would be 
accepted?

Regards,
Thomas Hallgren