[Postgres-xl-developers] DML in plpgsql functions

Roarke Wells roarke at roarkew.com
Fri Sep 12 05:11:32 PDT 2014


Thanks for quick follow up.  Another in the continuing flood of
questions....
It appears that the information_schema is only available at the coordinator
level - i.e. this executes and returns the correct table data:

execute direct on (coord1) 'select * from information_schema.columns'

However, the following throws a "sql_identifier does not exist" error

execute direct on (node1) 'select * from information_schema.columns'

Does the information_schema only exist on the coordinator with no data
maintained on a node (even for tables that are partitioned to a specific
node)?

Thanks again for all of your assistance


On Tue, Sep 9, 2014 at 5:43 PM, Mason Sharp <msharp at translattice.com> wrote:

>
>
> 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/20140912/69b8c147/attachment.htm>


More information about the Postgres-xl-developers mailing list