Is there a way to add PostGis data as property to a node (e.g., a point geometry)?

I've tried:

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: ST_SetSRID(ST_MakePoint(-71.10, 42.32),4326)}) return n
$$) as (a agtype);
ERROR:  function ag_catalog.age_st_makepoint(agtype, agtype) does not exist
LINE 2: ... (n:Test {id: 1, location: ST_SetSRID(ST_MakePoint(-71.10, 4...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: public.ST_SetSRID(public.ST_MakePoint(-71.10, 42.32),4326)}) return n
$$) as (a agtype);
ERROR:  function public.ST_MakePoint(agtype, agtype) does not exist
LINE 2: ...1, location: public.ST_SetSRID(public.ST_MakePoint(-71.10, 4...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: public.ST_SetSRID(public.ST_MakePoint(-71.10::float, 42.32::float),4326)}) return n
$$) as (a agtype);
ERROR:  ag function does not exist
LINE 1: SELECT * FROM cypher('testgraph', $$
                                           ^
DETAIL:  agtype_typecast_float(1)

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: public.ST_GeomFromText('POINT(-71.10, 42.32)', 4326)}) return n
$$) as (a agtype);
ERROR:  function public.ST_GeomFromText(agtype, agtype) does not exist
LINE 2: ...E (n:Test {id: 1, location: public.ST_GeomFromText('POINT(-7...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: public.ST_GeomFromText('POINT(-71.10, 42.32)'::text, 4326)}) return n
$$) as (a agtype);
ERROR:  typecast 'text' not supported
LINE 1: SELECT * FROM cypher('testgraph', $$
                                           ^

Additional information: the indivial extensions (age, postgis) do work:

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1}) return n
$$) as (a agtype);
                                     a                                      
----------------------------------------------------------------------------
 {"id": 1688849860263978, "label": "Test", "properties": {"id": 1}}::vertex
(1 row)

SELECT ST_SetSRID(ST_MakePoint(-71.10, 42.31),4326) as location;
                      location                      
----------------------------------------------------
 0101000020E61000006666666666C651C048E17A14AE274540
(1 row)

SELECT PostGIS_Version();
            postgis_version            
---------------------------------------
 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

I'd like to see some actual examples. A tutorialish use case if you will. And then, esp here for AGE, to have that example include a mix of Cypher and SQL, using the right subdomain language for each part of an overall interaction with the db.

How are graph edges and nodes exposed to the postgresql type system? Can graph queries / manipulation be done in stored procedures like pl/pgsql? How do relational tables and graphs interact?

Original Post in r/postgreSQL
https://www.reddit.com/r/PostgreSQL/comments/lv9u7s/postgresql_with_graph_database_features/gperw36?utm_source=share&utm_medium=web2x&context=3

I'm trying to do a relatively basic MATCH with a WHERE clause but I keep encountering an error per:

ERROR: operator is not unique: ag_catalog.agtype = ag_catalog.agtype
Hint: Could not choose a best candidate operator. You might need to add explicit type casts. Position: 93

The item and its item property exists in the graph.

This is the query:

SELECT *
FROM ag_catalog.cypher('my_test_graph', $$
    MATCH (n:some_label)
    WHERE n.pkey = 'some123string456'
    RETURN n.pkey
$$) as (v ag_catalog.agtype);

Where some_label and some123string456 correspond to the vertex labels and properties in the graph.

The pkey property is stored as a string.

I've tried adding explicit type casts, e.g. ::text and ::char but these return the following errors:

ERROR: typecast 'text' not supported

When I reframe the query per:

SELECT *
FROM ag_catalog.cypher('my_test_graph', $$
    MATCH (n:some_label {pkey:  'some123string456'})
    RETURN n.pkey
$$) as (v ag_catalog.agtype);

then it works.

Well, It's a kind of silly thing. And not a bug in AGE. But who is a newcomer, like me, maybe helpful information.
Maybe we'll test some queries in the official pdf guide through opened browser, but some troubles in a specific browser which is firefox.

If you just copy & paste below query throughout browser, like this query, maybe you've got some error like this
Query : SELECT​ *FROM​ cypher(​'graph_name'​, $$​CREATE​ (n), (m)$$) ​as​ (v agtype);

1. ERROR: syntax error at or near "SELECT​"
2. ERROR: cache lookup failed for type 0 at character 28

I got some errors like these. I found nothing helpful and why these errors i've gotten.

Because you copy the query in pdf on firefox, you just got extended-ASCII code together.

firefox copy-paste

1. query: SELECT​ * FROM​ cypher(​'a'​, $$​ CREATE​ (n), (m) $$ ) ​as​ (v agtype);
2. hex : 53 45 4c 45 43 54 e2 80 8b 20 2a 20 46 52 4f 4d e2 80 8b 20 63 79 70 68 65 72 28 e2 80 8b 27 61 27 e2 80 8b 2c 20 24 24 e2 80 8b 20 43 52 45 41 54 45 e2 80 8b 20 28 6e 29 2c 20 28 6d 29 20 24 24 20 29 20 e2 80 8b 61 73 e2 80 8b 20 28 76 20 61 67 74 79 70 65 29 3b

other browsers copy-paste

1. query: SELECT * FROM cypher('graph_name', $$ CREATE (n), (m) $$) as (v agtype);
2. hex : 53 45 4c 45 43 54 20 2a 0d 0a 46 52 4f 4d 20 63 79 70 68 65 72 28 27 67 72 61 70 68 5f 6e 61 6d 65 27 2c 20 24 24 0d 0a 43 52 45 41 54 45 20 28 6e 29 2c 20 28 6d 29 0d 0a 24 24 29 20 61 73 20 28 76 20 61 67 74 79 70 65 29 3b

The error point is "e2 80 8b" hex code which means "ZERO WIDTH SPACE". It's completely transparent. but you can backspace that. You can check what it is in utf-8 chart

Maybe adding browser recommendations will avoid what have I done.

I just say, In short, do not copy queries in Apache_AGE_Guide.pdf on firefox!

I wonder what AGE is different from AgensGraph

Is there any graph visualization tool that can be integrated with AGE?

From document, default query is here.

select * from cypher('test_graph', $$ match(v:Part) return v $$) as (result agtype)
That result of type is 'agtype'.

In this situation, I create basic query using 'with'.

with a as(select * from cypher('test_graph', $$ match(v:Part) return v $$) as (result agtype)) select a from a

Result of datatype of this query is 'record' as a.

So, I have a some questions.

1. Can I cast default datatype except on 'record'?
2. In this query, how can I get specific data(ex. select a.id as id)?

I noticed roadmap of age.apache.org/# has not updated since... well since it was made. I think updating at least the roadmap can clear some misunderstandings for the new users. (it's March 5th already and AGE is still v0.3.0, not v1.0.0!)
Also, to let the people know this is an active, ongoing project, I think adding some SNS links related to Apache AGE can be helpful as well. Updating SNS posts are easier and faster than regularly updating a homepage, right?

Is this compatible with the citus extension? I am currently using janusgraph. This looks interesting but I am worried how it would scale if I can't use citus.

Assigning in edge to vertex label.

SELECT * FROM cypher('a', $$
MATCH (b), (c) 
WHERE  id(b) = 4222124650659842 and id(c) = 4222124650659843 
CREATE (b)-[a: already_created_vertex_label_name { name:'abc'}]->(c) 
return id(a)
$$) as (a agtype);

Assigning in vertex to edge label.

SELECT * FROM cypher('a', $$
create (a: already_created_edge_label_name { name:'abc'})
return id(a)
$$) as (a agtype);

Both of them, print.

        server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

I think.. some error handling logics or guidance help in using ages much better.

Thanks for reading and have a nice day.

• Case sensitivity for label names

Create label name with the first letter of upper case
SELECT *
FROM cypher('graph_name', $$
CREATE (:Person{name: 'Andres', title: 'Developer'})
$$) as (n agtype);

Search by lowercase label name -> "label person does not exists" error
SELECT *
FROM cypher('graph_name', $$
match(a:person)
RETURN a
$$) as (a agtype);
-> label person does not exists

Is it a normal case ?

When will agcloud.bitnine.net offer cloud access to AGE?
The current version (Postgresql 10.4) does not have the AGE extension.

Hello,

Besides PostgreSQL 11 configuration file, is there any configuration file that comes (or gets installed) specifically with AGE?
I am asking this because we are developing an web-based database admin application for both AgensGraph and AGE (on PostgreSQL 11).

Thanks

Hi, I was able to use some examples to create records such as:

SELECT q.a
FROM (SELECT *
      FROM cypher('test_graph', $$
  MATCH (a:Part)
  RETURN a
$$) AS (a agtype)
     ) as q;

Which returns:

a
"{""id"": 844424930131974, ""label"": ""Part"", ""properties"": {""part_num"": ""123""}}::vertex"
"{""id"": 844424930131975, ""label"": ""Part"", ""properties"": {""part_num"": ""345""}}::vertex"
"{""id"": 844424930131976, ""label"": ""Part"", ""properties"": {""part_num"": ""456""}}::vertex"
"{""id"": 844424930131977, ""label"": ""Part"", ""properties"": {""part_num"": ""789""}}::vertex"

But, it's unclear to me how I would join these results against typical relational concepts. Would someone be willing to share an example or point me at documentation for this?

Hi

I found a bug that couldn't be deleted edge while I was testing 'create, delete, remove, drop ...' at Python & Go.

So I tried again at pgadmin and the result was the same.

select * from cypher('test_graph', $$ match(a:person)-[b:knows]->(c:person) return b $$) as (a agtype) 
result :
"{"id": 1125899906842625, "label": "knows", "end_id": 844424930131970, "start_id": 844424930131969, "properties": {}}::edge"

select * from cypher('test_graph', $$ match(a:person)-[b:knows]->(c:person) delete b $$) as (a agtype) 
result :
ERROR:  ag function does not exist
LINE 1: select * from cypher('test_graph', $$ match(a:person)-[b:kno...
                                           ^
DETAIL:  _cypher_delete_clause(1)
SQL state: XX000
Character: 37

how to delete edge?

/src/backend/utils/adt/agtype_util.c line 1536 Non-boolean value returned from function returning bool
static bool equals_agtype_scalar_value(agtype_value *a, agtype_value *b)
{
if (a->type == b->type)
{
switch (a->type)
{
case AGTV_NULL:
return true;
case AGTV_STRING:
return length_compare_agtype_string_value(a, b) == 0;
case AGTV_NUMERIC:
return DatumGetBool(DirectFunctionCall2(
numeric_eq, PointerGetDatum(a->val.numeric),
PointerGetDatum(b->val.numeric)));
case AGTV_BOOL:
return a->val.boolean == b->val.boolean;
case AGTV_INTEGER:
return a->val.int_value == b->val.int_value;
case AGTV_FLOAT:
return a->val.float_value == b->val.float_value;
case AGTV_VERTEX:
{
graphid a_graphid, b_graphid;
a_graphid = a->val.object.pairs[0].value.val.int_value;
b_graphid = b->val.object.pairs[0].value.val.int_value;

        return a_graphid == b_graphid;
    }

    default:
        ereport(ERROR, (errmsg("invalid agtype scalar type %d for equals",
                               a->type)));
    }
}
ereport(ERROR, (errmsg("agtype input scalars must be of same type")));
return -1; // <- should be bool

}

I have packaged AGE 0.2.0 in nixpkgs. I would like to update the package to 0.3.0, but there is no git tag for this version (for GitHub projects, we build based on a release tag), only 0.3.0 release candidate 0 seems to be tagged. Would it be possible to create a tag for the commit corresponding to version 0.3.0?

Hello, and hi5 to all AGE community!

Please consider adding LXC/Snap and/or Kata as additional containerisation option for AGE project... as many Web Hosts (incl. both of mine) restrict or do not support Docker virtualisation, but freely support other -v methods... due to their Hosting Virtualisation Mechanism...

So please, make AGE happen for low end VPS users. Thanks !

When I try to use asyncpg with query arguments, I get following error: asyncpg.exceptions.PostgresSyntaxError: unexpected character at or near "$"

Python reproduction code:

import asyncio
import asyncpg


async def main():
    # Don't use prepared statements (see https://github.com/apache/incubator-age/issues/28)
    pool = await asyncpg.create_pool('postgresql://testuser:[email protected]:5433/testdb', statement_cache_size=0)
    async with pool.acquire() as conn:
        await conn.execute(
            '''
                SET search_path = ag_catalog, "$user", public;
            '''
        )

        await conn.execute(
            '''
                SELECT create_graph('testgraph');
            '''
        )

        await conn.execute(
            '''
                SELECT * FROM cypher('testgraph', $$CREATE (v:Person {name: $1})$$) as (a agtype);
            ''',
            'Tom',
        )

asyncio.get_event_loop().run_until_complete(main())

When trying to do the same with node with node-postgres, I get a similar error: error: unexpected character at or near "$"

Typescript reproduction code:

import {Client} from "pg";

const config = {
    user: 'testuser',
    host: '127.0.0.1',
    database: 'testdb',
    password: 'testpass',
    port: 5433,
}

const main = async () => {
    const client = new Client(config);
    await client.connect();

    await client.query(`SET search_path = ag_catalog, "$user", public;`);
    await client.query(`SELECT create_graph('testgraph');`);

    await client.query(
        `SELECT * FROM cypher('testgraph', $$CREATE (v:Person {name: $1})$$) as (a agtype);`,
        [
            'Tom'
        ]
    ).catch(e => console.log(e));
}

main()

Is it possible to use query arguments in another way with the Dollar-Quoted String Constants incubator-age is using, or is there another easy solution to prevent sql injections when using user input?

explain
SELECT *
FROM cypher('graph_name', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Node A' AND b.name = 'Node B'
with a,b
match(a)-[e:RELTYPE]->(b)
where b.born > 1999
RETURN a
$$) as (a agtype);
QUERY PLAN

Function Scan on cypher (cost=0.00..10.00 rows=1000 width=32)
(1 row)

Explain about Cypher is simply displayed as Function Scan on Cypher.
How can I see the plan information for the Cypher statement?

In order to connect to the database, the following syntax was executed to execute the cypher syntax by default.

alter database testdb set search_path to ag_catalog, "$user", public;

After that, if you access the database and execute the cypher query, the following error occurs.

1. alter database testdb set search_path to ag_catalog, "$user", public;
2. \q
3. psql testdb -p 5400

testdb=# SELECT *
testdb-# FROM cypher('graph_name', $$
testdb$# match(a:Person)
testdb$# RETURN a
testdb$# $$) as (a agtype);
ERROR: unhandled cypher(cstring) function call

5. try again -> normal execute
   testdb=# SELECT *
   testdb-# FROM cypher('graph_name', $$
   testdb$# match(a:Person)
   testdb$# RETURN a
   testdb$# $$) as (a agtype);
   a

{"id": 844424930131970, "label": "Person", "properties": {"name": "Andres"}}::vertex
{"id": 844424930131971, "label": "Person", "properties": {"name": "John"}}::vertex
{"id": 844424930131973, "label": "Person", "properties": {}}::vertex
{"id": 844424930131974, "label": "Person", "properties": {}}::vertex
{"id": 844424930131972, "label": "Person", "properties": {"a": "c", "id": 1, "title": "1"}}::vertex
(5 rows)

Is there a problem or am I missing something?

I'm probably doing something wrong here, but for some reason I cannot get data created in a transaction to be seen in the same transaction. It is only seen after commit. For example:

cmdb=# begin;
BEGIN
cmdb=*# SELECT * FROM cypher('cmdb', $$ CREATE (a:Part {part_num: '678'}) $$) as (a agtype);
 a 
---
(0 rows)
cmdb=*# SELECT * FROM cypher('cmdb', $$ MATCH (a:Part {part_num: '678'}) RETURN a $$) as (a agtype);
 a 
---
(0 rows)
cmdb=*# commit;
COMMIT
cmdb=# SELECT * FROM cypher('cmdb', $$ MATCH (a:Part {part_num: '678'}) RETURN a $$) as (a agtype);
                                          a                                          
-------------------------------------------------------------------------------------
 {"id": 844424930131973, "label": "Part", "properties": {"part_num": "678"}}::vertex
(1 row)

Am I doing something wrong here or is this a bug?

Hello.

i trying to use AGE with JDBC.

and, i faced failure when Message Flow is extended query mode with CREATE clause.
has any solution with this issue or guide?

cheers.

failure query

SELECT * from cypher('my_graph_name', $$
  CREATE (a:Part {part_num: '123'}), 
         (b:Part {part_num: '345'}), 
         (c:Part {part_num: '456'}), 
         (d:Part {part_num: '789'})
$$) as (a agtype);

error message

Type : Error
Length : 98
Severity: ERROR
Code: XX000
Message: unrecognized node type : -1944838080
File : copyfuncs.c
Line: 5658
Routine: copyObjectImpl

environment
Docker : sorrell/agensgraph-extension-alpine

Cheers.

reference link : https://www.postgresql.org/docs/12/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

After creating a new database connection, the first cypher query results in a ERROR: unhandled cypher(cstring) function call error. This error does not occur on subsequent cypher queries, or if a create_graph query has been executed prior to the cypher query.

How to reproduce:

• Set up database

vagrant@local:~$ sudo -u postgres createdb testdb
vagrant@local:~$ sudo -u postgres psql -d testdb -c "CREATE EXTENSION age;"
CREATE EXTENSION
vagrant@local:~$ sudo -u postgres psql -d testdb -c "LOAD 'age';"
LOAD

• First connection to create a graph and add some data. Here, the cypher query succeeds.

vagrant@local:~$ sudo -u postgres psql -d testdb
psql (11.7 (Debian 11.7-0+deb10u1), server 11.10 (Debian 11.10-0+deb10u1))
Type "help" for help.

testdb=# SET search_path = ag_catalog, "$user", public;
SET
testdb=# SELECT create_graph('testgraph');
NOTICE:  graph "testgraph" has been created
 create_graph 
--------------
 
(1 row)

testdb=# SELECT * from cypher('testgraph', $$ CREATE (a:Person {name: 'Tom'}) $$) as (a agtype);
 a 
---
(0 rows)

testdb=# \q

• Second connection to query the graph. The first cypher query fails, the second query succeeds.

vagrant@local:~$ sudo -u postgres psql -d testdb
psql (11.7 (Debian 11.7-0+deb10u1), server 11.10 (Debian 11.10-0+deb10u1))
Type "help" for help.

testdb=# SET search_path = ag_catalog, "$user", public;
SET
testdb=# SELECT * FROM cypher('testgraph', $$MATCH (p) return p$$) as (a agtype);
ERROR:  unhandled cypher(cstring) function call
DETAIL:  testgraph
testdb=# SELECT * FROM cypher('testgraph', $$MATCH (p) return p$$) as (a agtype);

                                         a                                         
-----------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Person", "properties": {"name": "Tom"}}::vertex
(1 row)

(END)

testdb=# \q

• Third connection to add more data. The first cypher query again fails, the second query again succeeds.

vagrant@local:~$ sudo -u postgres psql -d testdb
psql (11.7 (Debian 11.7-0+deb10u1), server 11.10 (Debian 11.10-0+deb10u1))
Type "help" for help.

testdb=# SET search_path = ag_catalog, "$user", public;
SET
testdb=# SELECT * from cypher('testgraph', $$ CREATE (a:Person {name: 'Ann'}) $$) as (a agtype);
ERROR:  unhandled cypher(cstring) function call
DETAIL:  testgraph
testdb=# SELECT * from cypher('testgraph', $$ CREATE (a:Person {name: 'Ann'}) $$) as (a agtype);
 a 
---
(0 rows)

testdb=# \q

when use make with -j option like make -j16 throws error with cypher_gram_def.h

on first trying make,

.//src/include/parser/cypher_gram.h:66:10: fatal error: parser/cypher_gram_def.h: no such file or directory
   66 | #include "parser/cypher_gram_def.h"
      |          ^~~~~~~~~~~~~~~~~~~~~~~~~~

on twice trying make,

src/backend/parser/cypher_gram.c:147:10: fatal error: cypher_gram_def.h: no such file or directory
  147 | #include "cypher_gram_def.h"
      |          ^~~~~~~~~~~~~~~~~~~

it seems trying to compile, before bison generate codes.
so, how about change Makefile like behind?

PS, looks like bison not generating with directory part prefix since Bison 3.7.
so, I added CPPFLAGS for include header without prefix.
PG_CPPFLAGS = -I$(ag_include_dir) -I$(ag_include_dir)/parser

I don't think this is a good way. therefore, how about giving options according to the version or falling old version of bison.

Directive: %define api.header.include {"header.h"}
History: Introduced in Bison 3.4. Defaults to ‘"basename.h"’ since Bison 3.7, unless the header file is y.tab.h.
https://www.gnu.org/software/bison/manual/html_node/_0025define-Summary.html

build log
https://travis-ci.com/github/emotionbug/incubator-age/jobs/512275520

74c74
< 	  cypher_delete \
---
>           cypher_delete \
82c82
< EXTRA_CLEAN = $(addprefix $(ag_regress_dir)/, $(ag_regress_out))
---
> EXTRA_CLEAN = $(addprefix $(ag_regress_dir)/, $(ag_regress_out)) src/backend/parser/cypher_gram.c src/include/parser/cypher_gram_def.h
85c85
< PG_CPPFLAGS = -I$(ag_include_dir)
---
> PG_CPPFLAGS = -I$(ag_include_dir) -I$(ag_include_dir)/parser
91c91,96
< src/backend/parser/cypher_gram.c: BISONFLAGS += --defines=$(ag_include_dir)/parser/$(basename $(notdir $@))_def.h
---
> 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

Hi,

I work on graph database, mainly Neo4j, I'd like to quit, for several years. I'm a strong supporter of PostgreSQL for almost 20 years.
I think your idea to use PostgreSQL to support Cypher is great, but I fear that you'd miss the target for a silly issue.

One of the main advantage of Neo4J is there's an API to create node, relationships and so on by API.
Sometimes, Cypher is not the good answer in certain kind of apps and architecture, at least to create the graph. Of course, to query the graph, Cypher is great.

So my proposition is simple : provide an API, accessible from PL/PGSQL to create nodes, relationships at least, and one day to permit people to define ways to walk in the graph.

Many thanks for your work.

@Kim-Changhyun @emotionbug
Guys, does the current AGViewer (available at https://github.com/bitnine-oss/AGViewer) support AGE 0.4.0?

I saw in the presentation https://www.youtube.com/watch?v=32w_iVL8qN4
that there is a driver for Go... but I couldn't find it. I would really like to try AGE and need a Go driver.

These are on their way out in PostgreSQL. Is there something compelling about their use in AGE, and if so, what?

Hi nice to meet you AGE.

As a AGE beginner, I have a question, so submitted issue.

When I run pgadmin, I have to load 'age' extension every time(AGE is installed by docker).

So, is possible way to load age extension automatically?

I can't found how to do..

I want to querying label as row. but, i can't find label function like AgensGraph or Neo4j.
so, currently using follow function, ag_catalog._label_name(oid, v | e) like behind.

i think it is not suitable to use.

so, any other ways to querying to get label string?

-- $schema_name
SELECT label, count(label) as cnt
FROM (
         SELECT ag_catalog._label_name(oid, v)::text as label
         from cypher('$schema_name', $$
             MATCH (V)
             RETURN id(V)
             $$) as (V agtype), (SELECT oid FROM ag_catalog.ag_graph where name = '$schema_name') as oid
     ) b
GROUP BY b.label;

Hello AGE team,

I am a data analyst trying to install AGE extension for my PostgreSQL v13 and lack of database admin expertise. Here is what I have gone through so far.

• Had PostgreSQL v13 installed on my windows laptop and have been using it for quite a while
• Recently downloaded the apache-age-0.4.0-incubating-src.tar file
• Updated the path environment variable by adding this D:\Program Files\PostgreSQL\13\bin\pg_config
• Have also downloaded the Apache AGE Guide pdf but it doesn't mention how to install on windows. Therefore, I got stuck there and have no idea how to proceed.

Looking forward to your guidance.

Thank you very much.

I installed the Apache AGE PostgreSQL extension on Debian Buster using the instructions on https://age.apache.org/ (under Documentation -> Installing AGE).

I was able to CREATE EXTENSION age;, LOAD 'age'; and SET search_path = ag_catalog, "$user", public; as a superuser and subsequently create a graph and execute cypher queries.

Unfortunately, I was unable to execute LOAD 'age'; as non-superuser.

I tried moving /usr/lib/postgresql/11/lib/age.so to /usr/lib/postgresql/11/lib/plugins/age.so (as mentioned in https://www.postgresql.org/docs/11/sql-load.html) and was then able to execute load '$libdir/plugins/age.so';, but then I got following errors when trying to use AGE functions create_graph and cypher:

=> SELECT create_graph('g');
ERROR:  function create_graph(unknown) does not exist
LINE 1: SELECT create_graph('g');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

=> SELECT * from cypher('g', $$ CREATE (a:Person {name: 'Tom'}) $$) as (a agtype);
ERROR:  function cypher(unknown, unknown) does not exist
LINE 1: SELECT * from cypher('g', $$ CREATE (a:Person {name: 'Tom'})...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Is it currently only possible to execute AGE functions as a superuser, or do I need to do additional configuration in order to make them available to non-superusers?

Hello there, I got some trouble in using JDBC preparestatement. So I want to share about my experiences.

I have been confused rather am I doing something wrong or is it a bug. So, I just write down those phenomena.

1. Querystring

   1. PrepareStatement

      PreparedStatement pstmt  = connectionHelper.createConnection().prepareStatement(
      	"select * from cypher('a', $$ "+
      	"match (a) where id(a) = ? return properties(a)" +
      	"$$) as (a agtype););"
      pstmt.setString(1, "281474976710667");  //character '?' is replaced to 281474976710667

   2. Prepared Statements Preparation & Execution in "Apache_AGE_Guide.pdf"

      PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
                  "PREPARE cypher_stored_procedure(agtype) AS  " +
                  "select * " +
      			"from cypher('a', $$ " +
      			"match (a)  " +
      			"where a.id = $id " +
      			"return properties(a) " +
      			"$$, $1) as (a agtype);" +
      			"EXECUTE cypher_stored_procedure(('{\"id\": \" ? \"}'))");
      pstmt.setString(1, "844424930131976");  // character '?' is replaced to 844424930131976

2. Error message

   Both of them got the same out of index error. because they aren't replaced to other strings which is what we want to replace. (org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.)

3. Suggestion

   I asked about these errors to a coworker and understood why those errors were thrown. Because of accurate error showing, you guys did so.

   I concluded dollar-quoted strings are not to be replaced now. But JDBC standards compatibility is a kind of important thing about normal users. And AGE is an extension of Postgres. So how about some another route about preparestatement supporting?

4. Conclusion

   I am waiting for your opinion. or maybe you want some specific things about this article. then, feel easy to tell me. I would happily append more detailed info. If I got something miss or wrong, just let me know.

Many thanks..

Background

The ExecMaterial node in an Execution Tree may not rescan it's children nodes. The material node acts as an intermediary between it's parent and it's children. During the initial run, MaterialNode will execute its subplans, cache the results in a data structure called a Tuplestorestate (postgres has only exposed the name of, the structure is hidden in Tuplestorestate.c), and pass the child tuple to its parent.

When the node is notified it will be rescanned or it will change scan direction. The material node will try to get the needed tuple in its local Tuplestorestate before scanning the child Append node. Assuming the above query will return at least one result, the tuplestorestate will be used in the following query:

SELECT * FROM cypher('G', $$ match (a)-[e]->(b) delete e return e$$) AS (u agtype);

The Execution Tree:

SELECT * FROM cypher('G', $$EXPLAIN match ()-[e]->(b) delete e return e$$) AS (u agtype);
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Custom Scan (Cypher Delete)  (cost=0.00..0.00 rows=0 width=32)
   ->  Subquery Scan on _  (cost=0.00..3.06 rows=3 width=32)
         ->  Nested Loop  (cost=0.00..3.03 rows=3 width=96)
               Join Filter: (graphid_to_agtype(e.end_id) = graphid_to_agtype(b.id))
               ->  Append  (cost=0.00..1.03 rows=3 width=38)
                     ->  Seq Scan on _ag_label_edge e  (cost=0.00..0.00 rows=1 width=56)
                     ->  Seq Scan on edge e_1  (cost=0.00..1.02 rows=2 width=29)
               ->  Materialize  (cost=0.00..1.05 rows=3 width=8)
                     ->  Append  (cost=0.00..1.03 rows=3 width=8)
                           ->  Seq Scan on _ag_label_vertex b  (cost=0.00..0.00 rows=1 width=8)
                           ->  Seq Scan on vertex b_1  (cost=0.00..1.02 rows=2 width=8)
(11 rows)

Cypher's Delete clause (The Custom Scan node at the top of the execution tree) attempts to dive into its children to get the physical tuple that it has been asked to delete. Which is stored in the SeqScan, for the above query (DELETE, CREATE, SET, or REMOVE may store it also). It needs this information because we need the delete clause to mark the tuple as deleted*.

*Deleting a tuple in Postgres whether using Cypher's or Postgres' delete functionality does't actually delete data, they just mark it as no longer a valid tuple for future queries.

Problem

The problem is that when the Materialize Node passes a cached tuple: the SeqScan is not going to be holding the physical tuple anymore, function that get the tuple returns NULL to the Cypher Delete node. The delete clause then cannot find the tuple and doesn't delete the tuple.

Solution

Instead of the DELETE (or SET/REMOVE) using its children to get the physical location of the tuple on disc, the clause get it themselves. We start a new scan on the table until we find the physical tuple. Use the id, which is unique per edge/vertex in any given graph.

Considerations

This will slow down performance, because we have to scan the disc for the tuple's location ourselves. However, using the execution tree when possible and scanning the tables when not will be time consuming and potentially inconsequential relative to other performance solutions that could render this dual retrieval solution useless.

Is there a list of supported & unsupported Cypher syntax?
I tried using MERGE and OPTIONAL MATCH and I get a syntax error for both.

I have data saved in relationnel Tables, i want to Import them as a Graph data, so i can use Graph relations between them.

Is there a way to do that directly in SQL ? or do i have to use a Programming Language to query the data first from the relational tables first then create Cipher Queries to create the Data in the Graph DB ?

ps: i noticed that it's possible to Join the Select Data with Relationnel Tables, but here i'm searching to export the data from the Relationnel Table into the graph DB

By creating a CONTRIBUTING.md you can let others know how to contribute

Hello

I'm trying to analyze AGE source for using create vertex on pl/pgsql.

I tried this script

LOAD 'age';
SET search_path = ag_catalog, age, public;

select create_graph('test');

SELECT * from cypher('test', $$
  CREATE (a:Part {id: '123',value: '1234'})
$$) as (a agtype);

SELECT * from cypher('test', $$
  MATCH (a:Part)
  RETURN a.value
$$) as (a agtype);


CREATE OR REPLACE FUNCTION public.create_graph4(
	)
    RETURNS void
    LANGUAGE 'plpgsql'
AS $BODY$
begin
	LOAD 'age';
	SET search_path = ag_catalog, age, public;
	SELECT * from cypher('test', $$ create(:Part{id:'1234',value:'12345'}) $$) as (a agtype);	
end
$BODY$;
select public.create_graph4();

2021-03-09 12:48:38.560 KST [24113] LOG:  server process (PID 26455) was terminated by signal 11: 세그멘테이션 오류
2021-03-09 12:48:38.560 KST [24113] DETAIL:  Failed process was running: select public.create_graph4();
2021-03-09 12:48:38.560 KST [24113] LOG:  terminating any other active server processes
2021-03-09 12:48:38.561 KST [26449] WARNING:  terminating connection because of crash of another server process
2021-03-09 12:48:38.561 KST [26449] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-03-09 12:48:38.561 KST [26449] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: 2021-03-09 12:48:38.563 KST [26452] WARNING:  terminating connection because of crash of another server process
2021-03-09 12:48:38.563 KST [26452] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-03-09 12:48:38.563 KST [26452] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2021-03-09 12:48:38.564 KST [26457] FATAL:  the database system is in recovery mode
Failed.
!> 2021-03-09 12:48:38.565 KST [24113] LOG:  all server processes terminated; reinitializing
2021-03-09 12:48:38.578 KST [26458] LOG:  database system was interrupted; last known up at 2021-03-09 12:46:40 KST
2021-03-09 12:48:38.772 KST [26458] LOG:  database system was not properly shut down; automatic recovery in progress
2021-03-09 12:48:38.774 KST [26458] LOG:  redo starts at 0/31CF228
2021-03-09 12:48:38.791 KST [26458] LOG:  invalid record length at 0/337ADC8: wanted 24, got 0
2021-03-09 12:48:38.791 KST [26458] LOG:  redo done at 0/337AD60
2021-03-09 12:48:38.791 KST [26458] LOG:  last completed transaction was at log time 2021-03-09 12:48:37.494032+09
2021-03-09 12:48:38.835 KST [24113] LOG:  database system is ready to accept connections

세그멘테이션 오류 -> segmentation error
first start "select public.create_graph4();" -> disconnect
second start "select public.create_graph4();" -> not disconnect
third start "select public.create_graph4();" -> not disconnect

why first time start "select public.create_graph4()" disconnected ?

I encountered a strange situation when doing some experiments with AGE.

I used node-postgres (pg) to create a graph with

SELECT create_graph('my_graph_name');

if i repeat the same query, i will correctly get an error

ERROR:  schema "my_graph_name" already exists

But if afterward i use psql to repeat the query, i get

NOTICE:  graph "my_graph_name" has been created

repeating it a 2º time in psql (or in pg) with gives the correct error.

I would expect to get an error, the first time, when trying the query in psql since i already created the graph in pg.

Hello, it would be great if we could use unique keys in nodes and edges. in Neo4j it's called Node Key and it can be used like these:

1. CREATE CONSTRAINT constraint_name ON (n:Person) ASSERT (n.firstname) IS NODE KEY
2. CREATE CONSTRAINT constraint_name ON (n:Person) ASSERT (n.firstname, n.surname) IS NODE KEY
3. CREATE CONSTRAINT constraint_name IF NOT EXISTS ON (n:Person) ASSERT (n.firstname, n.surname) IS NODE KEY
4. CREATE CONSTRAINT constraint_with_provider ON (n:Label) ASSERT (n.prop1) IS NODE KEY OPTIONS {indexProvider: 'native-btree-1.0'}
5. DROP CONSTRAINT constraint_name
6. DROP CONSTRAINT missing_constraint_name IF EXISTS

I've tried to use Postgres queries but since the properties column type is not json/jsonb I got error:

postgres=# CREATE UNIQUE INDEX person_name_idx ON mygraph.person( (properties->>'name') ) ;
ERROR:  operator does not exist: ag_catalog.agtype ->> unknown
LINE 1: ...INDEX person_name_idx ON mygraph.person( (properties->>'name')...
                                                               ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

I've currently implemented graphs (sans vertex properties) in Postgres using Recursive CTEs, without any real issue or restriction.

Is the main advantage of using AGE the query language being specialty focused on graph querying?

In looking at the docs, it seems like the extension is primarily focused on providing functions for interacting with the data. Under the covers, are the data stored as blobs? How is typing enforced if it's not using the native Postgres type system?

Sorry I'm making assumptions based on examples, and I could be totally wrong. I'm asking out of curiosity, not being critical.

Original Post in r/postgreSQL
https://www.reddit.com/r/PostgreSQL/comments/lv9u7s/postgresql_with_graph_database_features/

How would a Rust crate access this DB? Can it just submit query strings and be done?

This issue is just to bring a minute point.

When using psql to create a graph with

SELECT create_graph('my_graph_name');

the return is

NOTICE:  graph "my_graph_name" has been created

But when the graph is a duplicate the error given is

ERROR:  schema "my_graph_name" already exists

I was thinking that this message comes probably from postgresql and is not directly controlled by AGE?
Since we are using a AGE function, maybe we should be getting an error similar to:

ERROR:  graph "my_graph_name" already exists

This would help to emphasize the idea that a graph is much more than a schema and to use the AGE functions instead of, for a stretched example, trying to artificially delete a graph by deleting the schema.

It is possible to create a node with a list property:

SELECT * FROM cypher('testgraph', $$CREATE (v:Test {id: 1, a: ['a', 'b']}) return v$$) as (a agtype);
                                             a                                              
---------------------------------------------------------------------------------------------
 {"id": 1688849860263973, "label": "Test", "properties": {"a": ["a", "b"], "id": 1}}::vertex
(1 row)

It seems to be impossible to update existing nodes with list properties:

SELECT * FROM cypher('testgraph', $$MATCH (ve:Test {id: 1}) SET ve.a = ['c', 'd'] return ve$$) as (a agtype);
                                          a                                           
--------------------------------------------------------------------------------------
 {"id": 1688849860263974, "label": "Test", "properties": {"a": "c", "id": 1}}::vertex
(1 row)

It also seems to be impossible to add new list properties to existing nodes:

SELECT * FROM cypher('testgraph', $$MATCH (ve:Test {id: 1}) SET ve.b = ['e', 'f'] return ve$$) as (a agtype);
                                               a                                                
------------------------------------------------------------------------------------------------
 {"id": 1688849860263974, "label": "Test", "properties": {"a": "c", "b": "e", "id": 1}}::vertex
(1 row)

I want to querying label as row. but, i can't find label function like AgensGraph or Neo4j.
so, currently using follow function, ag_catalog._label_name(oid, v | e) like behind.

i think it is not suitable to use.

so, any other ways to querying to get label string?

-- $schema_name
SELECT label, count(label) as cnt
FROM (
         SELECT ag_catalog._label_name(oid, v)::text as label
         from cypher('$schema_name', $$
             MATCH (V)
             RETURN id(V)
             $$) as (V agtype), (SELECT oid FROM ag_catalog.ag_graph where name = '$schema_name') as oid
     ) b
GROUP BY b.label;

In agensgraph or postgresql, you can increase query reuse (the same query is executed without parsing)
by applying a bind variable($1) to the where condition value instead of a literal value with a prepare statement.

example)
PREPARE x(text) AS
SELECT * FROM ( match(a:person) WHERE a.name = $1 return properties(a)) as v;
EXECUTE x(933);

In age, the following error occurs when applying the bind variable ($1).
PREPARE x(text) AS
SELECT *
FROM cypher('graph_name', $$
MATCH (a:Person), (b:Person)
WHERE a.name = $1 AND b.name = 'Node B'
with a,b
match(a)-[e:RELTYPE]->(b)
where b.born > 1999
RETURN a
$$) as (a agtype);
ERROR: unexpected character at or near "$"
LINE 5: WHERE a.name = $1 AND b.name = 'Node B'

These queries are often implemented in development application environments.
Are there any rules for applying bind variables in age?

Hi!

Congratulations on the extension. It looks really nice and I love seeing postgres being used like this.

I wanted to check if anyone here in the project ran a performance comparison against Neo4J to see how it compares.

Thanks!