[Postgres-xl-developers] DML in plpgsql functions
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>
> On Tue, Aug 26, 2014 at 7:07 AM, Roarke Wells <roarke at roarkew.com> wrote:
>> 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
>> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Postgres-xl-developers