Sort a column that does not exist
Hi,
I'm in a project that probably some of you have heart about, it is called
PostgreSQLf and I get some help before from this list. The developer team is
been doing some progress about. Now one of us has created an extra column
that is called membership degree, this performs some calculations over some
data that it is store on a data table. We just create a target entry on the
parser and the planner, then in the ExecScan function is where the
calculations performs.
Well, I have been assigned a task. I have to sort that target entry. I
created this target entry on parse_clause and then I do this:
List *
transformSortClause(ParseState *pstate,
List *orderlist,
List **targetlist,
bool resolveUnknown)
{
List *sortlist = NIL;
ListCell *olitem;
foreach(olitem, orderlist)
{
SortBy *sortby = lfirst(olitem);
TargetEntry *tle;
//To find out if it is the membership degree
char *namegrmemb = strVal(linitial(((ColumnRef *)
sortby->node)->fields));
if (strcmp(namegrmemb, "grmemb")==0)
tle = createTargetFuzzyEntry(targetlist);
else
tle = findTargetlistEntry(pstate, sortby->node,
targetlist, ORDER_CLAUSE);
sortlist = addTargetToSortList(pstate, tle,
sortlist, *targetlist,
sortby->sortby_dir,
sortby->sortby_nulls,
sortby->useOp,
resolveUnknown);
}
return sortlist;
}
//To sort the membership degree
TargetEntry *
createTargetFuzzyEntry(List **targetlist){
/*I just have to create the fuzzy target entry right here */
TargetEntry *tfp = makeNode(TargetEntry);
Const *cn = makeNode(Const);
float val = 1.0;
TargetEntry *tlast = list_nth(*targetlist, list_length(*targetlist)-1);
cn = makeConst(700, -1, 4, (Float4GetDatum(val)), false, true);
tfp->resorigtbl=tlast->resorigtbl;
tfp->expr = (Expr *) cn;
tfp->resno = list_length(*targetlist) + 1;
tfp->resname = "grmemb";
tfp->resorigcol = list_length(*targetlist) + 1;
tfp->ressortgroupref = 0;
tfp->resjunk = false;
*targetlist = lappend(*targetlist, tfp);
return tfp;
}
As you can see if someone do this: SELECT * FROM table WHERE
field=some_value ORDER BY grmemb, postgresql creates a new target entry and
then assigned to the targetlist as a sort node. I know that it creates the
node on the parser, but it does not work, it seems the executor don't see
it.
How could I sort a column like this?, I know i'm missing something, but i
just don't see it. What is the process to sort a column?
2009/4/1 Werner Echezuria <wercool@gmail.com>:
As you can see if someone do this: SELECT * FROM table WHERE
field=some_value ORDER BY grmemb, postgresql creates a new target entry and
then assigned to the targetlist as a sort node. I know that it creates the
node on the parser, but it does not work, it seems the executor don't see
it.
See include/nodes/primnodes.h around line 1075:
bool resjunk; /* set to true to eliminate the attribute from
* final target list */
If the TargetEntry is set resjunk = false, the final result is
filtered as junk. So more accurately the executor sees but drops it.
How could I sort a column like this?, I know i'm missing something, but i
just don't see it. What is the process to sort a column?
Use makeTargetEntry in makefuncs.c
TargetEntry *
makeTargetEntry(Expr *expr,
AttrNumber resno,
char *resname,
bool resjunk)
by the 4th argument you can set resjunk = false if you don't want it
to be in the result.
Regards,
--
Hitoshi Harada
Hi, the problem goes on. I think the problem is in the planner.c on
grouping_planner function, because when I do a regular sort it gets to it:
/*
* If we were not able to make the plan come out in the right order, add
* an explicit sort step.
*/
if (parse->sortClause)
{
if (!pathkeys_contained_in(sort_pathkeys, current_pathkeys))
{
result_plan = (Plan *) make_sort_from_pathkeys(root,
result_plan,
sort_pathkeys,
limit_tuples);
current_pathkeys = sort_pathkeys;
}
}
and do the make_sort_from_pathkeys, but when I do the sort by grmemb it does
not. So I change it in order to pass through make_sort_from_pathkey, but it
drops an error like this "invalid attnum", so when I go to heaptuple and
force to get to ObjectIdGetDatum in heap_getsysattr, the server hang out.
What can I do? How can I assign a valid attrnum?
2009/4/1 Hitoshi Harada <umi.tanuki@gmail.com>
Show quoted text
2009/4/1 Werner Echezuria <wercool@gmail.com>:
As you can see if someone do this: SELECT * FROM table WHERE
field=some_value ORDER BY grmemb, postgresql creates a new target entryand
then assigned to the targetlist as a sort node. I know that it creates
the
node on the parser, but it does not work, it seems the executor don't see
it.See include/nodes/primnodes.h around line 1075:
bool resjunk; /* set to true to eliminate
the attribute from
* final
target list */If the TargetEntry is set resjunk = false, the final result is
filtered as junk. So more accurately the executor sees but drops it.How could I sort a column like this?, I know i'm missing something, but i
just don't see it. What is the process to sort a column?Use makeTargetEntry in makefuncs.c
TargetEntry *
makeTargetEntry(Expr *expr,
AttrNumber resno,
char *resname,
bool resjunk)by the 4th argument you can set resjunk = false if you don't want it
to be in the result.Regards,
--
Hitoshi Harada
Hi, I think I solved the problem in the parser and the planner, but I'm
stuck in the executor, I think is in the ExecSort function on nodeSort
around this code:
/*
* Scan the subplan and feed all the tuples to tuplesort.
*/
for (;;)
{
slot = ExecProcNode(outerNode);
if (TupIsNull(slot))
break;
tuplesort_puttupleslot(tuplesortstate, slot);
}
Now, when the server get in that loop it hangs out, Would I have to add
something that identifies the extra column? or will I have to include
somewhere in the tuplesort the column?
2009/4/2 Werner Echezuria <wercool@gmail.com>
Show quoted text
Hi, the problem goes on. I think the problem is in the planner.c on
grouping_planner function, because when I do a regular sort it gets to it:/*
* If we were not able to make the plan come out in the right order,
add
* an explicit sort step.
*/
if (parse->sortClause)
{
if (!pathkeys_contained_in(sort_pathkeys, current_pathkeys))
{
result_plan = (Plan *) make_sort_from_pathkeys(root,
result_plan,
sort_pathkeys,
limit_tuples);
current_pathkeys = sort_pathkeys;
}
}and do the make_sort_from_pathkeys, but when I do the sort by grmemb it
does not. So I change it in order to pass through make_sort_from_pathkey,
but it drops an error like this "invalid attnum", so when I go to heaptuple
and force to get to ObjectIdGetDatum in heap_getsysattr, the server hang
out. What can I do? How can I assign a valid attrnum?2009/4/1 Hitoshi Harada <umi.tanuki@gmail.com>
2009/4/1 Werner Echezuria <wercool@gmail.com>:
As you can see if someone do this: SELECT * FROM table WHERE
field=some_value ORDER BY grmemb, postgresql creates a new target entryand
then assigned to the targetlist as a sort node. I know that it creates
the
node on the parser, but it does not work, it seems the executor don't
see
it.
See include/nodes/primnodes.h around line 1075:
bool resjunk; /* set to true to eliminate
the attribute from
* final
target list */If the TargetEntry is set resjunk = false, the final result is
filtered as junk. So more accurately the executor sees but drops it.How could I sort a column like this?, I know i'm missing something, but
i
just don't see it. What is the process to sort a column?
Use makeTargetEntry in makefuncs.c
TargetEntry *
makeTargetEntry(Expr *expr,
AttrNumber resno,
char *resname,
bool resjunk)by the 4th argument you can set resjunk = false if you don't want it
to be in the result.Regards,
--
Hitoshi Harada