Sync Relational Tables to PolarDB Graph Storage via Triggers - PolarDB

The ganos_graph extension keeps a graph database in sync with your relational tables automatically. When you insert, update, or delete rows in a relational table, triggers capture those changes and apply the equivalent operation to the corresponding vertices or edges in the graph — no manual synchronization needed.

Prerequisites

Before you begin, ensure that you have:

A high-privilege account (required to install the extension)
The Apache AGE extension installed and enabled. See Graph database (AGE) quick start

Install the ganos_graph extension:

CREATE EXTENSION IF NOT EXISTS ganos_graph;

Note

If you get ERROR: invalid extension name: "ganos_graph", contact us.

Create a graph with the vertex and edge labels you need:

SELECT create_graph('<graph_name>');
SELECT create_vlabel('<graph_name>', '<label_name>');
SELECT create_elabel('<graph_name>', '<label_name>');

How it works

The ganos_graph extension maps your relational schema to a property graph:

Relational concept	Graph concept
Entity table (for example, `person_table`)	Vertex label
Each row in an entity table	Vertex
Join table (for example, `lives_in_table`)	Edge label
Each row in a join table	Edge

Triggers are attached to each relational table. When a DML operation runs on the table, the trigger automatically applies the equivalent create, update, or delete operation to the graph.

Important

Always create vertex triggers before edge triggers. Edges reference vertices by their primary key, so the vertices must exist in the graph before any edge trigger fires.

Vertex triggers

Each trigger function registers a trigger on the specified table. After creation, the trigger fires automatically on every matching DML operation.

All <primary_key_field_name> parameters must be an integer type: int4 (32-bit) or int8 (64-bit).

age_create_vertex_insert_trigger

Registers an INSERT trigger that creates a new vertex whenever a row is inserted into the table.

Syntax

age_create_vertex_insert_trigger(
    '<graph_name>',
    '<label_name>',
    '<table_name>',
    '<vertex_primary_key_field_name>' DEFAULT 'id',
    '<custom_property_processing_function>'
)

The <custom_property_processing_function> converts row columns into vertex properties. If omitted, all columns are mapped as-is. When provided, use this signature:

CREATE OR REPLACE FUNCTION custom_insert_function(NEW RECORD, columns TEXT)
RETURNS JSON AS $$
BEGIN
    RETURN json_build_object('name', NEW.name);
END;
$$ LANGUAGE plpgsql;

age_create_vertex_update_trigger

Registers an UPDATE trigger that updates the corresponding vertex when a row changes.

Syntax

age_create_vertex_update_trigger(
    '<graph_name>',
    '<label_name>',
    '<table_name>',
    '<vertex_primary_key_field_name>' DEFAULT 'id',
    '<custom_property_processing_function>'
)

The <custom_property_processing_function> receives both the old and new row, so you can retain previous property values alongside the updated ones:

CREATE OR REPLACE FUNCTION custom_update_function(OLD RECORD, NEW RECORD, columns TEXT)
RETURNS JSON AS $$
BEGIN
    RETURN json_build_object('name_old', OLD.name, 'name_new', NEW.name);
END;
$$ LANGUAGE plpgsql;

age_create_vertex_delete_trigger

Registers a DELETE trigger that removes the corresponding vertex when a row is deleted.

Syntax

age_create_vertex_delete_trigger(
    '<graph_name>',
    '<label_name>',
    '<table_name>',
    '<vertex_primary_key_field_name>' DEFAULT 'id',
    '<custom_property_processing_function>'
)

The <custom_property_processing_function> runs additional logic on deletion — for example, logging deleted data or performing cleanup. Use this signature:

CREATE OR REPLACE FUNCTION custom_delete_function(OLD RECORD, columns TEXT)
RETURNS void AS $$
BEGIN
    RAISE NOTICE 'delete: %', OLD;
END;
$$ LANGUAGE plpgsql;

Edge triggers

Edge triggers work like vertex triggers but require additional parameters to identify the source and target vertex labels that the edge connects.

All <primary_key_field_name> parameters must be int4 or int8.

age_create_edge_insert_trigger

Registers an INSERT trigger that creates a new edge when a row is inserted.

Syntax

age_create_edge_insert_trigger(
    '<graph_name>',
    '<edge_label_name>',
    '<edge_table_name>',
    '<start_vertex_label_name>',
    '<end_vertex_label_name>',
    '<edge_table_primary_key_field_name>' DEFAULT 'id',
    '<start_vertex_table_primary_key_field_name>' DEFAULT 'from_id',
    '<end_vertex_table_primary_key_field_name>' DEFAULT 'to_id',
    '<custom_property_processing_function>'
)

The <custom_property_processing_function> converts row columns into edge properties:

CREATE OR REPLACE FUNCTION custom_insert_function(NEW RECORD, columns TEXT)
RETURNS JSON AS $$
BEGIN
    RETURN json_build_object('name', NEW.name);
END;
$$ LANGUAGE plpgsql;

age_create_edge_update_trigger

Registers an UPDATE trigger that updates the corresponding edge when a row changes.

Syntax

age_create_edge_update_trigger(
    '<graph_name>',
    '<edge_label_name>',
    '<edge_table_name>',
    '<start_vertex_label_name>',
    '<end_vertex_label_name>',
    '<edge_table_primary_key_field_name>' DEFAULT 'id',
    '<start_vertex_table_primary_key_field_name>' DEFAULT 'from_id',
    '<end_vertex_table_primary_key_field_name>' DEFAULT 'to_id',
    '<custom_property_processing_function>'
)

The <custom_property_processing_function> converts row columns into edge properties, and can retain property values from the old row:

CREATE OR REPLACE FUNCTION custom_update_function(OLD RECORD, NEW RECORD, columns TEXT)
RETURNS JSON AS $$
BEGIN
    RETURN json_build_object('name_old', OLD.name, 'name_new', NEW.name);
END;
$$ LANGUAGE plpgsql;

age_create_edge_delete_trigger

Registers a DELETE trigger that removes the corresponding edge when a row is deleted.

Note

Unlike the insert and update edge triggers, this function does not take <start_vertex_label_name> or <end_vertex_label_name> parameters.

Syntax

age_create_edge_delete_trigger(
    '<graph_name>',
    '<edge_label_name>',
    '<edge_table_name>',
    '<edge_table_primary_key_field_name>' DEFAULT 'id',
    '<custom_property_processing_function>'
)

The <custom_property_processing_function> runs additional logic on deletion:

CREATE OR REPLACE FUNCTION custom_delete_function(OLD RECORD, columns TEXT)
RETURNS void AS $$
BEGIN
    RAISE NOTICE 'delete: %', OLD;
END;
$$ LANGUAGE plpgsql;

Example

This example sets up triggers for a small social graph with two vertex labels (Person, City) and one edge label (LivesIn).

Relational table	Graph element
`person_table`	`Person` vertex
`city_table`	`City` vertex
`lives_in_table`	`LivesIn` edge

Create graph structure

SELECT ag_catalog.create_graph('graph_age_create_trigger');

-- Create vertex labels
SELECT ag_catalog.create_vlabel('graph_age_create_trigger', 'Person');
SELECT ag_catalog.create_vlabel('graph_age_create_trigger', 'City');

-- Create edge label
SELECT ag_catalog.create_elabel('graph_age_create_trigger', 'LivesIn');

Create relational tables

CREATE TABLE person_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT
);

CREATE TABLE city_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    population INT
);

CREATE TABLE lives_in_table (
    id SERIAL PRIMARY KEY,
    person_id INT,
    city_id INT,
    since DATE
);

Create triggers

Create vertex triggers first, then edge triggers. Edge triggers reference vertex IDs, so the vertex triggers must be in place first.

-- Person vertex triggers
SELECT age_create_vertex_insert_trigger('graph_age_create_trigger', 'Person', 'person_table');
SELECT age_create_vertex_update_trigger('graph_age_create_trigger', 'Person', 'person_table');
SELECT age_create_vertex_delete_trigger('graph_age_create_trigger', 'Person', 'person_table');

-- City vertex triggers
SELECT age_create_vertex_insert_trigger('graph_age_create_trigger', 'City', 'city_table');
SELECT age_create_vertex_update_trigger('graph_age_create_trigger', 'City', 'city_table');
SELECT age_create_vertex_delete_trigger('graph_age_create_trigger', 'City', 'city_table');

-- LivesIn edge triggers
SELECT age_create_edge_insert_trigger('graph_age_create_trigger', 'LivesIn', 'lives_in_table', 'Person', 'City', 'id', 'person_id', 'city_id');
SELECT age_create_edge_update_trigger('graph_age_create_trigger', 'LivesIn', 'lives_in_table', 'Person', 'City', 'id', 'person_id', 'city_id');
SELECT age_create_edge_delete_trigger('graph_age_create_trigger', 'LivesIn', 'lives_in_table', 'id');

Insert data

INSERT INTO person_table (name, age) VALUES ('Alice', 30);
INSERT INTO person_table (name, age) VALUES ('Bob', 25);

INSERT INTO city_table (name, population) VALUES ('New York', 8419000);
INSERT INTO city_table (name, population) VALUES ('Los Angeles', 3980000);

INSERT INTO lives_in_table (person_id, city_id, since) VALUES (1, 1, '2010-01-01');
INSERT INTO lives_in_table (person_id, city_id, since) VALUES (2, 2, '2015-06-15');

Verify that the data was propagated to the graph. The following Cypher query (run via the cypher() function) matches all vertex-edge-vertex paths and returns their properties:

SELECT * FROM cypher('graph_age_create_trigger', $$
    MATCH (p1)-[r]->(p2)
    RETURN properties(p1), properties(r), properties(p2)
$$) AS (p agtype, r agtype, p2 agtype);

Expected output:

{"id": 1, "age": 30, "name": "Alice"} | {"id": 1, "since": "2010-01-01", "city_id": 1, "person_id": 1} | {"id": 1, "name": "New York", "population": 8419000}
{"id": 2, "age": 25, "name": "Bob"}   | {"id": 2, "since": "2015-06-15", "city_id": 2, "person_id": 2} | {"id": 2, "name": "Los Angeles", "population": 3980000}

Update data

UPDATE person_table SET age = 31 WHERE id = 1;
UPDATE city_table SET population = 100000 WHERE id = 1;
UPDATE lives_in_table SET since = '2025-01-01' WHERE id = 1;

Verify the updates:

SELECT * FROM cypher('graph_age_create_trigger', $$
    MATCH (p1)-[r]->(p2)
    RETURN properties(p1), properties(r), properties(p2)
$$) AS (p agtype, r agtype, p2 agtype);

Expected output:

{"id": 1, "age": 31, "name": "Alice"} | {"id": 1, "since": "2025-01-01", "city_id": 1, "person_id": 1} | {"id": 1, "name": "New York", "population": 100000}
{"id": 2, "age": 25, "name": "Bob"}   | {"id": 2, "since": "2015-06-15", "city_id": 2, "person_id": 2} | {"id": 2, "name": "Los Angeles", "population": 3980000}

Delete data

Delete the edge first, then the vertex. Edges that reference a vertex must be removed before the vertex itself is deleted.

DELETE FROM lives_in_table WHERE person_id = (SELECT id FROM person_table WHERE name = 'Bob' LIMIT 1);
DELETE FROM person_table WHERE name = 'Bob';

Verify the deletions:

SELECT * FROM cypher('graph_age_create_trigger', $$
    MATCH (p1)-[r]->(p2)
    RETURN properties(p1), properties(r), properties(p2)
$$) AS (p agtype, r agtype, p2 agtype);

Expected output:

{"id": 1, "age": 31, "name": "Alice"} | {"id": 1, "since": "2025-01-01", "city_id": 1, "person_id": 1} | {"id": 1, "name": "New York", "population": 100000}