From a74641fbe04ae80f09ea41e5c71ef9c63c1a288c Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Wed, 21 Aug 2024 19:41:48 +0530 Subject: [PATCH 8/8] Fixes following issues 1. A query containing GRAPH_TABLE reference used in a UDF causes segmentation fault: Earlier version of patches used p_post_columnref_hook to transform a property reference in a GRAPH_TABLE reference. But related hooks and members in ParseState are used by other non-core modules like plpgsql. If a query inside a plpgsql function has GRAPH_TABLE reference in it the usage of these hooks conflicts. Since GRAPH_TABLE is a core feature, it's better to introduce a new member ParseState::p_graph_table_pstate, of type GraphTableParseState, to hold the namespace for the GRAPH_TABLE reference being transformed. It is inline with ParseState::p_parent_cte or ParseState::p_queryEnv which have similar purposes. With that change definition of GraphTableParseState is moved to parsenodes.h where CommonTableExpr and QueryEnvironment are also defined. The function using the ParseState::p_graph_table_pstate is renamed to transformGraphTablePropertyRef() to be inline with names of other similar functions. This change also allows GraphTableParseState argument to be removed from a few functions. 2. ERROR: unrecognized lock mode: 0 via ScanQueryForLocks(): After fixing the first bug, I ran into above error. An RTE_GRAPH_TABLE is converted to RTE_SUBQUERY after transformation with relid = OID of the property graph being referenced in GRAPH_TABLE clause. Functions called from ScanQueryForLocks() take locks when RangeTblRef::relid is valid; which is desirable since we want to lock the property graph while it's being used. So set RangeTblRef::rellockmode to AccessShareLock. 3. Prohibits subquery within GRAPH_TABLE reference: In order to support it the hasSublinks flag needs to be transferred to the queries produced as a result of rewriting graph table RTE. It needs a bit of code and some non-trivial testing. So not done right now. 4. Support edge patterns in any direction: Such edge patterns satisfy edges in either direction. Additional changes 1. Test for a join between GRAPH_TABLE and a regular table. 2. Changed a few loops over lists to use foreach_node reducing and simplifying code a bit. 3. Improved comments. Author: Ashutosh Bapat Reported By: Ajay Pal --- src/backend/parser/parse_clause.c | 30 ++++- src/backend/parser/parse_expr.c | 5 + src/backend/parser/parse_graphtable.c | 43 +++---- src/backend/parser/parse_relation.c | 1 + src/backend/rewrite/rewriteGraphTable.c | 148 +++++++++++++--------- src/include/nodes/parsenodes.h | 6 + src/include/parser/parse_graphtable.h | 11 +- src/include/parser/parse_node.h | 5 + src/test/regress/expected/graph_table.out | 81 +++++++++++- src/test/regress/sql/graph_table.sql | 45 +++++++ 10 files changed, 277 insertions(+), 98 deletions(-) diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 63f5dd5556..923f8b3fbf 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -917,6 +917,7 @@ transformRangeGraphTable(ParseState *pstate, RangeGraphTable *rgt) List *colnames = NIL; ListCell *lc; int resno = 0; + bool saved_hasSublinks; rel = parserOpenTable(pstate, rgt->graph_name, AccessShareLock); if (rel->rd_rel->relkind != RELKIND_PROPGRAPH) @@ -930,12 +931,21 @@ transformRangeGraphTable(ParseState *pstate, RangeGraphTable *rgt) gpstate->graphid = graphid; - pstate->p_post_columnref_hook = graph_table_property_reference; - pstate->p_ref_hook_state = gpstate; + /* + * The syntax does not allow nested GRAPH_TABLE and this function + * prohibits subquery within GRAPH_TABLE. There should be only one + * GRAPH_TABLE being transformed at a time. + */ + Assert(!pstate->p_graph_table_pstate); + pstate->p_graph_table_pstate = gpstate; + Assert(!pstate->p_lateral_active); pstate->p_lateral_active = true; - gp = transformGraphPattern(pstate, gpstate, rgt->graph_pattern); + saved_hasSublinks = pstate->p_hasSubLinks; + pstate->p_hasSubLinks = false; + + gp = transformGraphPattern(pstate, rgt->graph_pattern); foreach(lc, rgt->columns) { @@ -970,10 +980,20 @@ transformRangeGraphTable(ParseState *pstate, RangeGraphTable *rgt) table_close(rel, NoLock); - pstate->p_pre_columnref_hook = NULL; - pstate->p_ref_hook_state = NULL; + pstate->p_graph_table_pstate = NULL; pstate->p_lateral_active = false; + /* + * If we support subqueries within GRAPH_TABLE, those need to be + * propagated to the queries resulting from rewriting graph table RTE. We + * don't do that right now, hence prohibit it for now. + */ + if (pstate->p_hasSubLinks) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("subqueries within GRAPH_TABLE reference are not supported"))); + pstate->p_hasSubLinks = saved_hasSublinks; + return addRangeTableEntryForGraphTable(pstate, graphid, castNode(GraphPattern, gp), columns, colnames, rgt->alias, false, true); } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 38222e745e..2c1e74774d 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -30,6 +30,7 @@ #include "parser/parse_collate.h" #include "parser/parse_expr.h" #include "parser/parse_func.h" +#include "parser/parse_graphtable.h" #include "parser/parse_oper.h" #include "parser/parse_relation.h" #include "parser/parse_target.h" @@ -825,6 +826,10 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) break; } + /* Try it as a graph table property reference. */ + if (node == NULL) + node = transformGraphTablePropertyRef(pstate, cref); + /* * Now give the PostParseColumnRefHook, if any, a chance. We pass the * translation-so-far so that it can throw an error if it wishes in the diff --git a/src/backend/parser/parse_graphtable.c b/src/backend/parser/parse_graphtable.c index 1a97168702..b088306b5b 100644 --- a/src/backend/parser/parse_graphtable.c +++ b/src/backend/parser/parse_graphtable.c @@ -33,12 +33,15 @@ /* - * Resolve a property reference. + * Transform a property reference. */ Node * -graph_table_property_reference(ParseState *pstate, ColumnRef *cref, Node *var) +transformGraphTablePropertyRef(ParseState *pstate, ColumnRef *cref) { - GraphTableParseState *gpstate = pstate->p_ref_hook_state; + GraphTableParseState *gpstate = pstate->p_graph_table_pstate; + + if (!gpstate) + return NULL; if (list_length(cref->fields) == 2) { @@ -144,8 +147,10 @@ transformLabelExpr(GraphTableParseState *gpstate, Node *labelexpr) * Transform a GraphElementPattern. */ static Node * -transformGraphElementPattern(ParseState *pstate, GraphTableParseState *gpstate, GraphElementPattern *gep) +transformGraphElementPattern(ParseState *pstate, GraphElementPattern *gep) { + GraphTableParseState *gpstate = pstate->p_graph_table_pstate; + if (gep->variable) gpstate->variables = lappend(gpstate->variables, makeString(pstrdup(gep->variable))); @@ -161,17 +166,13 @@ transformGraphElementPattern(ParseState *pstate, GraphTableParseState *gpstate, * Transform a path term (list of GraphElementPattern's). */ static Node * -transformPathTerm(ParseState *pstate, GraphTableParseState *gpstate, List *path_term) +transformPathTerm(ParseState *pstate, List *path_term) { List *result = NIL; - ListCell *lc; - - foreach(lc, path_term) - { - Node *n = transformGraphElementPattern(pstate, gpstate, lfirst_node(GraphElementPattern, lc)); - result = lappend(result, n); - } + foreach_node(GraphElementPattern, gep, path_term) + result = lappend(result, + transformGraphElementPattern(pstate, gep)); return (Node *) result; } @@ -180,17 +181,12 @@ transformPathTerm(ParseState *pstate, GraphTableParseState *gpstate, List *path_ * Transform a path pattern list (list of path terms). */ static Node * -transformPathPatternList(ParseState *pstate, GraphTableParseState *gpstate, List *path_pattern) +transformPathPatternList(ParseState *pstate, List *path_pattern) { List *result = NIL; - ListCell *lc; - foreach(lc, path_pattern) - { - Node *n = transformPathTerm(pstate, gpstate, lfirst(lc)); - - result = lappend(result, n); - } + foreach_node(List, path_term, path_pattern) + result = lappend(result, transformPathTerm(pstate, path_term)); return (Node *) result; } @@ -199,9 +195,12 @@ transformPathPatternList(ParseState *pstate, GraphTableParseState *gpstate, List * Transform a GraphPattern. */ Node * -transformGraphPattern(ParseState *pstate, GraphTableParseState *gpstate, GraphPattern *graph_pattern) +transformGraphPattern(ParseState *pstate, GraphPattern *graph_pattern) { - graph_pattern->path_pattern_list = (List *) transformPathPatternList(pstate, gpstate, graph_pattern->path_pattern_list); + List *path_pattern_list = castNode(List, + transformPathPatternList(pstate, graph_pattern->path_pattern_list)); + + graph_pattern->path_pattern_list = path_pattern_list; graph_pattern->whereClause = transformExpr(pstate, graph_pattern->whereClause, EXPR_KIND_WHERE); assign_expr_collations(pstate, graph_pattern->whereClause); diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 5ea9fa993b..d222c958c1 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -2154,6 +2154,7 @@ addRangeTableEntryForGraphTable(ParseState *pstate, rte->graph_pattern = graph_pattern; rte->graph_table_columns = columns; rte->alias = alias; + rte->rellockmode = AccessShareLock; eref = alias ? copyObject(alias) : makeAlias(refname, NIL); diff --git a/src/backend/rewrite/rewriteGraphTable.c b/src/backend/rewrite/rewriteGraphTable.c index 6e2e6ed38b..1ea5a5caeb 100644 --- a/src/backend/rewrite/rewriteGraphTable.c +++ b/src/backend/rewrite/rewriteGraphTable.c @@ -41,12 +41,11 @@ /* * Represents one path factor in a path. * - * One path factor corresponds to one element pattern in a simple path. + * In a non-cyclic path, one path factor corresponds to one element pattern. * * In a cyclic path, one path factor may correspond to one or more element - * patterns all pointing to the same graph element. The members of such a path - * factor are a combination of corresponding specifications in the element - * patterns. + * patterns sharing the same variable name, thus pointing to the same graph + * element. */ struct path_factor { @@ -66,17 +65,20 @@ struct path_factor * Represents one property graph element (vertex or edge) in the path. * * Label expression in an element pattern resolves into a set of elements. For - * each of those elements we create one graph_path_element object. + * each of those elements we create one path_element object. */ struct path_element { + /* Path factor from which this element is derived. */ + struct path_factor *path_factor; Oid elemoid; Oid reloid; + /* Source and destination vertex elements for an edge element. */ Oid srcvertexid; Oid destvertexid; - List *qual_exprs; - /* Path factor from which this element is derived. */ - struct path_factor *path_factor; + /* Source and destination conditions for an edge element. */ + List *src_quals; + List *dest_quals; }; static Node *replace_property_refs(Oid propgraphid, Node *node, const List *mappings); @@ -187,23 +189,22 @@ generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern) /* * For every element pattern in the given path pattern collect all the * graph elements that satisfy the element pattern. + * + * Element patterns with the same name represent the same element and + * hence same path factor. They do not add a new graph element to the + * query but affect the links of adjacent elements. Merge such elements + * patterns into a single path factor. */ foreach_node(GraphElementPattern, gep, path_pattern) { struct path_factor *pf = NULL; - if (gep->kind != VERTEX_PATTERN && - gep->kind != EDGE_PATTERN_LEFT && gep->kind != EDGE_PATTERN_RIGHT) - elog(ERROR, "unsupported element pattern kind: %s", get_gep_kind_name(gep->kind)); + if (gep->kind != VERTEX_PATTERN && !IS_EDGE_PATTERN(gep->kind)) + elog(ERROR, "unsupported element pattern kind: \"%s\"", get_gep_kind_name(gep->kind)); if (gep->quantifier) elog(ERROR, "element pattern quantifier not supported yet"); - /* - * Element patterns with the same name represent the same element and - * hence same path factor. They do not add a new graph element to the - * query but affect the links of adjacent elements. - */ foreach_ptr(struct path_factor, other, path_factors) { if (gep->variable && other->variable && @@ -227,12 +228,14 @@ generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern) other->labelexpr = gep->labelexpr; else if (gep->labelexpr && !equal(other->labelexpr, gep->labelexpr)) ereport(ERROR, - /* XXX: Use correct error code. */ - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("element patterns with same variable name \"%s\" but different label expressions", + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("element patterns with same variable name \"%s\" but different label expressions are not supported", gep->variable))); - /* Both sets of conditions apply to the element pattern. */ + /* + * Conditions from both elements patterns constrain the graph + * element. Combine by ANDing them. + */ if (!other->whereClause) other->whereClause = gep->whereClause; else if (gep->whereClause) @@ -259,19 +262,27 @@ generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern) } /* - * Setup adjacent path factors. If multiple edge patterns share the - * same variable name, they constain the adjacent vertex patterns - * since an edge can connect only one pair of vertexes and those - * vertexes also need to repeated along with the edge (a walk). This - * means that we have to coalesce the vertex patterns adjacent to a - * repeated edge even though they have different variables. E.g. - * (a)-[b]->(c)-[b]<-(d) implies that (a) and (d) represent the same + * Setup links to the previous path factor. If the previous path + * factor is an edge, this path factor represents an adjacent vertex; + * source vertex for an edge pointing left or destination vertex for + * an edge pointing right. Edge pointing in any direction is treated + * similar to that pointing in right direction here. When constructing + * a query, in generate_query_for_graph_path() we will swap source and + * destination elements if the edge element turns out to be and edge + * pointing in left direction. + * + * If multiple edge patterns share the same variable name, they + * constain the adjacent vertex patterns since an edge can connect + * only one pair of vertexes. Those vertex patterns also need to + * repeated and merged along with the repeated edge (a walk of graph) + * even though they have different variables. E.g. + * (a)-[b]->(c)<-[b]-(d) implies that (a) and (d) represent the same * vertex element pattern. This is slighly harder to implement and * probably less useful. Hence not supported for now. */ if (prev_pf) { - if (prev_pf->kind == EDGE_PATTERN_RIGHT) + if (prev_pf->kind == EDGE_PATTERN_RIGHT || prev_pf->kind == EDGE_PATTERN_ANY) { Assert(!IS_EDGE_PATTERN(pf->kind)); if (prev_pf->dest_pf && prev_pf->dest_pf != pf) @@ -285,13 +296,8 @@ generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern) elog(ERROR, "An edge can not connect more than two vertexes even in a cyclic pattern."); prev_pf->src_pf = pf; } - else if (prev_pf->kind == EDGE_PATTERN_ANY) - { - /* We don't support undirected edges yet. */ - Assert(false); - } - if (pf->kind == EDGE_PATTERN_RIGHT) + if (pf->kind == EDGE_PATTERN_RIGHT || pf->kind == EDGE_PATTERN_ANY) { Assert(!IS_EDGE_PATTERN(prev_pf->kind)); if (pf->src_pf && pf->src_pf != prev_pf) @@ -305,11 +311,6 @@ generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern) elog(ERROR, "An edge can not connect more than two vertexes even in a cyclic pattern."); pf->dest_pf = prev_pf; } - else if (pf->kind == EDGE_PATTERN_ANY) - { - /* We don't support undirected edges yet. */ - Assert(false); - } } prev_pf = pf; @@ -389,31 +390,62 @@ generate_query_for_graph_path(RangeTblEntry *rte, List *graph_path) Relation rel; ParseNamespaceItem *pni; - Assert(pf->kind == VERTEX_PATTERN || - pf->kind == EDGE_PATTERN_LEFT || pf->kind == EDGE_PATTERN_RIGHT); + Assert(pf->kind == VERTEX_PATTERN || IS_EDGE_PATTERN(pf->kind)); - if (pf->kind == EDGE_PATTERN_LEFT || pf->kind == EDGE_PATTERN_RIGHT) + /* Add conditions representing edge connnections. */ + if (IS_EDGE_PATTERN(pf->kind)) { struct path_element *src_pe; - struct path_element *dest_ge; + struct path_element *dest_pe; + List *src_quals; + List *dest_quals; Assert(pf->src_pf && pf->dest_pf); src_pe = list_nth(graph_path, pf->src_pf->factorpos); - dest_ge = list_nth(graph_path, pf->dest_pf->factorpos); + dest_pe = list_nth(graph_path, pf->dest_pf->factorpos); /* Make sure that the links of adjacent vertices are correct. */ Assert(pf->src_pf == src_pe->path_factor && - pf->dest_pf == dest_ge->path_factor); + pf->dest_pf == dest_pe->path_factor); /* * If the given edge element does not connect the adjacent vertex * elements in this path, the path is broken. Abandon this path as * it won't return any rows. + * + * For an edge element pattern pointing in any direction, try + * swapping the source and destination vertex elements. */ if (src_pe->elemoid != pe->srcvertexid || - dest_ge->elemoid != pe->destvertexid) - return NULL; + dest_pe->elemoid != pe->destvertexid) + { + if (pf->kind == EDGE_PATTERN_ANY && + dest_pe->elemoid == pe->srcvertexid && + src_pe->elemoid == pe->destvertexid) + { + dest_quals = copyObject(pe->src_quals); + src_quals = copyObject(pe->dest_quals); + + /* Swap the source and destination varnos in the quals. */ + ChangeVarNodes((Node *) dest_quals, pe->path_factor->src_pf->factorpos + 1, + pe->path_factor->dest_pf->factorpos + 1, 0); + ChangeVarNodes((Node *) src_quals, pe->path_factor->dest_pf->factorpos + 1, + pe->path_factor->src_pf->factorpos + 1, 0); + } + else + return NULL; + } + else + { + src_quals = copyObject(pe->src_quals); + dest_quals = copyObject(pe->dest_quals); + } + + qual_exprs = list_concat(qual_exprs, src_quals); + qual_exprs = list_concat(qual_exprs, dest_quals); } + else + Assert(!pe->src_quals && !pe->dest_quals); /* Create RangeTblEntry for this element table. */ rel = table_open(pe->reloid, AccessShareLock); @@ -442,7 +474,6 @@ generate_query_for_graph_path(RangeTblEntry *rte, List *graph_path) qual_exprs = lappend(qual_exprs, tr); } - qual_exprs = list_concat(qual_exprs, pe->qual_exprs); } if (rte->graph_pattern->whereClause) @@ -457,7 +488,7 @@ generate_query_for_graph_path(RangeTblEntry *rte, List *graph_path) path_query->jointree = makeFromExpr(fromlist, (Node *) makeBoolExpr(AND_EXPR, qual_exprs, -1)); - /* Each path query projects the columns specified in the GRAH_TABLE clause */ + /* Each path query projects the COLUMNS specified in the GRAH_TABLE. */ path_query->targetList = castNode(List, replace_property_refs(rte->relid, (Node *) rte->graph_table_columns, @@ -667,7 +698,6 @@ create_gpe_for_element(struct path_factor *pf, Oid elemoid) pe->path_factor = pf; pe->elemoid = elemoid; pe->reloid = pgeform->pgerelid; - pe->qual_exprs = NIL; /* * When the path containing this element will be converted into a query @@ -681,8 +711,6 @@ create_gpe_for_element(struct path_factor *pf, Oid elemoid) */ if (IS_EDGE_PATTERN(pf->kind)) { - List *edge_qual; - pe->srcvertexid = pgeform->pgesrcvertexid; pe->destvertexid = pgeform->pgedestvertexid; Assert(pf->src_pf && pf->dest_pf); @@ -700,14 +728,12 @@ create_gpe_for_element(struct path_factor *pf, Oid elemoid) * number of paths this element appears in, fetching the catalog entry * each time. */ - edge_qual = build_edge_vertex_link_quals(eletup, pf->factorpos + 1, pf->src_pf->factorpos + 1, - Anum_pg_propgraph_element_pgesrckey, - Anum_pg_propgraph_element_pgesrcref); - pe->qual_exprs = list_concat(pe->qual_exprs, edge_qual); - edge_qual = build_edge_vertex_link_quals(eletup, pf->factorpos + 1, pf->dest_pf->factorpos + 1, - Anum_pg_propgraph_element_pgedestkey, - Anum_pg_propgraph_element_pgedestref); - pe->qual_exprs = list_concat(pe->qual_exprs, edge_qual); + pe->src_quals = build_edge_vertex_link_quals(eletup, pf->factorpos + 1, pf->src_pf->factorpos + 1, + Anum_pg_propgraph_element_pgesrckey, + Anum_pg_propgraph_element_pgesrcref); + pe->dest_quals = build_edge_vertex_link_quals(eletup, pf->factorpos + 1, pf->dest_pf->factorpos + 1, + Anum_pg_propgraph_element_pgedestkey, + Anum_pg_propgraph_element_pgedestref); } ReleaseSysCache(eletup); @@ -727,7 +753,7 @@ get_gep_kind_name(GraphElementPatternKind gepkind) case EDGE_PATTERN_RIGHT: return "edge pointing right"; case EDGE_PATTERN_ANY: - return "undirected edge"; + return "edge pointing any direction"; case PAREN_EXPR: return "nested path pattern"; } diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index c591b566e2..3d5e5a82f0 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -4359,4 +4359,10 @@ typedef struct DropSubscriptionStmt DropBehavior behavior; /* RESTRICT or CASCADE behavior */ } DropSubscriptionStmt; +typedef struct GraphTableParseState +{ + Oid graphid; + List *variables; +} GraphTableParseState; + #endif /* PARSENODES_H */ diff --git a/src/include/parser/parse_graphtable.h b/src/include/parser/parse_graphtable.h index af0f550cd2..4cefd5acf9 100644 --- a/src/include/parser/parse_graphtable.h +++ b/src/include/parser/parse_graphtable.h @@ -14,18 +14,11 @@ #ifndef PARSE_GRAPHTABLE_H #define PARSE_GRAPHTABLE_H -#include "nodes/parsenodes.h" #include "nodes/pg_list.h" #include "parser/parse_node.h" -typedef struct GraphTableParseState -{ - Oid graphid; - List *variables; -} GraphTableParseState; +extern Node *transformGraphTablePropertyRef(ParseState *pstate, ColumnRef *cref); -extern Node *graph_table_property_reference(ParseState *pstate, ColumnRef *cref, Node *var); - -extern Node *transformGraphPattern(ParseState *pstate, GraphTableParseState *gpstate, GraphPattern *graph_pattern); +extern Node *transformGraphPattern(ParseState *pstate, GraphPattern *graph_pattern); #endif /* PARSE_GRAPHTABLE_H */ diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 3246fbe156..949ab1a494 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -177,6 +177,9 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param, * p_resolve_unknowns: resolve unknown-type SELECT output columns as type TEXT * (this is true by default). * + * p_graph_table_pstate: Namespace for the GRAPH_TABLE reference being + * transformed, if any. + * * p_hasAggs, p_hasWindowFuncs, etc: true if we've found any of the indicated * constructs in the query. * @@ -219,6 +222,8 @@ struct ParseState * type text */ QueryEnvironment *p_queryEnv; /* curr env, incl refs to enclosing env */ + GraphTableParseState *p_graph_table_pstate; /* Current graph table + * namespace, if any */ /* Flags telling about things found in the query: */ bool p_hasAggs; diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out index 87ab3e31af..3796297cb0 100644 --- a/src/test/regress/expected/graph_table.out +++ b/src/test/regress/expected/graph_table.out @@ -349,6 +349,25 @@ select src, conn, dest, lprop1, vprop2, vprop1 from graph_table (g1 match (a is v11 | e132 | v31 | vl3_prop | | 2010 (4 rows) +-- edges directed in both ways - to and from v2 +SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS vl2)-[conn]-(dest) COLUMNS (src.vname AS sname, conn.ename AS cname, dest.vname AS dname)); + sname | cname | dname +-------+-------+------- + v21 | e122 | v12 + v22 | e121 | v11 + v21 | e211 | v12 + v22 | e231 | v32 +(4 rows) + +SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS vl2)-(dest) COLUMNS (src.vname AS sname, dest.vname AS dname)); + sname | dname +-------+------- + v21 | v12 + v22 | v11 + v21 | v12 + v22 | v32 +(4 rows) + -- Errors -- vl1 is not associated with property vprop2 select src, src_vprop2, conn, dest from graph_table (g1 match (a is vl1)-[b is el1]->(c is vl2 | vl3) columns (a.vname as src, a.vprop2 as src_vprop2, b.ename as conn, c.vname as dest)); @@ -483,7 +502,7 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a where a.vprop1 between 20 and 2000)->(b w SELECT * FROM GRAPH_TABLE (g1 MATCH (a is l1)-[a is l1]->(b is l1) columns (a.ename AS aename, b.ename AS bename)) ORDER BY 1, 2; -- error ERROR: element patterns with same variable name "a" but different element pattern types SELECT * FROM GRAPH_TABLE (g1 MATCH (a is vl1)->(b)->(a is vl2) WHERE a.vname <> b.vname COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through; -- error -ERROR: element patterns with same variable name "a" but different label expressions +ERROR: element patterns with same variable name "a" but different label expressions are not supported SELECT * FROM GRAPH_TABLE (g1 MATCH (a is vl1)->(b)->(a) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through; self | through | self_p1 | through_p1 ------+---------+---------+------------ @@ -604,5 +623,65 @@ SELECT * FROM customers_us_redacted; redacted1 (1 row) +-- GRAPH_TABLE in UDFs +CREATE FUNCTION out_degree(sname varchar) RETURNS varchar AS $$ +DECLARE + out_degree int; +BEGIN + SELECT count(*) INTO out_degree + FROM GRAPH_TABLE (g1 MATCH (src WHERE src.vname = sname)->() COLUMNS (src.vname)); + RETURN out_degree; +END; +$$ LANGUAGE plpgsql; +CREATE FUNCTION direct_connections(sname varchar) +RETURNS TABLE (cname varchar, dname varchar) +AS $$ + SELECT cname, dname + FROM GRAPH_TABLE (g1 MATCH (src WHERE src.vname = sname)-[conn]->(dst) + COLUMNS (conn.ename AS cname, dst.vname AS dname)); +$$ LANGUAGE SQL; +SELECT sname, out_degree(sname) FROM GRAPH_TABLE (g1 MATCH (src IS vl1) COLUMNS (src.vname AS sname)); + sname | out_degree +-------+------------ + v11 | 3 + v12 | 1 + v13 | 1 +(3 rows) + +SELECT sname, cname, dname + FROM GRAPH_TABLE (g1 MATCH (src IS vl1) COLUMNS (src.vname AS sname)), + LATERAL direct_connections(sname); + sname | cname | dname +-------+-------+------- + v11 | e121 | v22 + v11 | e131 | v33 + v11 | e132 | v31 + v12 | e122 | v21 + v13 | e123 | v23 +(5 rows) + +-- GRAPH_TABLE joined to a regular table +SELECT * + FROM customers co, + GRAPH_TABLE (myshop2 MATCH (cg IS customers WHERE cg.address = co.address)-[IS customer_orders]->(o IS orders) + COLUMNS (cg.name_redacted AS customer_name_redacted)) + WHERE co.customer_id = 1; + customer_id | name | address | customer_name_redacted +-------------+-----------+---------+------------------------ + 1 | customer1 | US | redacted1 +(1 row) + +-- query within graph table +SELECT sname, dname + FROM GRAPH_TABLE (g1 MATCH (src)->(dest) + WHERE src.vprop1 > (SELECT max(v1.vprop1) FROM v1) + COLUMNS(src.vname as sname, dest.vname as dname)); +ERROR: subqueries within GRAPH_TABLE reference are not supported +SELECT sname, dname + FROM GRAPH_TABLE (g1 MATCH (src)->(dest) + WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) + FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) + COLUMNS(src.vname as sname, dest.vname as dname)); +ERROR: subqueries within GRAPH_TABLE reference are not supported -- leave for pg_upgrade/pg_dump tests --DROP SCHEMA graph_table_tests CASCADE; diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql index f34616163a..905be9df01 100644 --- a/src/test/regress/sql/graph_table.sql +++ b/src/test/regress/sql/graph_table.sql @@ -262,6 +262,9 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a IS vl1 | vl2) COLUMNS (a.vname, a.vprop1)); -- vprop2 is associated with vl2 but not vl3 select src, conn, dest, lprop1, vprop2, vprop1 from graph_table (g1 match (a is vl1)-[b is el1]->(c is vl2 | vl3) columns (a.vname as src, b.ename as conn, c.vname as dest, c.lprop1, c.vprop2, c.vprop1)); +-- edges directed in both ways - to and from v2 +SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS vl2)-[conn]-(dest) COLUMNS (src.vname AS sname, conn.ename AS cname, dest.vname AS dname)); +SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS vl2)-(dest) COLUMNS (src.vname AS sname, dest.vname AS dname)); -- Errors -- vl1 is not associated with property vprop2 @@ -394,5 +397,47 @@ CREATE VIEW customers_us_redacted AS SELECT * FROM GRAPH_TABLE (myshop2 MATCH (c SELECT * FROM customers_us_redacted; +-- GRAPH_TABLE in UDFs +CREATE FUNCTION out_degree(sname varchar) RETURNS varchar AS $$ +DECLARE + out_degree int; +BEGIN + SELECT count(*) INTO out_degree + FROM GRAPH_TABLE (g1 MATCH (src WHERE src.vname = sname)->() COLUMNS (src.vname)); + RETURN out_degree; +END; +$$ LANGUAGE plpgsql; + +CREATE FUNCTION direct_connections(sname varchar) +RETURNS TABLE (cname varchar, dname varchar) +AS $$ + SELECT cname, dname + FROM GRAPH_TABLE (g1 MATCH (src WHERE src.vname = sname)-[conn]->(dst) + COLUMNS (conn.ename AS cname, dst.vname AS dname)); +$$ LANGUAGE SQL; + +SELECT sname, out_degree(sname) FROM GRAPH_TABLE (g1 MATCH (src IS vl1) COLUMNS (src.vname AS sname)); +SELECT sname, cname, dname + FROM GRAPH_TABLE (g1 MATCH (src IS vl1) COLUMNS (src.vname AS sname)), + LATERAL direct_connections(sname); + +-- GRAPH_TABLE joined to a regular table +SELECT * + FROM customers co, + GRAPH_TABLE (myshop2 MATCH (cg IS customers WHERE cg.address = co.address)-[IS customer_orders]->(o IS orders) + COLUMNS (cg.name_redacted AS customer_name_redacted)) + WHERE co.customer_id = 1; + +-- query within graph table +SELECT sname, dname + FROM GRAPH_TABLE (g1 MATCH (src)->(dest) + WHERE src.vprop1 > (SELECT max(v1.vprop1) FROM v1) + COLUMNS(src.vname as sname, dest.vname as dname)); +SELECT sname, dname + FROM GRAPH_TABLE (g1 MATCH (src)->(dest) + WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) + FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) + COLUMNS(src.vname as sname, dest.vname as dname)); + -- leave for pg_upgrade/pg_dump tests --DROP SCHEMA graph_table_tests CASCADE; -- 2.34.1