[Postgres-xl-developers] DML in plpgsql functions

Roarke Wells roarke at roarkew.com
Tue Sep 9 07:46:03 PDT 2014

I have discovered the current limitation with execution of functions in
Postgres-XL - i.e. that they are pushed to a datanode for execution.  Is
there any workaround for this as it severely limits the functionality of
the product.  We have a lot of our business logic in functions and the
inability to reliably execute queries, cursors, etc. because they will only
return data local to the datanode is pretty much a show stopper for us.  I
did run a quick test where I used an EXECUTE to run an EXECUTE DIRECT on
the coordinator and that seems to have worked, but that would mean all of
the SQL statements within a function would have to be forced via EXECUTE
DIRECT back to a given coordinator to return the correct results.


On Tue, Aug 26, 2014 at 2:01 PM, Mason Sharp <msharp at translattice.com>

> On Tue, Aug 26, 2014 at 7:07 AM, Roarke Wells <roarke at roarkew.com> wrote:
>> All,
>> Ran into the error regarding DML in plpgsql functions: "Postgres-XL does
>> not support DML queries in PL/pgSQL".   I've seen the workaround using
>> EXECUTE and was able to convert some initial code over to use the EXECUTE
>> solution.
>> I am trying to get an understanding of the issue regarding DML in
>> functions.  When testing what is allowed and what isn't allowed it appears
>> that I am able to implement a DML command (insert) inside a function as
>> long as I am not in a control loop - i.e. FOR loop.  Inside the FOR loop I
>> have to use EXECUTE statements.
>> Can anyone provide some background on the DML in function issue and is
>> there any other workaround?  I'm not anxious to rewrite several thousand
>> line queries to use EXECUTEs if there is another solution.
> The main issue here is, a function could execute on a datanode that tries
> to update data and makes the assumption that it has a full view of all data
> across all nodes, whereas it really is just updating data on that one node.
> Such a function would be safe if doing a "SELECT myfunc();", the
> Coordinator would do the right thing. If the function is run via something
> like "SELECT ... FROM mytable WHERE col1 = unsafe_volatile_func()"
> unexpected results may occur.
> This is going to be addressed in the near future.  In the meantime, if you
> and your development team understand the caveats and don't break
> distribution rules, you could change the code to unblock this functionality.
> Regards,
> Mason
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20140909/db9e542e/attachment.htm>

More information about the Postgres-xl-developers mailing list