Обсуждение: Get the name of the target Relation from Query struct?
Hello, I want to get the target Relation name for a UPDATE / INSERT / DELETE in a planner_hook. Do I understand struct Query correctlythat: Query->resultRelation will be the index into Query->rtable to give me the target Relation? And if yes, what would rtable give me as list entry? An OID or a Relation or something else? Best regards, Ernst-Georg
On 5 April 2018 at 22:34, Ernst-Georg Schmid <ernst-georg.schmid@bayer.com> wrote: > I want to get the target Relation name for a UPDATE / INSERT / DELETE in a planner_hook. Do I understand struct Query correctlythat: > > Query->resultRelation will be the index into Query->rtable to give me the target Relation? Yes > And if yes, what would rtable give me as list entry? An OID or a Relation or something else? The list_nth(query->rtable, query->resultRelation) will give you a RangeTblEntry which has a property called relid, which is the Relation's OID as per pg_class.oid. If you want the relation name from the OID then you'll need something like get_rel_name(). You'll probably also want to check the query->commandType to ensure the command is one that will actually have a valid resultRelation. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Thank you David, so I am on the right track. >If you want the relation name from the OID then you'll need something >like get_rel_name(). Hm, lsyscache.c says for get_rel_name(): * NOTE: since relation name is not unique, be wary of code that uses this * for anything except preparing error messages. How do I get the schema name too, then? Call get_rel_namespace() and then get_rel_name() again with the Oid that was returned? Best regards, Ernst-Georg
On 6 April 2018 at 01:20, Ernst-Georg Schmid <ernst-georg.schmid@bayer.com> wrote: >>If you want the relation name from the OID then you'll need something >>like get_rel_name(). > > Hm, lsyscache.c says for get_rel_name(): > > * NOTE: since relation name is not unique, be wary of code that uses this > * for anything except preparing error messages. > > How do I get the schema name too, then? > > Call get_rel_namespace() and then get_rel_name() again with the Oid that was returned? No, get_rel_name returns the relname from pg_class. What you need is in pg_namespace: namespace from relid: get_namespace_name(get_rel_namespace(relid)) relname from relid: get_rel_name(relid)))); If you're going to relation_open the rel, then you might want to use: get_namespace_name(RelationGetNamespace(rel)) and RelationGetRelationName(rel)))); -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Hello again,
unfortunately this:
>The list_nth(query->rtable, query->resultRelation) will give you a
>RangeTblEntry which has a property called relid, which is the
>Relation's OID as per pg_class.oid.
gives me a Signal 11.
I can get the resultRelation and it is 1 (which matches the Documentation that says that it would be 0 for a SELECT but
Iexclude SELECTs as you suggested).
But the list_nth() reliably crashes the backend with a Signal 11.
Here is the complete function:
static PlannedStmt *
limit_func(Query *parse, int cursorOptions, ParamListInfo boundParams)
{
PlannedStmt *result;
RangeTblEntry *rte;
char *target_table;
char *target_schema;
/* this way we can daisy chain planner hooks if necessary */
if (prev_planner_hook != NULL)
result = (*prev_planner_hook) (parse, cursorOptions, boundParams);
else
result = standard_planner(parse, cursorOptions, boundParams);
if(parse->commandType != CMD_SELECT) {
int resultRelation = parse->resultRelation;
rte = (RangeTblEntry *) list_nth(parse->rtable, resultRelation);
//target_table = get_rel_name(rte->relid);
//target_schema = get_namespace_name(get_rel_namespace(rte->relid));
ereport(NOTICE, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("%i", resultRelation)));
}
return result;
}
If I comment out the line: rte = (RangeTblEntry *) list_nth(parse->rtable, resultRelation);
it works and sends me the resultRelation to a NOTICE in the log output.
What am I doing wrong? It's a 10.3 on Ubuntu 16.04 compiled from sources. Everything else, e.g. multicorn and other
extensionswork, so I guess it must be my code and not a problem of the build system or PostgreSQL itself.
Best regards,
Ernst-Georg
On 6 April 2018 at 20:38, Ernst-Georg Schmid
<ernst-georg.schmid@bayer.com> wrote:
> I can get the resultRelation and it is 1 (which matches the Documentation that says that it would be 0 for a SELECT
butI exclude SELECTs as you suggested).
I didn't quite say exclude SELECT :)
I wrote:
> You'll probably also want to check the query->commandType to ensure
> the command is one that will actually have a valid resultRelation.
> if(parse->commandType != CMD_SELECT) {
Does it perhaps crash on a utility command?
Have a look at parsenodes.h and find the comments for resultRelation,
then find the commandType field and look for the definition of
CmdType. You'll see you need to think a bit harder about which
commandTypes you allow.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Hello, the crashing query is INSERT INTO blah.blahfasel (id) VALUES (1). But I'll check specifically for INSERTs now and try again. BTW: What exactly is a UTILITY command? Something like e.g. TRUNCATE or VACUUM? Best regards, Ernst-Georg
Unfortunately it still crashes with Signal 11 even with a more strict check:
if(parse->commandType == CMD_INSERT) {
best regards,
Ernst-Georg
On Fri, Apr 6, 2018 at 2:08 PM, Ernst-Georg Schmid
<ernst-georg.schmid@bayer.com> wrote:
>
> if(parse->commandType != CMD_SELECT) {
> int resultRelation = parse->resultRelation;
>
> rte = (RangeTblEntry *) list_nth(parse->rtable, resultRelation);
>
list_nth() assumes that the lists are 0 based. But relations indexes
are 1 based. Please use rt_fetch() or planner_rt_fetch() as suitable.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company