[Postgres-xl-developers] DML in plpgsql functions

Mason Sharp msharp at translattice.com
Tue Aug 26 12:01:05 PDT 2014

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.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20140826/e14b327d/attachment.htm>

More information about the Postgres-xl-developers mailing list