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

[Michael P. <mic...@gm...>]

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
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