[Postgres-xl-developers] DML in plpgsql functions

Koichi Suzuki koichi.dbms at gmail.com
Sat Sep 13 16:07:18 PDT 2014


Hope this meets the previous discussion on this.

To allow more than one DML in plpgsql function, XL (and originating
XC) needs to support the savepoint.    We need to work a bit for this.
  Root transaction needs to keep track of active savepoints to ship
them to new remote transactions.   This is the reason why XL (and XC)
allows just one DML in the plpgsql.

Regards;
---
Koichi Suzuki


2014-09-12 21:11 GMT+09:00 Roarke Wells <roarke at roarkew.com>:
> 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
>>
>
>
> ------------------------------------------------------------------------------
> Want excitement?
> Manually upgrade your production database.
> When you want reliability, choose Perforce
> Perforce version control. Predictably reliable.
> http://pubads.g.doubleclick.net/gampad/clk?id=157508191&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xl-developers mailing list
> Postgres-xl-developers at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/postgres-xl-developers
>




More information about the Postgres-xl-developers mailing list