Re: [Postgres-xc-bugs] View type cannot be used from within function

[Ashutosh B. <ash...@en...>]

On Wed, Jun 20, 2012 at 10:25 AM, Michael Paquier <mic...@gm...
> wrote:

>
>
> On Wed, Jun 20, 2012 at 12:58 PM, Ashutosh Bapat <
> ash...@en...> wrote:
>
>>
>>
>> On Wed, Jun 20, 2012 at 9:18 AM, Michael Paquier <
>> mic...@gm...> wrote:
>>
>>>
>>>
>>> On Wed, Jun 20, 2012 at 12:46 PM, Ashutosh Bapat <
>>> ash...@en...> wrote:
>>>
>>>> One fix, I can think of is to create volatile functions only on
>>>> coordinator. Although, I would still take a step back, and find out why we
>>>> took the decision not to store views on the datanodes.
>>>>
>>> View => projection of table data => need distribution type of table =>
>>> distribution data only available on Coordinator for data distribution => no
>>> sense to define views on Datanodes
>>>
>>
>> In the case, where a view type is used as function argument or return
>> type, it does make sense to have the view definition on the datanodes. The
>> implication behind my question is whether there is any correctness problem
>> by creating view and related definitions at the datanodes.
>>
> By taking this question from another angle:
> Are there any problems to push down clauses using views to Datanodes?
>

Having view definitions on the datanode does not imply that we have to push
the clauses using views to the datanodes. In fact, even if we want to, we
won't be able to do so, as the view resolution happens even before we take
into consideration the distribution.


> Just based on correctness, the answer is no problem. Btw, the function
> using a view should be volatile as it reads data, so it will not be used on
> Datanodes at all...
>

We are not using view here, we are using datatype which corresponds to the
view result. Using such datatype does not necessarily mean that we touch
any of the data. For example, see the function (modified version of the
example given by Dimitrije) below

CREATE OR REPLACE FUNCTION some_function() RETURNS SETOF some_view AS
$body$
BEGIN
return (1, 1);
END;
$body$
LANGUAGE 'plpgsql'
COST 100;

This function is certainly immutable (certainly not volatile), and thus
pushable to the datanodes. For such functions, it having view definitions
at the datanodes will be helpful.


> --
> Michael Paquier
> http://michael.otacoo.com
>



-- 
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company