postgres-xc-bugs

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

[Dimitrije R. <tem...@gm...>]

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
CREATE VIEW some_view AS SELECT test_table.* FROM test_table;

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.

Kind regards,
Dimitrije

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

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

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

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

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

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

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

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

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?
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...
-- 
Michael Paquier
http://michael.otacoo.com

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

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

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

Hi Michael,
That's a good catch and good that we came upon it before finalising the fix.

While creating the view definition, it tries to parse the SELECT statement
and while doing so, tries to resolve the aggregate function. On datanode,
aggregates have transition type as return type, which in this case is
polymorphic (not acceptable as return type). We manage such aggregates by
not pushing the aggregates down to the datanodes, but in this case don't
look at what can be pushed or not inside the view definition.

What we may want to do, and is hard to do, is to dis-assemble the view
definition at coordinator and send the relevant information (the one stored
in catalogs?) to the datanode to be stored directly (without involving
parsing etc.). The same may need to be done with all the utilities, but
this is a massive change, and something which needs to be thought through
properly.

On Wed, Jun 27, 2012 at 11:05 AM, Michael Paquier <mic...@gm...
> wrote:

> Hi,
>
> I wrote a patch enabling the creation of views on Datanodes to get rid of
> this function problem. The fix is attached.
> However, while digging into this issue, I found a problem with types and
> views, for example:
> create table aa (a int);
> create type aa_type as enum ('1','2','3','4','5','6');
> create view aa_v as select max(a::aa_type) from aa; -- created on all the
> nodes
> ERROR:  column "max" has pseudo-type anyenum
>
> This error comes from heap.c, where a check is done on the type of the
> column.
> The problem is that in the case of aggregates, we use the transition type
> on Datanodes, which is a pseudo-type and is by definition forbidden for as
> a column type.
> The aggregate modification comes from here:
> --- a/src/backend/parser/parse_agg.c
> +++ b/src/backend/parser/parse_agg.c
> @@ -209,6 +209,7 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
>     aggform = (Form_pg_aggregate) GETSTRUCT(aggTuple);
>     agg->aggtrantype = aggform->aggtranstype;
>     agg->agghas_collectfn = OidIsValid(aggform->aggcollectfn);
> +   //Error comes from this one:
>     if (IS_PGXC_DATANODE)
>         agg->aggtype = agg->aggtrantype;
>
> Associating a transition type on Datanodes for aggregates is correct, but
> until now we have never created views on Datanodes.
> Btw, a fix for this second issue is included in the patch attached. What I
> simply did was bypassing the error on Datanodes as we may have a
> pseudo-type in the case of an aggregate. Ashutosh, comments on that?
>
>
>
> On Wed, Jun 20, 2012 at 2:59 PM, Ashutosh Bapat <
> ash...@en...> wrote:
>
>>
>>
>> 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
>>
>>
>
>
> --
> Michael Paquier
> http://michael.otacoo.com
>



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

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

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

On Wed, Jun 27, 2012 at 3:23 PM, Ashutosh Bapat <
ash...@en...> wrote:

> Hi Michael,
> That's a good catch and good that we came upon it before finalising the
> fix.
>
> While creating the view definition, it tries to parse the SELECT statement
> and while doing so, tries to resolve the aggregate function. On datanode,
> aggregates have transition type as return type, which in this case is
> polymorphic (not acceptable as return type). We manage such aggregates by
> not pushing the aggregates down to the datanodes, but in this case don't
> look at what can be pushed or not inside the view definition.
>
Yes, OK.


>
> What we may want to do, and is hard to do, is to dis-assemble the view
> definition at coordinator and send the relevant information (the one stored
> in catalogs?) to the datanode to be stored directly (without involving
> parsing etc.). The same may need to be done with all the utilities, but
> this is a massive change, and something which needs to be thought through
> properly.
>
This is... well... not simple. And not completely related to this fix.


>
>
> On Wed, Jun 27, 2012 at 11:05 AM, Michael Paquier <
> mic...@gm...> wrote:
>
>> Hi,
>>
>> I wrote a patch enabling the creation of views on Datanodes to get rid of
>> this function problem. The fix is attached.
>> However, while digging into this issue, I found a problem with types and
>> views, for example:
>> create table aa (a int);
>> create type aa_type as enum ('1','2','3','4','5','6');
>> create view aa_v as select max(a::aa_type) from aa; -- created on all the
>> nodes
>> ERROR:  column "max" has pseudo-type anyenum
>>
>> This error comes from heap.c, where a check is done on the type of the
>> column.
>> The problem is that in the case of aggregates, we use the transition type
>> on Datanodes, which is a pseudo-type and is by definition forbidden for as
>> a column type.
>> The aggregate modification comes from here:
>> --- a/src/backend/parser/parse_agg.c
>> +++ b/src/backend/parser/parse_agg.c
>> @@ -209,6 +209,7 @@ transformAggregateCall(ParseState *pstate, Aggref
>> *agg,
>>     aggform = (Form_pg_aggregate) GETSTRUCT(aggTuple);
>>     agg->aggtrantype = aggform->aggtranstype;
>>     agg->agghas_collectfn = OidIsValid(aggform->aggcollectfn);
>> +   //Error comes from this one:
>>     if (IS_PGXC_DATANODE)
>>         agg->aggtype = agg->aggtrantype;
>>
>> Associating a transition type on Datanodes for aggregates is correct, but
>> until now we have never created views on Datanodes.
>> Btw, a fix for this second issue is included in the patch attached. What
>> I simply did was bypassing the error on Datanodes as we may have a
>> pseudo-type in the case of an aggregate. Ashutosh, comments on that?
>>
>>
>>
>> On Wed, Jun 20, 2012 at 2:59 PM, Ashutosh Bapat <
>> ash...@en...> wrote:
>>
>>>
>>>
>>> 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
>>>
>>>
>>
>>
>> --
>> Michael Paquier
>> http://michael.otacoo.com
>>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>
>


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