Обсуждение: 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