DEV Community: Mohamed Mokhtar

Graph representation in relational tables

Mohamed Mokhtar — Mon, 28 Aug 2023 19:43:08 +0000

Quick overview about making graphs in relational tables representation

We will have 2 tables Vertices and edges tables.

Vertices tables:

CREATE TABLE vertices (
vertex_id integer PRIMARY KEY,
properties json
);

Edges table

CREATE TABLE edges (
edge_id integer PRIMARY KEY,
from_vertex integer REFERENCES vertices (vertex_id),
to_vertex integer REFERENCES vertices (vertex_id),
label text,
properties json
);

Index the from-to vertices ids in the edges table (for faster access)

CREATE INDEX edges_from ON edges (from_vertex);
CREATE INDEX edges_to ON edges (to_vertex);

Json operators in PostgreSQL

Some important aspects of this model are:

Any vertex can have an edge connecting it with any other vertex. There is no schema that restricts which kinds of things can or cannot be associated.
Given any vertex, you can efficiently find both its incoming and its outgoing edges, and thus traverse the graph—i.e., follow a path through a chain of vertices — both forward and backward.
By using different labels for different kinds of relationships, you can store several different kinds of information in a single graph, while still maintaining a clean data model.

Querying vertices table:

SELECT * FROM vertices WHERE properties->>'json_filed' = 'Peru';

Querying edges table

SELECT * FROM edges WHERE from_vertex = (**SELECT QUERY**) and to_vertex = (**SELECT QUERY**)

So, what are the hardest parts is it like a piece of cake? The hardest parts comes when trying to make complex query like which has multiple edges in between with specific filtration
So, keeping on mind that query in OpenCypher

MATCH
(person) -[:BORN_IN]-> () -[:WITHIN*0..]-> (us:Location {name:'United States'}),
(person) -[:LIVES_IN]-> () -[:WITHIN*0..]-> (eu:Location {name:'Europe'})
RETURN person.name

Is equivalent to that in SQL

WITH RECURSIVE
-- in_usa is the set of vertex IDs of all locations within the United States
in_usa(vertex_id) AS (
SELECT vertex_id FROM vertices WHERE properties->>'name' = 'United States'
UNION
SELECT edges.tail_vertex FROM edges
JOIN in_usa ON edges.head_vertex = in_usa.vertex_id
WHERE edges.label = 'within'
),
-- in_europe is the set of vertex IDs of all locations within Europe
in_europe(vertex_id) AS (
SELECT vertex_id FROM vertices WHERE properties->>'name' = 'Europe'
UNION
SELECT edges.tail_vertex FROM edges
JOIN in_europe ON edges.head_vertex = in_europe.vertex_id
WHERE edges.label = 'within'
),
-- born_in_usa is the set of vertex IDs of all people born in the US
born_in_usa(vertex_id) AS (
SELECT edges.tail_vertex FROM edges
JOIN in_usa ON edges.head_vertex = in_usa.vertex_id
WHERE edges.label = 'born_in'
),
-- lives_in_europe is the set of vertex IDs of all people living in Europe
lives_in_europe(vertex_id) AS (
SELECT edges.tail_vertex FROM edges
JOIN in_europe ON edges.head_vertex = in_europe.vertex_id
WHERE edges.label = 'lives_in'
)
SELECT vertices.properties->>'name'
FROM vertices
-- join to find those people who were both born in the US *and* live in Europe
JOIN born_in_usa ON vertices.vertex_id = born_in_usa.vertex_id
JOIN lives_in_europe ON vertices.vertex_id = lives_in_europe.vertex_id;

References and resources

Designing Data-Intensive Applications - Martin Kleppmann
https://www.postgresql.org/docs/9.4/functions-json.html
https://github.com/apache/age
https://age.apache.org

Internship Journey at Bitnine Global Inc. (Apache AGE)

Mohamed Mokhtar — Fri, 25 Aug 2023 11:34:27 +0000

That's not a technical blog

The End of an Internship Journey at Bitnine Global Inc.

As my internship at Bitnine Global Inc. comes to a close, I can't help but reflect on the incredible journey I've had over the past few months. It has been a transformative experience filled with growth, learning, and exciting challenges. Working at Bitnine Global Inc. has not only provided me with valuable industry exposure but has also allowed me to develop new skills, build meaningful relationships, and gain insight into the world of technology.

Embracing a Dynamic Work Environment

One of the most striking aspects of my internship at Bitnine Global Inc. was the dynamic work environment. From day one, I was immersed in a culture that encouraged creativity, collaboration, and innovation. The team fostered an atmosphere where ideas were valued, and everyone's opinions held weight. This inclusive environment allowed me to contribute my unique perspective and learn from the diverse range of expertise within the organization.

Learning from Industry Experts

During my time at Bitnine Global Inc., I had the privilege of working alongside industry experts who were passionate about their work. Their wealth of knowledge and experience served as an invaluable resource for my professional development. Whether it was through formal training resources, informal discussions, or hands-on projects, I was constantly exposed to new ideas and emerging technologies. The guidance and mentorship provided by the seasoned professionals at Bitnine Global Inc. have undoubtedly shaped my career trajectory. And also it was the first time I get introduced to the graph databases concepts and also dive into the backed of the DBMS of PostgreSQL

Tackling Real-World Challenges

One of the highlights of my internship was the opportunity to work on real-world projects. Bitnine Global Inc. entrusted me with meaningful responsibilities and allowed me to make a tangible impact. I was involved in developing innovative solutions, troubleshooting complex issues, and contributing to the company's overall objectives. This hands-on experience not only enhanced my technical skills but also taught me the importance of adaptability, problem-solving, and effective communication.

Building Lasting Connections

Beyond the valuable work experience, the internship at Bitnine Global Inc. provided me with a platform to build lasting connections. I had the pleasure of collaborating with talented individuals from diverse backgrounds, each bringing their unique strengths to the table. The strong sense of camaraderie and teamwork fostered a supportive environment where everyone was invested in each other's success. These connections extend beyond the internship period and will undoubtedly continue to be a source of inspiration and support in my professional journey. I have get connected with an awesome operation manager and team leaders I have met during my internship

Expressing Gratitude

I would like to extend my deepest gratitude to the entire Bitnine Global Inc. team for their support specially they have accepted me while I was still in the military and accepted my schedule of vacations that I can attend with doubling effort I really appreciate their support to me during all that period, guidance, and mentorship throughout my internship. Their belief in my abilities and their investment in my professional development have been instrumental in my growth. I am thankful for the opportunities I was given and the trust placed in me.

Closing Thoughts

As my internship at Bitnine Global Inc. draws to a close, I look back on this experience with a sense of accomplishment and gratitude. It has been a period of immense personal and professional growth, and I am excited to carry the lessons learned into my future endeavors. Bitnine Global Inc. has provided me with a solid foundation and set the stage for a promising career in the technology industry. I am truly grateful for the opportunity to be a part of this remarkable organization and will cherish the memories and lessons from my time here. Working on opensource projects is very good as your work and contributions during the internship will be transparent and you can show them wherever and it opens to you a door into the opensource world and community

Apply

I really recommend applying there, currently hiring from Africa (They look for interns from Egypt) [REMOTE]
Keep an eye on their LinkedIn and apply there using easy apply, if your resume passes the filtration you will get a task solve it and if you pass the tests, you will get interviewed and if you pass welcome on board. The internship period is 6 months and it is paid (good payment actually).

Under the hood of Apache AGE Makefile

Mohamed Mokhtar — Wed, 23 Aug 2023 15:39:42 +0000

Hola hola,

Today I'm going to explain the Makefile of Apache AGE and why it does look like that looking.

Firstly let's take a view of it in short view:

# License ....
MODULE_big = age

OBJS = src/backend/age.o \
       .... # some other objects
       src/backend/utils/name_validation.o

EXTENSION = age

DATA = age--1.3.0.sql

# sorted in dependency order
REGRESS = scan \
          ... all regression file names \
          name_validation \
          drop

srcdir=`pwd`

ag_regress_dir = $(srcdir)/regress
REGRESS_OPTS = --load-extension=age --inputdir=$(ag_regress_dir) --outputdir=$(ag_regress_dir) --temp-instance=$(ag_regress_dir)/instance --port=61958 --encoding=UTF-8

ag_regress_out = instance/ log/ results/ regression.*
EXTRA_CLEAN = $(addprefix $(ag_regress_dir)/, $(ag_regress_out)) src/backend/parser/cypher_gram.c src/include/parser/cypher_gram_def.h src/include/parser/cypher_kwlist_d.h

GEN_KEYWORDLIST = $(PERL) -I ./tools/ ./tools/gen_keywordlist.pl
GEN_KEYWORDLIST_DEPS = ./tools/gen_keywordlist.pl tools/PerfectHash.pm

ag_include_dir = $(srcdir)/src/include
PG_CPPFLAGS = -I$(ag_include_dir) -I$(ag_include_dir)/parser

PG_CONFIG ?= pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

src/backend/parser/cypher_keywords.o: src/include/parser/cypher_kwlist_d.h

src/include/parser/cypher_kwlist_d.h: src/include/parser/cypher_kwlist.h $(GEN_KEYWORDLIST_DEPS)
    $(GEN_KEYWORDLIST) --extern --varname CypherKeyword --output src/include/parser $<

src/include/parser/cypher_gram_def.h: src/backend/parser/cypher_gram.c

src/backend/parser/cypher_gram.c: BISONFLAGS += --defines=src/include/parser/cypher_gram_def.h

src/backend/parser/cypher_parser.o: src/backend/parser/cypher_gram.c
src/backend/parser/cypher_keywords.o: src/backend/parser/cypher_gram.c

src/backend/parser/ag_scanner.c: FLEX_NO_BACKUP=yes

installcheck: export LC_COLLATE=C

Let's break it down

Apache License

"The Apache License is a permissive free software license written by the Apache Software Foundation (ASF).[4] It allows users to use the software for any purpose, to distribute it, to modify it, and to distribute modified versions of the software under the terms of the license, without concern for royalties. The ASF and its projects release their software products under the Apache License. The license is also used by many non-ASF projects. " wiki said.

MODULE_big & others

Returning to PostgreSQL "Extension Building Infrastructure"
at chapter 38. Extending SQL, the explanation of adding an extension to their code-base is well explaining, it is mentioned that: "If you are thinking about distributing your PostgreSQL extension modules, setting up a portable build system for them can be fairly difficult. Therefore the PostgreSQL installation provides a build infrastructure for extensions, called PGXS, so that simple extension modules can be built simply against an already installed server. PGXS is mainly intended for extensions that include C code, although it can be used for pure-SQL extensions too. Note that PGXS is not intended to be a universal build system framework that can be used to build any software interfacing to PostgreSQL; it simply automates common build rules for simple server extension modules. For more complicated packages, you might need to write your own build system." PostgreSQL documentation said.

In conclusion they have mentioned that to use the PGXS infrastructure for your extension. you must write a Makefile in a specific format that sets some variables will be expected and used on the side of PostgreSQL,
In the makefile, you need to set some variables and include the global PGXS makefile, and also they provide a simple example of a Makefile that builds an extension module named isbn_issn

MODULES = isbn_issn
EXTENSION = isbn_issn
DATA = isbn_issn--1.0.sql
DOCS = README.isbn_issn
HEADERS_isbn_issn = isbn_issn.h

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Why MODULE_big?

Setting one of these three variables to specify what is built alongside PostgreSQL and obviously AGE is a single shared library so the second one was our selection

MODULES: list of shared-library objects to be built from source files with same stem (do not include library suffixes in this list)
MODULE_big: a shared library to build from multiple source files (list object files in OBJS)
PROGRAM: an executable program to build (list object files in OBJS)

Others

Variables can also be set

EXTENSION: extension name(s); for each name you must provide an extension.control file, which will be installed into prefix/share/extension
MODULEDIR: subdirectory of prefix/share into which DATA and DOCS files should be installed (if not set, default is extension if EXTENSION is set, or contrib if not)
DATA: random files to install into prefix/share/$MODULEDIR
DATA_built: random files to install into prefix/share/$MODULEDIR, which need to be built first
DATA_TSEARCH: random files to install under prefix/share/tsearch_data
DOCS: random files to install under prefix/doc/$MODULEDIR
HEADERS: Files to (optionally build and) install under prefix/include/server/$MODULEDIR/$MODULE_big.
HEADERS_built: Unlike DATA_built, files in HEADERS_built are not removed by the clean target; if you want them removed, also add them to EXTRA_CLEAN or add your own rules to do it.
HEADERS_$MODULE:
Files to install (after building if specified) under prefix/include/server/$MODULEDIR/$MODULE, where $MODULE must be a module name used in MODULES or MODULE_big.
HEADERS_built_$MODULE: Unlike DATA_built, files in HEADERS_built_$MODULE are not removed by the clean target; if you want them removed, also add them to EXTRA_CLEAN or add your own rules to do it.

It is legal to use both variables for the same module, or any combination, unless you have two module names in the MODULES list that differ only by the presence of a prefix built_, which would cause ambiguity. In that (hopefully unlikely) case, you should use only the HEADERS_built_$MODULE variables.
SCRIPTS:
script files (not binaries) to install into prefix/bin
SCRIPTS_built:
script files (not binaries) to install into prefix/bin, which need to be built first
REGRESS:
list of regression test cases (without suffix), see below
REGRESS_OPTS:
additional switches to pass to pg_regress
ISOLATION:
list of isolation test cases, see below for more details
ISOLATION_OPTS:
additional switches to pass to pg_isolation_regress
TAP_TESTS:
switch defining if TAP tests need to be run, see below
NO_INSTALL:
don't define an install target, useful for test modules that don't need their build products to be installed
NO_INSTALLCHECK:
don't define an installcheck target, useful e.g., if tests require special configuration, or don't use pg_regress
EXTRA_CLEAN:
extra files to remove in make clean
PG_CPPFLAGS:
will be prepended to CPPFLAGS
PG_CFLAGS:
will be appended to CFLAGS
PG_CXXFLAGS:
will be appended to CXXFLAGS
PG_LDFLAGS:
will be prepended to LDFLAGS
PG_LIBS:
will be added to PROGRAM link line
SHLIB_LINK:
will be added to MODULE_big link line
PG_CONFIG:
path to pg_config program for the PostgreSQL installation to build against (typically just pg_config to use the first one in your PATH)

Conclusion

It was a quick overview about the Makefile of AGE and what it is written in that form, I hope it give a some sort of new information you get out from that article.

References:

Under the hood of CREATE on AGE cypher

Mohamed Mokhtar — Sun, 13 Aug 2023 21:04:45 +0000

Hola

Greetings, continuing the series of under the hood of Apache AGE, last article was introductory to create_graph which is the first entry of any graph operation we are going to play with, so what is next is having nodes and something to work with inside that graph so we need to understand what is under the hood of the CREATE
Before going into that I would like to recommend the following articles which are good to understand

How AGE is executed on top of PostgreSQL: https://dev.to/rrrokhtar/how-age-is-executed-on-top-of-postgresql-4mpf
What's behind scenes of PostgreSQL from Apache AGE?: https://dev.to/rrrokhtar/whats-behind-scenes-of-apache-age-23f2

Overview

Apache AGE is a layer that is being extended on top of PostgreSQL through the nature of the PostgreSQL's enabled extensions and one of them which are being executed after the parsing stage of PostgreSQL we have a Parse Tree from parser and the Query tree from the Analyzer and Re-writer stage so Apache AGE is being executed at the Query tree level, it takes Query Tree and returns tuned Query Tree,

What is that tuning exactly?

From the side of AGE it starts parsing the query and looking for every function call at the query tree once it finds a function call, it checks if it is a cypher or not, if it is a cypher it starts converting it into SUB-QUERY

Our mission today is going through CREATE statement and getting know what is the equivalent SUB-QUERY it is being converted to during the conversion stage of AGE.

Let's get started

Start by looking into transform a cypher_clause function which is responsible for that


Query *transform_cypher_clause(cypher_parsestate *cpstate,
                               cypher_clause *clause)

It looks into the cypher clauses after being parsed into cypher nodes and transforms each node base on its type,
the type we are looking into is CREATE, so at some point in the function we have that

    else if (is_ag_node(self, cypher_create))
    {
        result = transform_cypher_create(cpstate, clause);
    }

That's our target is learning what is happening inside transform_cypher_create

Diving into that


static Query *transform_cypher_create(cypher_parsestate *cpstate,
                                      cypher_clause *clause)
{
    ParseState *pstate = (ParseState *)cpstate;
    cypher_create *self = (cypher_create *)clause->self;
    cypher_create_target_nodes *target_nodes;
    Const *null_const;
    List *transformed_pattern;
    FuncExpr *func_expr;
    Query *query;
    TargetEntry *tle;

    target_nodes = make_ag_node(cypher_create_target_nodes);
    target_nodes->flags = CYPHER_CLAUSE_FLAG_NONE;
    target_nodes->graph_oid = cpstate->graph_oid;

    query = makeNode(Query);
    query->commandType = CMD_SELECT;
    query->targetList = NIL;

    if (clause->prev)
    {
        handle_prev_clause(cpstate, query, clause->prev, true);

        target_nodes->flags |= CYPHER_CLAUSE_FLAG_PREVIOUS_CLAUSE;
    }

    null_const = makeNullConst(AGTYPEOID, -1, InvalidOid);
    tle = makeTargetEntry((Expr *)null_const, pstate->p_next_resno++,
                          AGE_VARNAME_CREATE_NULL_VALUE, false);
    query->targetList = lappend(query->targetList, tle);

    /*
     * Create the Const Node to hold the pattern. skip the parse node,
     * because we would not be able to control how our pointer to the
     * internal type is copied.
     */
    transformed_pattern = transform_cypher_create_pattern(cpstate, query,
                                                          self->pattern);

    target_nodes->paths = transformed_pattern;
    if (!clause->next)
    {
        target_nodes->flags |= CYPHER_CLAUSE_FLAG_TERMINAL;
    }


    func_expr = make_clause_func_expr(CREATE_CLAUSE_FUNCTION_NAME,
                                      (Node *)target_nodes);

    // Create the target entry
    tle = makeTargetEntry((Expr *)func_expr, pstate->p_next_resno++,
                          AGE_VARNAME_CREATE_CLAUSE, false);
    query->targetList = lappend(query->targetList, tle);

    query->rtable = pstate->p_rtable;
    query->rteperminfos = pstate->p_rteperminfos;
    query->jointree = makeFromExpr(pstate->p_joinlist, NULL);

    return query;
}

Function breakdown

Extending the nodes of PostgreSQL using DEFINE_NODE_METHODS through DEFINE_NODE_METHODS(cypher_create_target_nodes) And cypher_create is as the following:

typedef struct cypher_create_target_nodes
{
    ExtensibleNode extensible;
    List *paths;
    uint32 flags;
    uint32 graph_oid;
} cypher_create_target_nodes;

So that we can say that we are having a node of type cypher_create is attached to the PostgreSQL nodes and We can have a Node from that type

Assigning the target nodes' graphoid to the ParseState's graphoid and flags to NONE|NULLPTR
Make a Query node of type CMD SELECT, used in handling previous clauses

    query = makeNode(Query);
    query->commandType = CMD_SELECT;
    query->targetList = NIL;

    if (clause->prev)
    {
        handle_prev_clause(cpstate, query, clause->prev, true);

        target_nodes->flags |= CYPHER_CLAUSE_FLAG_PREVIOUS_CLAUSE;
    }

Create the Const Node to hold the pattern. skip the parse node, because we would not be able to control how our pointer to the internal type is copied. [From code comments]

    transformed_pattern = transform_cypher_create_pattern(cpstate, query, self->pattern);

target_nodes->paths = transformed_pattern;
    if (!clause->next)
    {
        target_nodes->flags |= CYPHER_CLAUSE_FLAG_TERMINAL;
    }

Creates the function expression that represents the clause. Adds the extensible node that represents the metadata that the clause needs to handle the clause in the execution phase. [From code comments], That's mean that we convert that to intermediate function expression that represents it in the final query, i.e. the final query is split up into subquries and those subquries are function calls

  func_expr = make_clause_func_expr(CREATE_CLAUSE_FUNCTION_NAME,
                                      (Node *)target_nodes);

    // Create the target entry
    tle = makeTargetEntry((Expr *)func_expr, pstate->p_next_resno++,
                          AGE_VARNAME_CREATE_CLAUSE, false);
    query->targetList = lappend(query->targetList, tle);

Finish up the query and return it

    query->rtable = pstate->p_rtable;
    query->rteperminfos = pstate->p_rteperminfos;
    query->jointree = makeFromExpr(pstate->p_joinlist, NULL);

    return query;

More explanation will be provided on part 2 about what is going on the backend in terms of the database

All functions are there:

References and resources

Under the hood of create_graph at Apache AGE

Mohamed Mokhtar — Wed, 09 Aug 2023 20:47:35 +0000

Introduction

The following article I am going to let you know more about what is going inside the Apache AGE back-end from the perspective of PostgreSQL and give you a brief introduction about the graph creation on PostgreSQL using Apache AGE.

Let's

Prerequisites

PostgreSQL
Apache AGE

if you don't have them visit my blogs and you will learn how to install them

The beginning

Mostly everything in Apache AGE is a function that is written in C and being loaded to PostgreSQL as any PL/SQL function

So, create_graph is a function and it is defined on age--1.3.0.sql for example (varies based on version being used)

That how does it look like

CREATE FUNCTION ag_catalog.create_graph(graph_name name)
RETURNS void
LANGUAGE c
AS 'MODULE_PATHNAME';

How it is implemented?


PG_FUNCTION_INFO_V1(create_graph);

/* function that is evoked for creating a graph */
Datum create_graph(PG_FUNCTION_ARGS)
{
    char *graph;
    Name graph_name;
    char *graph_name_str;
    Oid nsp_id;

    //if no argument is passed with the function, graph name cannot be null
    if (PG_ARGISNULL(0))
    {
        ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                        errmsg("graph name can not be NULL")));
    }

    //gets graph name as function argument
    graph_name = PG_GETARG_NAME(0);  

    graph_name_str = NameStr(*graph_name);

    //checking if the name of the graph falls under the pre-decided graph naming conventions(regex)
    if (!is_valid_graph_name(graph_name_str))
    {
        ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                        errmsg("graph name is invalid")));
    }

    //graph name must be unique, a graph with the same name should not exist
    if (graph_exists(graph_name_str))
    {
        ereport(ERROR,
                (errcode(ERRCODE_UNDEFINED_SCHEMA),
                 errmsg("graph \"%s\" already exists", graph_name_str)));
    }

    nsp_id = create_schema_for_graph(graph_name);

    //inserts the graph info into the relation which has all the other existing graphs info
    insert_graph(graph_name, nsp_id);  

    //Increment the Command counter before create the generic labels.
    CommandCounterIncrement();

    //Create the default label tables
    graph = graph_name->data;
    create_label(graph, AG_DEFAULT_LABEL_VERTEX, LABEL_TYPE_VERTEX, NIL);
    create_label(graph, AG_DEFAULT_LABEL_EDGE, LABEL_TYPE_EDGE, NIL);

    ereport(NOTICE,
            (errmsg("graph \"%s\" has been created", NameStr(*graph_name))));

    //according to postgres specification of c-language functions if function returns void this is the syntax
    PG_RETURN_VOID(); 
}

What are the most interesting parts

Creating a namespace (schema) for that graph

    /*
     * This is the same with running the following SQL statement.
     *
     * CREATE SCHEMA `graph_name`
     *   CREATE SEQUENCE `LABEL_ID_SEQ_NAME`
     *     AS integer
     *     MAXVALUE `LABEL_ID_MAX`
     *     CYCLE
     * The sequence will be used to assign a unique id to a label in the graph.
     *
     * schemaname doesn't have to be graph_name but the same name is used so
     * that users can find the backed schema for a graph only by its name.
     *
     * ProcessUtilityContext of this command is PROCESS_UTILITY_SUBCOMMAND
     * so the event trigger will not be fired.
     */

    nsp_id = create_schema_for_graph(graph_name);

Creates new entry on the ag_catalog at the graphs table

// INSERT INTO ag_catalog.ag_graph VALUES (graph_name, nsp_id)
// = 
    insert_graph(graph_name, nsp_id);

The following statements creates a table for holding the labels of (vertex and edges)

    graph = graph_name->data;
    create_label(graph, AG_DEFAULT_LABEL_VERTEX, LABEL_TYPE_VERTEX, NIL);
    create_label(graph, AG_DEFAULT_LABEL_EDGE, LABEL_TYPE_EDGE, NIL);

Conclusion

Create namespace (schema)
Insert into ag_catalog graphs table a new entry for it
Create Vertex labels table inside the create namespace
Create Edge labels table inside the create namespace

See you in next AGE statement under the hood

Resources and References

AGE GitHub: https://github.com/apache/age
AGE Website: https://age.apache.org/

The Next AGE of Discord

Mohamed Mokhtar — Thu, 03 Aug 2023 15:28:54 +0000

TLDR

That's an article about Graph databases gives you a very quick introduction about it and specifically Apache AGE and for the sake of understanding a very simple discord application will be implemented, I hope you enjoy reading it and gives you some new insights and information.

Pilot of the AGE

When I was a student I've heard a lot of thoughts from teachers while having the database or algorithms lectures and sections that if you are going to create a social network application database the best representation for it the graph data structure but for the SQL's being we need to normalize that graphs to fit into our tables

In the following article I am going to guide you to the truth and tell you everything they have not let us know!

Comparison

From: Designing Data Intensive Applications

Query using relational database structured query language
Query using cypher and graph database

What is going on?

Introduction to graph databases
Learn about AGE & learning a little about OpenCypher
Creating simple app demonstrating the idea (Simple Discord)

Inspiration

Our communication channel on Apache age is Discord
Some talks about the graph databases performance

Prerequisites

PostgreSQL installed
Apache AGE installed with correct version of PostgreSQL
Cypher Syntax very little knowledge is okay also I will cover some review

Brief introduction to what is the relation between AGE and PostgreSQL

That's how PostgreSQL looks like when we execute that

LOAD 'age';

Let's dive into in a very short terms what are Apache AGE and PostgreSQL and their connections

Apache AGE

It is a PostgreSQL extension that provides graph database functionality. That means we could have the relational database alongside the graph database

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

PostgreSQL and Apache AGE

As mentioned before having the extension AGE on top of PostgreSQL allowing you to enable a new feature of the graph databases on top of the structured relational databases, i.e. adding the OpenCypher query language to the SQL language into a single language.

Cypher querying review:

Create new graph

 SELECT * FROM create_graph('dev-graph');

Create nodes and edge

That query creates a node (AS n) of type/label DEVELOPER with properties name, role and department and connects it throw edge (AS e) with type/label MANAGES to another node (AS d) of type/label DEPARTMENT with properties name and returns them

Explanation:

(): Everything is wrapped with () is a node
[]: Everything is wrapped with () is an edge
[str:] : str is an alias to hold the edge or if within () the node to mention it in the rest of the query
(str: LABEL): LABEL is the category or the label of the node
{}: called properties of json type that holds any info you want to add

Query the graph

test=#  SELECT * FROM cypher('dev-graph', $$
MATCH (n:DEVELOPER)-[m:MANAGES]->(d:DEPARTMENT) return n, d
$$) as (n agtype, d agtype);
                                                                    n | d                           

+----------------
 {"id": 844424930131969, "label": "DEVELOPER", "properties": {"name": "Mark", "role": "Head of Engineering", "department": "IT"}}::vertex | {"id": 1407374883553281, "label": "DEPARTMENT", "properties": {"name":
 "IT"}}::vertex
(1 row)

Question comes to mind

Why for every cypher call should I make

SELECT * from cypher( -- bla bla bla

Why cannot I write cypher directly?

The answer in very short terms and words because AGE is an extension on top of PostgreSQL it is being injected to the PostgreSQL backend as a function call like the PL/SQL functions you are doing and while the working inside it turns into a query tree and gets involved on the query through replacing the function call with a sub query/ies based on what was inside the cypher so at the end we get a Query Tree is being executed by the executor of the PostgreSQL at the end.

SELECT * FROM (NEW QUERY INJECTED HERE)

That point introduces us to a very nice project that our team has building for the sake of user experience enhancements called AgeSQL that wraps the cypher call with the repeated portion so that you can start with the cypher query directly, you will find the link on the references

And for sure we cannot forget mentioning the project of AGE Viewer which is one of the interesting projects that is used for the graph visualizations

The being of having a single container for our data both the structured data and non structured data (Graph database alongside SQL) is one of the key features that supports the usage of AGE and specifically being part of PostgreSQL is a high valuable point.

Let's Build the APP

Setup Environment

I am going to use Python3.10 and PostgreSQL 13 alongside Apache AGE 1.3.0 (PG13 version)

PostgreSQL installation:

wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz

tar xzf postgresql-13.3.tar.gz
cd postgresql-13.3/

mkdir data
chown $(whoami) data

./configure --prefix=$(pwd) --enable-debug
make
make install

export LD_LIBRARY_PATH=$(pwd)/lib
export PATH=$PATH:$(pwd)/bin

echo export PG_PATH=$(pwd) >> ~/.bashrc
echo export LD_LIBRARY_PATH=$(pwd)/lib >> ~/.bashrc
echo export PATH=$PATH:$(pwd)/bin >> ~/.bashrc

# initialize the data directory
./bin/initdb -D ./data

# start the server
./bin/postgres -D ./data >logfile 2>&1 &

# create db named test
./bin/createdb test

./bin/psql test

AGE installation

wget https://github.com/apache/age/releases/tag/PG13/v1.3.0-
rc0
tar xzf apache-age-1.3.0-src.tar.gz
cd apache-age-1.3.0

echo export AG_PATH=$(pwd) >> ~/.bashrc

make PG_CONFIG=$(PG_PATH)/pg_config install
make PG_CONFIG=$(PG_PATH)/pg_config installcheck

psql test
CREATE EXSTENSION age;
LOAD 'age';
SELECT * FROM ag_catalog.create_graph('test');

Congrats you are having a working environment now :)

Python Environment

mkdir discord
git init
virtualenv venv
source venv/bin/activate
touch requirements.txt

Add the following (that's the same of the driver of age)

psycopg2 --no-binary :all: psycopg2
antlr4-python3-runtime==4.11.1
setuptools

Save and run pip3 install -r requirements.txt

Then install age driver

cd $(AG_PATH)/drivers/python
python setup.py install
# Go back to AG_PATH
cd $(AG_PATH)

You are now supposed having a running python AGE environment

Graph creation

Create new graph

LOAD 'age';
SET search_path = ag_catalog;
SELECT * FROM create_graph('discord');

As it is a simple discord we are going to treat the server as single channel

Schematic

User
- ID
- Username
- Full name
Server
- ID
- Name
Message
- ID
- Content

Relations

(User) -[Member]-> (Server)
(User) -[Message]-> (User|Server)

Endpoints

CREATE
- Create User
- Create Server
- Join Server
- Send Message
MATCH
- Get User
- Get Server
- Get Messages

Let's get started

touch main.py

Create connection

db.py

import psycopg2
import age
import os

graph_name = os.environ.get("GRAPH_NAME", "test")
host = os.environ.get("GRAPH_NAME", "localhost")
port = os.environ.get("PORT", "5432")
dbname = os.environ.get("DB_NAME", "test")
user = os.environ.get("USERNAME", "rrr")
password = os.environ.get("PASSWORD", "")

conn = psycopg2.connect(host=host, port=port, dbname=dbname, user=user, password=password)

if conn == None:
    raise ConnectionError("Connection to database failed")

age.setUpAge(conn, graph_name)

Setup query wrapper to avoid writing select * from every time, also at db.py

def execute_query(conn, graph_name, query, return_count=1):
    if graph_name == None:
        raise ValueError("Graph name is not provided")
    if query == None:
        raise ValueError("Query is not provided")
    as_statement = ", ".join([f"v{i} agtype" for i in range(return_count)])
    print(f"SELECT * FROM cypher('{graph_name}', $$ {query} $$) as ({as_statement});")
    with conn.cursor() as cursor:
        try:
            cursor.execute(
                f"SELECT * FROM cypher('{graph_name}', $$ {query} $$) as ({as_statement});",
            )
            for row in cursor:
                print("CREATED::", row)
            conn.commit()
            return cursor
        except Exception as ex:
            conn.rollback()
            print(type(ex), ex)

# The function that will be used on the querying as a top level 
def eq(query, rc=1):
    """
    Execute query (eq) wrapper it takes the query as an openCypher langauge
    and executes it also it requires having the count of the returns (rc: return count)

    Args:
        - query: string OpenCypher query starts which (MATCH, CREATE, etc)
        - rc: int number of the returned nodes+edges on the query default 1 return
    """
    return execute_query(conn, graph_name, query, rc)

Create user function

def create_user(name, username) -> age.Vertex:
    # Validate that user with the same username does not exist
    exisiting_user = find_user(username)
    if exisiting_user:
        raise ValueError("Duplicate username")

    cur = eq(
        f"""CREATE (u:User {{username: "{username}", name:"{name}"}}) RETURN u """,
        1,
    )
    res = cur.fetchall()
    if (len(res) > 0):
        res = res[0][0]
    cur.close()
    return res

Find user


def find_user(username) -> age.Vertex:
    cur = eq(
        f"""MATCH (u:User {{username: "{username}"}}) RETURN u """,
        1,
    )
    res = cur.fetchall()
    if len(res) > 0:
        res = res[0][0]
    cur.close()
    return res

def find_user_by_id(id) -> age.Vertex:
    cur = eq(
        f"""MATCH (u:User {{id: "{id}"}}) RETURN u """,
        1,
    )
    res = cur.fetchall()
    if len(res) > 0:
        res = res[0][0]
    else:
        res = None
    cur.close()
    return res

Create server


def create_server(name) -> age.Vertex:
    id = uuid.uuid4()
    cur = eq(
        f"""CREATE (s:Server {{name: "{name}", id:"{id}"}}) RETURN s """,
        1,
    )
    res = cur.fetchall()
    if len(res) > 0:
        res = res[0][0]
    cur.close()
    return res

Join server

def join_server(user_id, sever_id):
    cur = eq(
        f"""MATCH (u:User {{id: "{user_id}"}}), (s:Server {{id: "{sever_id}"}}) 
        CREATE (u)-[e:Member]->(s)
        RETURN e """,
        1,
    )
    if cur == None:
        raise Exception("Incorrect ids provided")
    res = cur.fetchall()
    if len(res) > 0:
        res = res[0][0]
    cur.close()
    return res

Send message to


def send_message_to(from_id, to_id, to_type, message_content):
    if to_type != "User" and to_type != "Server":
        raise ValueError("Incorrect message direction")

    from_user = find_user_by_id(from_id)
    if from_user == None:
        raise Exception("Non-exisiting from user")

    # Check authorization
    if to_type == "Server":
        cur = eq(
            f"""MATCH (u:User {{id: "{from_id}"}}) -[]- (x:Server {{id: "{to_id}"}}) RETURN u """,
            1,
        )
        res = cur.fetchall()
        if res == None:
            cur.close()
            raise Exception("Unauthorized")
        cur.close()
    else:
        to_user = find_user_by_id(to_id)
        if to_user == None:
            raise Exception("Non-exisiting from user")

    cur = eq(
        f"""MATCH (u:User {{id: "{from_id}"}}), (x:{to_type} {{id: "{to_id}"}})
          CREATE (u)-[m:Message {{content:"{message_content}", from:"{from_id}", to:"{to_id}"}}]->(x)
          RETURN m
          """,
        1,
    )
    if cur == None:
        raise Exception("Failed to create the message")
    res = cur.fetchall()
    if len(res) > 0:
        res = res[0][0]
    cur.close()
    return res

Get messages of


def get_messages_of(user_id) -> list[age.Edge]:
    cur = eq(
        f"""MATCH (u:User {{id: "{user_id}"}})-[m:Message]->(x)
        RETURN m """,
        1,
    )
    if cur == None:
        return []
    res = cur.fetchall()
    res = [r[0] for r in res]
    cur.close()
    return res

Main

from db import create_user, create_server, join_server, send_message_to, get_messages_of

if __name__ == '__main__':
  # Create user 
  user1 = create_user("Mohamed", "mohamed")

  # Create another user 
  user2 = create_user("Ahmed", "ahmed")

  # Create server
  server = create_server("AgeDB")

  # Join server
  join_server(user1.properties["id"], server.properties["id"])

  # Send message to user
  send_message_to(user1.properties["id"], user2.properties["id"], "User", "Hello Ahmed!")
  send_message_to(user1.properties["id"], user2.properties["id"], "User", "Hello Ahmed 2!")

  # Send message to server
  send_message_to(user1.properties["id"], server.properties["id"], "Server", "Hello Server!")

  # Get messages of user
  user1_messages = get_messages_of(user1.properties["id"])

  # Print messages of user
  for message in user1_messages:
      print(message.toJson())

All codes are provided in the following repository

Conclusion

In that article I have tried to give you a brief introductory to the graph databases and the ability to use it on applications as well as specifically giving an example of usage of Apache AGE alongside PostgreSQL which allows you to have relational database supported by the extension of Graph databases which makes you enjoy all of the key features of PostgreSQL and its robustness in addition to getting AgeDB beside that as well as a simple lesson about OpenCypher it has been provided also. I hope you find the article as a useful resource and gives you the initial steps of your journey with the graph databases

End of the article

Thank you for reaching here and reading my blog :)

References and resources:

AGE PG15/16 New updates

Mohamed Mokhtar — Sun, 23 Jul 2023 18:20:01 +0000

Holaa!

This post aims to keep the state of the project up to date with the participant and the other interns on the pg15-16 team.

Split up

Currently the team has split up to perform the following tasks

Keep working on pg16
Merging PG14 to dev/PG15 by hands (I've joined to)
Solving issues from AGE

Merging

Our mission is to merge the code that is currently on the branch of the PL PG14 starting from that into the dev/PG15 so that we create a new more robust release of PG15

What's wrong with the current on?

The current one has been started from an alpha branch not the actual PG14 branch so that it misses some parts and features that is not included during the merging phase so that we are asked for making new merging

How to accomplish that

Standard merging using git (will not allow the collaborative spiting the project between the team members)
Standard files merging and getting the difference between each of them using diff command, it is suggested by the PL

How can we split the project

I have create a simple suggestions that to be on the level of directories and each one will be responsible for some part of the code and then we all merge it together on a branch of one of us and then make another PR to the main dev/PG15 branch

Proposed split

regression will be only the same as dev/PG15 [complete replace without merge]
Non src directories (tools, .github, docker-entrypoint-initdb.d)
Top level files (configurations and any file exits on /age parent directory
drivers directory
src/ (catalog, commands) & /include related files
src/executor & /include related files
src/nodes and src/optimizer & /include related files
src/parser & /include related files
src/utils/cache & src/utils/load & /include related files
src/utils/adt (large directory) & /include related files

That is only a suggestions I am going to discuss with the team and the PL.

I have created a GitHub project for it:
https://github.com/users/rrrokhtar/projects/3/views/1

Resources

Does your APP need Apache AGE?

Mohamed Mokhtar — Thu, 20 Jul 2023 11:16:58 +0000

Apache AGE

It is a PostgreSQL extension that provides graph database functionality. That means we could have the relational database alongside the graph database

PostgreSQL

PostgreSQL and Apache AGE

Need those or not?

Do you need a flexible schema with relations?
Need to quickly and easily store data and analyze the relationships among data, so you can better understand the myriad of possible outcomes?
Simple querying

SELECT e.LastName, et.Description
FROM Employee AS e
JOIN EmployeeTerritory AS et ON (et.EmployeeID = e.EmployeeID)
JOIN Territory AS t ON (et.TerritoryID = t.TerritoryID);
Copy

Replaced with

MATCH (t:Territory)<-[:IN_TERRITORY]-(e:Employee)
RETURN t.description, collect(e.lastName);

Visualization: you can view your graph using age-viewer
Transactions support wrapped as it is inside a sql from statement, that feature is not widely supported by the rest available solutions of graph db

Example

Requirements

PostgreSQL installed
Apache AGE installed ###
START A PSQL SESSION AND LOAD AGE AND SET SEARCH PATH

export $DB_NAME=test
psql $DB_NAME
test=# LOAD 'age';
LOAD
test=# SET search_path = ag_catalog, "$user", public;
SET

Create graph

 SELECT * FROM create_graph('dev-graph');

Create nodes and edge

test=#  SELECT * FROM cypher('dev-graph', $$
CREATE (n:DEVELOPER {name:"Mark", role:"Head of Engineering", department:"IT"})-[m:MANAGES]->(d:DEPARTMENT {name:"IT"}) return n, m, d 
$$) as (n agtype, m agtype, d agtype);

Explanation:
- (): Everything is wrapped with () is a node
- []: Everything is wrapped with () is an edge
- [str:] : str is an alias to hold the edge or if within () the node to mention it in the rest of the query
- (str: LABEL): LABEL is the category or the label of the node
- {}: called properties of json type that holds any info you want to add
Query

test=#  SELECT * FROM cypher('dev-graph', $$
MATCH (n:DEVELOPER)-[m:MANAGES]->(d:DEPARTMENT) return n, d
$$) as (n agtype, d agtype);
                                                                    n                                                                     |                                           d                           

------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------
----------------
 {"id": 844424930131969, "label": "DEVELOPER", "properties": {"name": "Mark", "role": "Head of Engineering", "department": "IT"}}::vertex | {"id": 1407374883553281, "label": "DEPARTMENT", "properties": {"name":
 "IT"}}::vertex
(1 row)

Resources to learn more about Apache AGE:

10 Reasons Why to use Apache AGE alongside PostgreSQL

Mohamed Mokhtar — Sun, 16 Jul 2023 16:39:11 +0000

Apache AGE

AGE is an extension for PostgreSQL that allows you to run graph queries using the Cypher query language, which is commonly used in graph databases like Neo4j.
It is built by Bitnine Global Inc.

Reasons

Open-source: You can look into the code and take part into that and contribute with your ideas and suggestions.
JSONability: Apache AGE is designed to work efficiently with PostgreSQL, it uses JSONB in the properties of the vertices or edges.
Comparability: AGE is a PostgreSQL extension which allows you also working with other extensions freely.
Single Container for the structured data and non structured data (Graph database alongside SQL).
If you know Neo4j, you don't need to learn much, you already familiar with the OpenCypher.
Security: You are behaving in back of PostgreSQL which is robust enough and well maintained.
Community (Apache Project): Both PostgreSQL and Apache AGE have active and supportive communities, which can provide resources and support as you explore graph databases (We use Discord).
Enterprise support: If you are a company and looking for support solutions are provided by Bitnine.
Cloud solutions support by the company.
Easy to install :)

References:

Analysis of cypher_match at AGE16 failing testcases

Mohamed Mokhtar — Thu, 13 Jul 2023 15:47:42 +0000

Hello, what is going on?

We are currently having the following state in the test suite of Apache AGE (our regression test)

8 are failing out of 24

not ok 8 - cypher_match 1696 ms
not ok 10 - cypher_set 416 ms
not ok 11 - cypher_remove 290 ms
not ok 12 - cypher_delete 525 ms
not ok 14 - cypher_vle 1872 ms
not ok 16 - cypher_call 101 ms
not ok 17 - cypher_merge 372 ms
not ok 20 - index 232 ms

Let's discuss `cypher_match` is it all failing? Nope.

What are the cases causes the issues then?

Undefined error that we are getting empty results while it should have returned results


SELECT * FROM cypher('cypher_match', $$
    MATCH ()<-[]-(n:v2)-[]->()
    MATCH p=(n)-[]->()
    RETURN p
$$) AS (i agtype);
-- Error here is having an empty results while expecting a result set

MATCH & OPTIONAL MATCH together error

SELECT * FROM cypher('cypher_match', $$
    MATCH (u:opt_match_v)
    OPTIONAL MATCH (u)-[m]-(l)
    RETURN u.name as u, type(m), l.name as l
    ORDER BY u, m, l
$$) AS (u agtype, m agtype, l agtype);
ERROR:  graph_oid and label_id must not be null

MATCH & OPTIONAL MATCH together error

SELECT * FROM cypher('cypher_match', $$
    MATCH (n:opt_match_v), (m:opt_match_v)
    WHERE id(n) <> id(m)
    OPTIONAL MATCH (n)-[r]->(p), (m)-[s]->(q)
    RETURN n.name AS n, type(r) AS r, p.name AS p,
           m.name AS m, type(s) AS s, q.name AS q
    ORDER BY n, p, m, q
 $$) AS (n agtype, r agtype, p agtype, m agtype, s agtype, q agtype);
ERROR:  graph_oid and label_id must not be null

MATCH & OPTIONAL MATCH together error

SELECT * FROM cypher('test_retrieve_var', $$
    MATCH (a:A) WITH a
    OPTIONAL MATCH (a)-[:incs]->(c)
    WHERE EXISTS((c)<-[:incs]-())
    RETURN a, c
$$) AS (a agtype, c agtype);
ERROR:  graph_oid and label_id must not be null

Cleanup queries of the script which are dependencies of the above failed quires so that we need to fixed the above which will led to fixing them

So in conclusion we are having two types of errors (main) and one dependent:

ERROR: graph_oid and label_id must not be null That happens when having OPTIONAL MATCH with MATCH
Undefined error that we are expecting a result set and getting empty result set
Cleanup error which should be solved if we have resolved the above ones

Trials and debugging

Recalling that the problem happens in transform_cypher_optional_match_clause
so we trying to resolve the rtindex dependency problems
What has been already gone through and what I am trying now I will mention that in the next blog.

References:

What is going in AGE PG16

Mohamed Mokhtar — Sat, 08 Jul 2023 20:21:01 +0000

Hi PG16 AGE TEAM,

In our last meeting we have discussing the problems we got and one of them was that issue happens when there is a join when we use OPTIONAL MATCH that was existing in the cypher_match.sql regression tests, after digging into that issue Panagiotis has stated that in the meeting:
You will figure out the first failed script is:

not ok 8 - cypher_match 792 ms

so that we et started with

Here is the function we are trying to solve the issue within:

src/backend/parser/cypher_clause.c


/*
 * transform_cypher_optional_match_clause
 *      Transform the previous clauses and OPTIONAL MATCH clauses to be LATERAL LEFT JOIN
 *   transform_cypher_optional_match_clause   to construct a result value.
 */
static RangeTblEntry *transform_cypher_optional_match_clause(cypher_parsestate *cpstate,
                                                             cypher_clause *clause)
{
    cypher_clause *prevclause;
    RangeTblEntry *l_rte, *r_rte;
    ParseNamespaceItem *l_nsitem, *r_nsitem;
    ParseState *pstate = (ParseState *) cpstate;
    JoinExpr* j = makeNode(JoinExpr);
    List *res_colnames = NIL, *res_colvars = NIL;
    Alias *l_alias, *r_alias;
    ParseNamespaceItem *jnsitem;
    int i = 0;

    j->jointype = JOIN_LEFT;

    l_alias = makeAlias(PREV_CYPHER_CLAUSE_ALIAS, NIL);
    r_alias = makeAlias(CYPHER_OPT_RIGHT_ALIAS, NIL);

    j->larg = transform_clause_for_join(cpstate, clause->prev, &l_rte,
                                        &l_nsitem, l_alias);
    pstate->p_namespace = lappend(pstate->p_namespace, l_nsitem);

    /*
     * Remove the previous clause so when the transform_clause_for_join function
     * transforms the OPTIONAL MATCH, the previous clause will not be transformed
     * again.
     */
    prevclause = clause->prev;
    clause->prev = NULL;

    //set the lateral flag to true
    pstate->p_lateral_active = true;

    j->rarg = transform_clause_for_join(cpstate, clause, &r_rte,
                                        &r_nsitem, r_alias);

    // we are done transform the lateral left join
    pstate->p_lateral_active = false;

    /*
     * We are done with the previous clause in the transform phase, but
     * reattach the previous clause for semantics.
     */
    clause->prev = prevclause;

    pstate->p_namespace = NIL;

    // get the colnames and colvars from the rtes
    get_res_cols(pstate, l_nsitem, r_nsitem, &res_colnames, &res_colvars);

    jnsitem = addRangeTableEntryForJoin(pstate,
                                        res_colnames,
                                        NULL,
                                        j->jointype,
                                        0,
                                        res_colvars,
                                        NIL,
                                        NIL,
                                        j->alias,
                                        NULL,
                                        false);

    j->rtindex = jnsitem->p_rtindex;

    for (i = list_length(pstate->p_joinexprs) + 1; i < j->rtindex; i++)
    {
        pstate->p_joinexprs = lappend(pstate->p_joinexprs, NULL);
    }
    pstate->p_joinexprs = lappend(pstate->p_joinexprs, j);
    Assert(list_length(pstate->p_joinexprs) == j->rtindex);

    pstate->p_joinlist = lappend(pstate->p_joinlist, j);

    /* add jrte to column namespace only */
    addNSItemToQuery(pstate, jnsitem, false, false, true);

    return jnsitem->p_rte;
}

The problem is a dependency problem we need that index which is being evaluated in the end we need that at first to be correctly set

    jnsitem = addRangeTableEntryForJoin(pstate,
                                        res_colnames,
                                        NULL,
                                        j->jointype,
                                        0,
                                        res_colvars,
                                        NIL,
                                        NIL,
                                        j->alias,
                                        NULL,
                                        false);

    j->rtindex = jnsitem->p_rtindex;

The index is expected to be evaluated before that line:

    j->rarg = transform_clause_for_join(cpstate, clause, &r_rte,
                                        &r_nsitem, r_alias);

because inside that function (transform_clause_for_join) the rtindex is being set the line before return statement

static Node *transform_clause_for_join(cypher_parsestate *cpstate,
                                       cypher_clause *clause,
                                       RangeTblEntry **rte,
                                       ParseNamespaceItem **nsitem,
                                       Alias* alias)
{
    RangeTblRef *rtr;

    *nsitem = transform_cypher_clause_as_subquery(cpstate,
                                               transform_cypher_clause,
                                               clause, alias, false);
    *rte = (*nsitem)->p_rte;

    rtr = makeNode(RangeTblRef);
    rtr->rtindex = (*nsitem)->p_rtindex;

    return (Node *) rtr;
}

"Overall we are trying to fix that first and make it work don't take into consideration any hassle may be done it can be refactored after we get it work" PL said

References:

How AGE is executed on top of PostgreSQL

Mohamed Mokhtar — Wed, 05 Jul 2023 22:05:27 +0000

Hey everyone

Today I will explain to you how Apache AGE works in top of Postgresql it is considered as a complimentary part of the old blogs I have published before you can find them on my profile, lets get started!

First of all thanks to Postgresql being friendly with their extension they have taken into consideration that there are some HOOKS can be added to their source code one of them which is mainly used by Apache AGE is
post_parse_analyze_hook

What is post_parse_analyze_hook?

it is a function address assigned to NULL on Postgresql

And if it gets an address it will be called, i.e. when an extension is loaded on Postgresql session

And it has added an effect on that Postgresql variable when the queries are being executed that hook will be used.
When does it used? That's the decision of PostgreSQL not extensions' decision so that in the current time it is being called after the analyze and parse state.

What is attached to that function call?

ParseState and QueryTree and JumpleState. If you miss any part of those terminologies you can find POSTS related to them on my blog also.

What happens after Apache AGE gets that calling and what function is being used for that?

    if (post_parse_analyze_hook)
        (*post_parse_analyze_hook) (pstate, query, jstate);

Apache AGE has set post_parse_analyze_hook to post_parse_analyze
Through the following code at src/cypher/anaylze

void post_parse_analyze_init(void)
{
    prev_post_parse_analyze_hook = post_parse_analyze_hook;
    post_parse_analyze_hook = post_parse_analyze;
}

post_parse_analyze is a part of apache age and it's purpose is?

A: To change every cyhper call to subquery node
Q: How?
Post parsing: i.e. second parsing.

Go to every part of the query in the from statement (because our cypher call is being executed at the FROM part)

How to find them? In the parse state we can find them on field RangeTblEntry

For every entry of those we check them if it is

Function call
Subquery
If a function call it may be a cypher call so we need to make a check here [Simply string matching]
Got it? but why do we need to check another subqueries? because their from list may consist of a cypher call (We are solving that recursively)

What function does that conversion? convert_cypher_walker

find cypher() calls in FROM clauses and convert them to SELECT subqueries

KEEPS CHECKING a NODE if it is an RTETblEntry (the nodes on the FROM list)

if (they are function as cypher is defined as function) { 
  // check if it is cypher or not 
  if (cypher) CONVERT TO SUB_QUERY
}
if there is real existing sub query check if it contains a cypher in their FROM list or not (i.e. RECURSIVE CALL)

MISSION : UPDATES THE QUERY TREE TO ADD THE CYPHER SUB QUERIES INSTEAD OF CYPHER FUNCTION CALL

Portion of the code of convert_cypher_walker

static bool convert_cypher_walker(Node *node, ParseState *pstate)
{
    if (!node)
        return false;

    if (IsA(node, RangeTblEntry))
    {
        RangeTblEntry *rte = (RangeTblEntry *)node;

        switch (rte->rtekind)
        {
        case RTE_SUBQUERY:
            // traverse other RTE_SUBQUERYs
            return convert_cypher_walker((Node *)rte->subquery, pstate);
        case RTE_FUNCTION:
            if (is_rte_cypher(rte))
                convert_cypher_to_subquery(rte, pstate);
            return false;
        default:
            return false;
        }
    }

Thanks for reading till here I have reached to an end of today's blog I hope you feel it has given you good understanding of Postgresql extensions and AGE

                        _ _                
   __ _  ___   ___   __| | |__  _   _  ___ 
  / _` |/ _ \ / _ \ / _` | '_ \| | | |/ _ \
 | (_| | (_) | (_) | (_| | |_) | |_| |  __/
  \__, |\___/ \___/ \__,_|_.__/ \__, |\___|
  |___/                         |___/