Prepare

  • Current AgensGraph version is released lastest 2.1 version.
  • I will compare 2.0 and 2.1 version
  • Installation (AgensGraph v.2.0 and AgensGraph v.2.1)

Current problem

Below view cypher query, this graph pattern is two ‘Vertex’ inside one ‘Edge’ pattern.

This pattern is called by cypher query language.

The cypher query below works fine with version 2.0.

but, 2.1 release version does not work correctly. Aggregate function was not executed. (e.g count())

Cypher doesn’t have explicit group-by, instead, the grouping key is formed from the non-aggregation columns in scope.

query (2.0 under version)

match (a)-[r]->(b)
with graphid_labid(start(r)) as s_oid, graphid_labid(id(r)) as e_oid, graphid_labid("end"(r)) as t_oid
return e_oid, s_oid, t_oid, count(*) as cnt;

 e_oid | s_oid | t_oid |   cnt
-------+-------+-------+---------
     3 |    10 |    10 |      90
     3 |    13 |    13 |    5665
     3 |    14 |    14 |    2847
     3 |    15 |    15 |    5222
    27 |    13 |     7 |   45917
    27 |    13 |    21 |   65352

query (2.1 version)

  • Although the cypher query is equals, not operate grouping. Alt text

SOLUTION

1) you meta_table(called by ag_graphmeta) available from AgensGraph 2.0 release

SELECT * 
FROM   pg_catalog.ag_graphmeta
WHERE  graph = (SELECT oid FROM pg_catalog.ag_graph WHERE graphname = 'movie');

 graph | edge | start | end | edgecount
-------+------+-------+-----+----------
 16816 |    5 |     1 |   3 |         6
 16816 |    7 |     4 |   3 |        15
 16816 |    8 |     4 |   3 |        10
 16816 |    5 |     4 |   3 |       165
 16816 |    6 |     4 |   3 |        44
 16816 |   10 |     4 |   3 |         9
 16816 |    9 |     4 |   4 |         3
(7 rows)
  • postgresql oid is Object Identifier. It is hidden.
  • ‘movie’ is graph_path name. if you graph_path don’t know, console login and enter ‘\dG’

2) Hybrid query search executing. (include group by)

SELECT    e_oid, s_oid, t_oid, count(*) AS cnt
FROM      (match (a)-[r]->(b)
          with graphid_labid(start(r)) as s_oid, graphid_labid(id(r)) as e_oid, graphid_labid("end"(r)) as t_oid
          return e_oid, s_oid, t_oid) t1 
GROUP BY  e_oid, s_oid, t_oid;

 e_oid | s_oid | t_oid | cnt
-------+-------+-------+-----
     5 |     4 |     3 | 165
     7 |     4 |     3 |  15
     5 |     1 |     3 |   6
     6 |     4 |     3 |  44
     9 |     4 |     4 |   3
     8 |     4 |     3 |  10
    10 |     4 |     3 |   9
(7 rows)