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

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

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.


> Am I missing smth?
>
>
>>
>> On Wed, Jun 20, 2012 at 6:10 AM, Michael Paquier <
>> mic...@gm...> wrote:
>>
>>>
>>>
>>> On Tue, Jun 19, 2012 at 4:41 PM, Dimitrije Radojevic <
>>> tem...@gm...> wrote:
>>>
>>>> Hi,
>>>>
>>>> I have encountered a problem when using view types within a function. I
>>>> read the documentation on VIEW to check if this functionality is not yet
>>>> implemented in Postgres-XC, but I couldn't find it, so I'm guessing this is
>>>> supposed to be a bug report (though I am not sure, this may just be a
>>>> missing feature). Here's my test case:
>>>>
>>>> 1) create a table
>>>> CREATE TABLE test_table (
>>>> id SERIAL NOT NULL,
>>>> other_id int4 NOT NULL,
>>>> PRIMARY KEY("id") )
>>>> DISTRIBUTE BY REPLICATION;
>>>>
>>>> 2) create a view
>>>>
>>>>
>>>> 3) create a function using the view
>>>> CREATE OR REPLACE FUNCTION some_function() RETURNS SETOF some_view AS
>>>> $body$
>>>> BEGIN
>>>> RETURN QUERY SELECT * FROM some_view;
>>>> END;
>>>> $body$
>>>> LANGUAGE 'plpgsql'
>>>> VOLATILE
>>>> CALLED ON NULL INPUT
>>>> SECURITY INVOKER
>>>> COST 100;
>>>>
>>>> I get the following error:
>>>> ERROR:  type "some_view" does not exist
>>>>
>>>> This runs fine in postgres 9.1.4.
>>>>
>>> A view is only created on Coordinators. It is basically a projection of
>>> the data that is located on remote Datanodes, so selecting data from a view
>>> means that you need to fallback to a given table (or tables for a join),
>>> and to analyse the distribution type of those tables before fetching the
>>> data from Datanodes. So it doesn't really make sense to define view on
>>> Datanodes.
>>> When creating a function, we create it on all the nodes, resulting in
>>> the error you see as the process fails at the function creation.
>>>
>>> This indeed looks like a bug, and based on the definition XC has about
>>> views, it would make sense to create such functions only on Coordinators.
>>> As you are giving a test case so it will be easier to fix this
>>> particular function creation.
>>> --
>>> Michael Paquier
>>> http://michael.otacoo.com
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Live Security Virtual Conference
>>> Exclusive live event will cover all the ways today's security and
>>> threat landscape has changed and how IT managers can respond. Discussions
>>> will include endpoint security, mobile security and the latest in malware
>>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>>> _______________________________________________
>>> Postgres-xc-bugs mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>>>
>>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EntepriseDB Corporation
>> The Enterprise Postgres Company
>>
>>
>
>
> --
> Michael Paquier
> http://michael.otacoo.com
>



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