[Postgres-xl-developers] DML in plpgsql functions

Mason Sharp msharp at translattice.com
Tue Sep 9 15:43:25 PDT 2014

On Tue, Sep 9, 2014 at 10:46 AM, Roarke Wells <roarke at roarkew.com> wrote:

> Mason,
> 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.

Yes, it is currently a limitation. Were you able to run top level functions
like "SELECT myfunc();" ok?

>  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.

I understand you would want to avoid that.

We may be able to do something to indicate when functions are "local safe"
or when they need to have execution go to the coordinator. This would be a
little different than volatile, stable and immutable functions, where one
could still allow execution of volatile functions on a node.

Anyway, some level of effort is required here.

> Thanks
> On Tue, Aug 26, 2014 at 2:01 PM, Mason Sharp <msharp at translattice.com>
> wrote:
>> 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

Mason Sharp

TransLattice - http://www.translattice.com
TransLattice Elastic Database (TED)
Postgres-XL Support
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20140909/78869bc2/attachment.htm>

More information about the Postgres-xl-developers mailing list