[Postgres-xl-developers] DML in plpgsql functions

Roarke Wells roarke at roarkew.com
Tue Aug 26 12:35:43 PDT 2014


Thanks Mason... makes sense given the distributed data.  We may be ok to
open the restriction since we are using a hash partitioning solution that
would limit the data to a single node.


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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20140826/c4567232/attachment.htm>


More information about the Postgres-xl-developers mailing list