Re: identify table oid for an AggState during plan tree initialization

Hi,

during the initialization of the nodes in the plan tree (in ExecInitNode in
the file execProcnode.c) I want to find out for a node with the type T_Agg
which table will be aggregated. I tried the following:

resultAsAggState = ExecInitAgg((Agg *) node, estate, eflags);

if (resultAsAggState)
{
//tableOid = rel->rd_id;
//tableOid = resultAsAggState->ss.ss_currentRelation->rd_id;
}
It would be great to get the Oid of the table, but I would also be
satisfied if I could get at least the name of the table. Does anyone know
if it is possible to gather these information?

Best regards
Cathleen

2014/1/15 Masterprojekt Naumann1 <mpws2013n1(at)gmail(dot)com>

> Hi,
>
> during the initialization of the nodes in the plan tree (in ExecInitNode
> in the file execProcnode.c) I want to find out for a node with the type
> T_Agg which table will be aggregated. I tried the following:
>
> resultAsAggState = ExecInitAgg((Agg *) node, estate, eflags);
>
> if (resultAsAggState)
> {
> //tableOid = rel->rd_id;
> //tableOid = resultAsAggState->ss.ss_currentRelation->rd_id;
> }
> It would be great to get the Oid of the table, but I would also be
> satisfied if I could get at least the name of the table. Does anyone know
> if it is possible to gather these information?
>
> Best regards
> Cathleen
>

Sorry my mail program send the mail to early. Please ignore the line
tableOid = rel->rd_id. I was just deleting the line when google send the
mail :(

resultAsAggState is of the type AggState. As you can see I tried to get the
Oid with the ScanState inside of the AggState, but its Relation
ss_currentRelation is not set already. Is there another way to get the Oid?

Hi Cathleen,
An aggregate can be working on more than one table e.g.

select count(*) from a, b, c where a.c1 = b.c1 and b.c1 = c.c1;

In such a case, which table would you like to be reported?

IOW, it doesn't look to be sensible to attach and aggregate with a table.

If you can explain what you intend to do with that information, there might
be other ways to solve your problem.

On Wed, Jan 15, 2014 at 3:29 PM, Masterprojekt Naumann1 <
mpws2013n1(at)gmail(dot)com> wrote:

>
> 2014/1/15 Masterprojekt Naumann1 <mpws2013n1(at)gmail(dot)com>
>
>> Hi,
>>
>> during the initialization of the nodes in the plan tree (in ExecInitNode
>> in the file execProcnode.c) I want to find out for a node with the type
>> T_Agg which table will be aggregated. I tried the following:
>>
>> resultAsAggState = ExecInitAgg((Agg *) node, estate, eflags);
>>
>> if (resultAsAggState)
>> {
>> //tableOid = rel->rd_id;
>> //tableOid = resultAsAggState->ss.ss_currentRelation->rd_id;
>> }
>> It would be great to get the Oid of the table, but I would also be
>> satisfied if I could get at least the name of the table. Does anyone know
>> if it is possible to gather these information?
>>
>> Best regards
>> Cathleen
>>
>
> Sorry my mail program send the mail to early. Please ignore the line
> tableOid = rel->rd_id. I was just deleting the line when google send the
> mail :(
>
> resultAsAggState is of the type AggState. As you can see I tried to get
> the Oid with the ScanState inside of the AggState, but its Relation
> ss_currentRelation is not set already. Is there another way to get the Oid?
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

2014/1/15 Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>

> Hi Cathleen,
> An aggregate can be working on more than one table e.g.
>
> select count(*) from a, b, c where a.c1 = b.c1 and b.c1 = c.c1;
>
> In such a case, which table would you like to be reported?
>
> IOW, it doesn't look to be sensible to attach and aggregate with a table.
>
> If you can explain what you intend to do with that information, there
> might be other ways to solve your problem.
>
>
> On Wed, Jan 15, 2014 at 3:29 PM, Masterprojekt Naumann1 <
> mpws2013n1(at)gmail(dot)com> wrote:
>
>>
>> 2014/1/15 Masterprojekt Naumann1 <mpws2013n1(at)gmail(dot)com>
>>
>>> Hi,
>>>
>>> during the initialization of the nodes in the plan tree (in ExecInitNode
>>> in the file execProcnode.c) I want to find out for a node with the type
>>> T_Agg which table will be aggregated. I tried the following:
>>>
>>> resultAsAggState = ExecInitAgg((Agg *) node, estate, eflags);
>>>
>>> if (resultAsAggState)
>>> {
>>> //tableOid = rel->rd_id;
>>> //tableOid = resultAsAggState->ss.ss_currentRelation->rd_id;
>>> }
>>> It would be great to get the Oid of the table, but I would also be
>>> satisfied if I could get at least the name of the table. Does anyone know
>>> if it is possible to gather these information?
>>>
>>> Best regards
>>> Cathleen
>>>
>>
>> Sorry my mail program send the mail to early. Please ignore the line
>> tableOid = rel->rd_id. I was just deleting the line when google send the
>> mail :(
>>
>> resultAsAggState is of the type AggState. As you can see I tried to get
>> the Oid with the ScanState inside of the AggState, but its Relation
>> ss_currentRelation is not set already. Is there another way to get the Oid?
>>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>

Hi,

maybe I should clarify what I want to achieve: At the moment I only handle
special aggregations with a filter that compares values with a constant. An
exampe query would be "select sum(c_custkey), c_nationkey from customer
group by c_nationkey having sum(c_custkey) = 445820473;" This query can be
run on TPCH.

For such special queries I want to find out, on which column in the query
result will be filtered and what is the filtered value. For the columnid,
it is sufficient, if I get the table OID and the columnid of the original
column, i.e. the table OID of customer und the columnid of c_custkey in the
original table. I know that the expression is represented by an OpExpr,
that has an opno and operators. I would like to find such expression in an
AggState. Additionally, I need the table OID, which may be elsewhere.
Where can I find this information?

Best regards,
Cathleen

Hmm, ok, this is slighly involved.

Is it important that you should do this at the execution time? At planner
level where Agg node is involved, one can look for Aggref node and traverse
down the args list to find any vars are involved in aggregation. You can
find Var nodes involved in an Aggref by calling pull_var_clauses() with
appropriate arguments. The varno in those Var node can be used to pull
RangeTblEntry (RTE) in range table of query, hanging off of Query
(rt_fetch). If the RTE belongs to a table (it could be anything subquery,
function, join ...), relid in the RTE gives you OID of the table.

Though this is simple for simple query given as example in your mail, the
process is tricky for complex queries.

On Wed, Jan 15, 2014 at 6:05 PM, Masterprojekt Naumann1 <
mpws2013n1(at)gmail(dot)com> wrote:

> 2014/1/15 Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
>
>> Hi Cathleen,
>> An aggregate can be working on more than one table e.g.
>>
>> select count(*) from a, b, c where a.c1 = b.c1 and b.c1 = c.c1;
>>
>> In such a case, which table would you like to be reported?
>>
>> IOW, it doesn't look to be sensible to attach and aggregate with a table.
>>
>> If you can explain what you intend to do with that information, there
>> might be other ways to solve your problem.
>>
>>
>> On Wed, Jan 15, 2014 at 3:29 PM, Masterprojekt Naumann1 <
>> mpws2013n1(at)gmail(dot)com> wrote:
>>
>>>
>>> 2014/1/15 Masterprojekt Naumann1 <mpws2013n1(at)gmail(dot)com>
>>>
>>>> Hi,
>>>>
>>>> during the initialization of the nodes in the plan tree (in
>>>> ExecInitNode in the file execProcnode.c) I want to find out for a node with
>>>> the type T_Agg which table will be aggregated. I tried the following:
>>>>
>>>> resultAsAggState = ExecInitAgg((Agg *) node, estate, eflags);
>>>>
>>>> if (resultAsAggState)
>>>> {
>>>> //tableOid = rel->rd_id;
>>>> //tableOid = resultAsAggState->ss.ss_currentRelation->rd_id;
>>>> }
>>>> It would be great to get the Oid of the table, but I would also be
>>>> satisfied if I could get at least the name of the table. Does anyone know
>>>> if it is possible to gather these information?
>>>>
>>>> Best regards
>>>> Cathleen
>>>>
>>>
>>> Sorry my mail program send the mail to early. Please ignore the line
>>> tableOid = rel->rd_id. I was just deleting the line when google send the
>>> mail :(
>>>
>>> resultAsAggState is of the type AggState. As you can see I tried to get
>>> the Oid with the ScanState inside of the AggState, but its Relation
>>> ss_currentRelation is not set already. Is there another way to get the Oid?
>>>
>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EnterpriseDB Corporation
>> The Postgres Database Company
>>
>
>
> Hi,
>
> maybe I should clarify what I want to achieve: At the moment I only handle
> special aggregations with a filter that compares values with a constant. An
> exampe query would be "select sum(c_custkey), c_nationkey from customer
> group by c_nationkey having sum(c_custkey) = 445820473;" This query can be
> run on TPCH.
>
> For such special queries I want to find out, on which column in the query
> result will be filtered and what is the filtered value. For the columnid,
> it is sufficient, if I get the table OID and the columnid of the original
> column, i.e. the table OID of customer und the columnid of c_custkey in the
> original table. I know that the expression is represented by an OpExpr,
> that has an opno and operators. I would like to find such expression in an
> AggState. Additionally, I need the table OID, which may be elsewhere.
> Where can I find this information?
>
> Best regards,
> Cathleen
>
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company