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

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

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.

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