SQL Property Graph Queries (SQL/PGQ)

Hi,

On 2024-02-16 15:53:11 +0100, Peter Eisentraut wrote:

The patch is quite fragile, and treading outside the tested paths will
likely lead to grave misbehavior. Use with caution. But I feel that
the general structure is ok, and we just need to fill in the
proverbial few thousand lines of code in the designated areas.

One aspect that I m concerned with structurally is that the transformation,
from property graph queries to something postgres understands, is done via the
rewrite system. I doubt that that is a good idea. For one it bars the planner
from making plans that benefit from the graph query formulation. But more
importantly, we IMO should reduce usage of the rewrite system, not increase
it.

Greetings,

Andres Freund

On 16.02.24 20:23, Andres Freund wrote:

One aspect that I m concerned with structurally is that the transformation,
from property graph queries to something postgres understands, is done via the
rewrite system. I doubt that that is a good idea. For one it bars the planner
from making plans that benefit from the graph query formulation. But more
importantly, we IMO should reduce usage of the rewrite system, not increase
it.

PGQ is meant to be implemented like that, like views expanding to joins
and unions. This is what I have gathered during the specification
process, and from other implementations, and from academics. There are
certainly other ways to combine relational and graph database stuff,
like with native graph storage and specialized execution support, but
this is not that, and to some extent PGQ was created to supplant those
other approaches.

Many people will agree that the rewriter is sort of weird and archaic at
this point. But I'm not aware of any plans or proposals to do anything
about it. As long as the view expansion takes place there, it makes
sense to align with that. For example, all the view security stuff
(privileges, security barriers, etc.) will eventually need to be
considered, and it would make sense to do that in a consistent way. So
for now, I'm working with what we have, but let's see where it goes.

(Note to self: Check that graph inside view inside graph inside view ...
works.)

On 2/23/24 17:15, Peter Eisentraut wrote:

On 16.02.24 20:23, Andres Freund wrote:

One aspect that I m concerned with structurally is that the
transformation,
from property graph queries to something postgres understands, is done
via the
rewrite system. I doubt that that is a good idea. For one it bars the
planner
from making plans that benefit from the graph query formulation. But more
importantly, we IMO should reduce usage of the rewrite system, not
increase
it.

PGQ is meant to be implemented like that, like views expanding to joins
and unions.  This is what I have gathered during the specification
process, and from other implementations, and from academics.  There are
certainly other ways to combine relational and graph database stuff,
like with native graph storage and specialized execution support, but
this is not that, and to some extent PGQ was created to supplant those
other approaches.

I understand PGQ was meant to be implemented as a bit of a "syntactic
sugar" on top of relations, instead of inventing some completely new
ways to store/query graph data.

But does that really mean it needs to be translated to relations this
early / in rewriter? I haven't thought about it very deeply, but won't
that discard useful information about semantics of the query, which
might be useful when planning/executing the query?

I've somehow imagined we'd be able to invent some new index types, or
utilize some other type of auxiliary structure, maybe some special
executor node, but it seems harder without this extra info ...

Many people will agree that the rewriter is sort of weird and archaic at
this point.  But I'm not aware of any plans or proposals to do anything
about it.  As long as the view expansion takes place there, it makes
sense to align with that.  For example, all the view security stuff
(privileges, security barriers, etc.) will eventually need to be
considered, and it would make sense to do that in a consistent way.  So
for now, I'm working with what we have, but let's see where it goes.

(Note to self: Check that graph inside view inside graph inside view ...
works.)

AFAIK the "policy" regarding rewriter was that we don't want to use it
for user stuff (e.g. people using it for partitioning), but I'm not sure
about internal stuff.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Feb 23, 2024 at 11:08 PM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:

On 2/23/24 17:15, Peter Eisentraut wrote:

On 16.02.24 20:23, Andres Freund wrote:

One aspect that I m concerned with structurally is that the
transformation,
from property graph queries to something postgres understands, is done
via the
rewrite system. I doubt that that is a good idea. For one it bars the
planner
from making plans that benefit from the graph query formulation. But more
importantly, we IMO should reduce usage of the rewrite system, not
increase
it.

PGQ is meant to be implemented like that, like views expanding to joins
and unions. This is what I have gathered during the specification
process, and from other implementations, and from academics. There are
certainly other ways to combine relational and graph database stuff,
like with native graph storage and specialized execution support, but
this is not that, and to some extent PGQ was created to supplant those
other approaches.

I understand PGQ was meant to be implemented as a bit of a "syntactic
sugar" on top of relations, instead of inventing some completely new
ways to store/query graph data.

But does that really mean it needs to be translated to relations this
early / in rewriter? I haven't thought about it very deeply, but won't
that discard useful information about semantics of the query, which
might be useful when planning/executing the query?

I've somehow imagined we'd be able to invent some new index types, or
utilize some other type of auxiliary structure, maybe some special
executor node, but it seems harder without this extra info ...

I am yet to look at the implementation but ...
1. If there are optimizations that improve performance of some path
patterns, they are likely to improve the performance of joins used to
implement those. In such cases, loosing some information might be ok.
2. Explicit graph annotatiion might help to automate some things like
creating indexes automatically on columns that appear in specific
patterns OR create extended statistics automatically on the columns
participating in specific patterns. OR interpreting statistics/costing
in differently than normal query execution. Those kind of things will
require retaining annotations in views, planner/execution trees etc.
3. There are some things like aggregates/operations on paths which
might require stuff like new execution nodes. But I am not sure we
have reached that stage yet.

There might be things we may not see right now in the standard e.g.
indexes on graph properties. For those mapping the graph objects unto
database objects might prove useful. That goes back to Peter's comment
--- quote
As long as the view expansion takes place there, it makes
sense to align with that.  For example, all the view security stuff
(privileges, security barriers, etc.) will eventually need to be
considered, and it would make sense to do that in a consistent way.
--- unquote

--
Best Wishes,
Ashutosh Bapat

Patch conflicted with changes in ef5e2e90859a39efdd3a78e528c544b585295a78.
Attached patch with the conflict resolved.

--
Best Wishes,
Ashutosh Bapat

Here is a new version of this patch. I have been working together with
Ashutosh on this. While the version 0 was more of a fragile demo, this
version 1 has a fairly complete minimal feature set and should be useful
for playing around with. We do have a long list of various internal
bits that still need to be fixed or revised or looked at again, so there
is by no means a claim that everything is completed.

Documentation to get started is included (ddl.sgml and queries.sgml).
(Of course, feedback on the getting-started documentation would be most
welcome.)

In the ddl.sgml, I’d swap the first two paragraphs.
I find the first one a bit confusing as-is. As far as I can tell, it’s an implementation detail.
The first paragraph should answer, “I have some data modeled as a graph G=(V, E). Can Postgres help me?”.

Then, introducing property graphs makes more sense.

I'd also use the examples and fake data in `graph_table.sql` in ddl/queries.sgml).
I was bummed that that copy-pasting didn't work as is.
I’d keep explaining how a graph query translates to a relational one later in the page.

As for the implementation, I can’t have an opinion yet,
but for those not familiar, Apache Age uses a slightly different approach
that mimics jsonpath (parses a sublanguage expression into an internal execution engine etc.).
However, the standard requires mapping this to the relational model, which makes sense for core Postgres.

On Thu, Jun 27, 2024 at 6:01 PM Peter Eisentraut <peter@eisentraut.org>
wrote:

Here is a new version of this patch. I have been working together with
Ashutosh on this. While the version 0 was more of a fragile demo, this
version 1 has a fairly complete minimal feature set and should be useful
for playing around with. We do have a long list of various internal
bits that still need to be fixed or revised or looked at again, so there
is by no means a claim that everything is completed.

PFA the patchset fixing compilation error reported by CI bot.
0001 - same as previous one
0002 - fixes compilation error
0003 - adds support for WHERE clause in graph pattern missing in the first
patch.

--
Best Wishes,
Ashutosh Bapat

On Mon, Jul 8, 2024 at 7:07 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Thu, Jun 27, 2024 at 6:01 PM Peter Eisentraut <peter@eisentraut.org> wrote:

Here is a new version of this patch. I have been working together with
Ashutosh on this. While the version 0 was more of a fragile demo, this
version 1 has a fairly complete minimal feature set and should be useful
for playing around with. We do have a long list of various internal
bits that still need to be fixed or revised or looked at again, so there
is by no means a claim that everything is completed.

PFA the patchset fixing compilation error reported by CI bot.
0001 - same as previous one
0002 - fixes compilation error
0003 - adds support for WHERE clause in graph pattern missing in the first patch.

There's a test failure reported by CI. Property graph related tests
are failing when regression is run from perl tests. The failure is
reported only on Free BSD. I have added one patch in the series which
will help narrow the failure. The patch changes the code to report the
location of an error reported when handling implicit properties or
labels.
0001 - same as previous one
0002 - fixes pgperltidy complaints
0003 - fixes compilation failure
0004 - same as 0003 in previous set
0005 - patch to report parse location of error

--
Best Wishes,
Ashutosh Bapat

On Wed, Jul 17, 2024 at 11:04 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Mon, Jul 8, 2024 at 7:07 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Thu, Jun 27, 2024 at 6:01 PM Peter Eisentraut <peter@eisentraut.org> wrote:

Here is a new version of this patch. I have been working together with
Ashutosh on this. While the version 0 was more of a fragile demo, this
version 1 has a fairly complete minimal feature set and should be useful
for playing around with. We do have a long list of various internal
bits that still need to be fixed or revised or looked at again, so there
is by no means a claim that everything is completed.

PFA the patchset fixing compilation error reported by CI bot.
0001 - same as previous one
0002 - fixes compilation error
0003 - adds support for WHERE clause in graph pattern missing in the first patch.

There's a test failure reported by CI. Property graph related tests
are failing when regression is run from perl tests. The failure is
reported only on Free BSD.

I thought it's related to FreeBSD but the bug could be observed
anywhere with -DRELCACHE_FORCE_RELEASE. It's also reported indirectly
by valgrind.

When infering properties of an element from the underlying table's
attributes, the attribute name pointed to the memory in the heap tuple
of pg_attribute row. Thus when the tuple was released, it pointed to a
garbage instead of actual column name resulting in column not found
error.

Attached set of patches with an additional patch to fix the bug.

0001 - same as previous one
0002 - fixes pgperltidy complaints
0003 - fixes compilation failure
0004 - fixes issue seen on CI
0005 - adds support for WHERE clause in graph pattern missing in the
first patch.

Once reviewed, patches 0002 to 0005 should be merged into 0001.

--
Best Wishes,
Ashutosh Bapat

Hi
I am attaching a new patch for a minor feature addition.

- Adding support for 'Labels and properties: EXCEPT list'

Please let me know if something is missing.

Thanks and Regards
Imran Zaheer

On Mon, Jul 22, 2024 at 9:02 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Mon, Jul 22, 2024 at 5:31 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

I found that the patches do not support cyclic paths correctly. A
cyclic path pattern is a path patterns where an element pattern
variable repeats e.g. (a)->(b)->(a). In such a path pattern the
element patterns with the same variable indicate the same element in
the path. In the given example (a) specifies that the path should
start and end with the same vertex. Patch 0006 supports cyclic path
patterns.

Elements which share the variable name should have the same element
type. The element patterns sharing the same variable name should have
same label expression. They may be constrained by different conditions
which are finally ANDed since they all represent the same element. The
patch creates a separate abstraction "path_factor" which combines all
the GraphElementPatterns into one element pattern. SQL/PGQ standard
uses path_factor for such an entity, so I chose that as the structure
name. But suggestions are welcome.

A path_factor is further expanded into a list of path_element objects
each representing a vertex or edge table that satisfies the label
expression in GraphElementPattern. In the previous patch set, the
consecutive elements were considered to be connected to each other.
Cyclic paths change that. For example, in path pattern (a)->(b)->(a),
(b) is connected to the first element on both sides (forming a cycle)
instead of first and third element. Patch 0006 has code changes to
appropriately link the elements. As a side effect, I have eliminated
the confusion between variables with name gep and gpe.

While it's easy to imagine a repeated vertex pattern, a repeated edge
pattern is slightly complex. An edge connects only two vertices, and
thus a repeated edge pattern constrains the adjacent vertex patterns
even if they have different variable names. Such patterns are not
supported. E.g. (a)-[b]->(c)-[b]->(d) would mean that (d) and (a)
represent the same vertex even if the variable names are different.
Such patterns are not supported for now. But (a)-[b]->(a)-[b]->(a) OR
(a)-[b]->(c)<-[b]-(a) are supported since the vertices adjacent to
repeated edges are constrained by the variable name anyway.

The patch also changes many foreach() to use foreach_* macros as appropriate.

0001 - same as previous one
0002 - fixes pgperltidy complaints
0003 - fixes compilation failure
0004 - fixes issue seen on CI
0005 - adds support for WHERE clause in graph pattern missing in the
first patch.

0006 - adds full support for cyclic path patterns

Once reviewed, patches 0002 to 0006 should be merged into 0001.

--
Best Wishes,
Ashutosh Bapat

Hi Imran,

On Sun, Aug 4, 2024 at 12:32 PM Imran Zaheer <imran.zhir@gmail.com> wrote:

Hi
I am attaching a new patch for a minor feature addition.

- Adding support for 'Labels and properties: EXCEPT list'

Do you intend to support EXCEPT in the label expression as well or
just properties?

Please let me know if something is missing.

I think the code changes are in the right place. I didn't review the
patch thoroughly. But here are some comments and some advice.

Please do not top-post on hackers.

Always sent the whole patchset. Otherwise, CI bot gets confused. It
doesn't pick up patchset from the previous emails.

About the functionality: It's not clear to me whether an EXCEPT should
be applicable only at the time of property graph creation or it should
be applicable always. I.e. when a property graph is dumped, should it
have EXCEPT in it or have a list of columns surviving except list?
What if a column in except list is dropped after creating a property
graph?

Some comments on the code
1. You could use list_member() in insert_property_records() to check
whether a given column is in the list of exceptions after you have
enveloped in String node.
2. The SELECT with GRAPH_TABLE queries are tested in graph_table.sql.
We don't include those in create_property_graph.sql
3. Instead of creating a new property graph in the test, you may
modify one of the existing property graphs to have a label with except
list and then query it.

We are aiming a minimal set of features in the first version. I will
let Peter E. decide whether to consider this as minimal set feature or
not. The feature looks useful to me.

--
Best Wishes,
Ashutosh Bapat

Hi Ashutosh,

Thanks for the feedback.

Do you intend to support EXCEPT in the label expression as well or
just properties?

I only implemented it for the properties because I couldn't find any
example for Label expression using EXCEPT clause. So I thought it was
only meant to be for the properties.
But if you can confirm that we do use EXCEPT clauses with label
expressions as well then I can try supporting that too.

Please do not top-post on hackers.

Always sent the whole patchset. Otherwise, CI bot gets confused. It
doesn't pick up patchset from the previous emails.

Okay, I will take care of that.

About the functionality: It's not clear to me whether an EXCEPT should
be applicable only at the time of property graph creation or it should
be applicable always. I.e. when a property graph is dumped, should it
have EXCEPT in it or have a list of columns surviving except list?
What if a column in except list is dropped after creating a property
graph?

I did some testing on that, for now we are just dumping the columns
surviving the except list.
If an exceptional table column is deleted afterwards it doesn't show
any effect on the graph. I also tested this scenario with duckdb pgq
extension [1]https://github.com/cwida/duckpgq-extension, deleting the col doesn't affect the graph.

Some comments on the code

I am attaching a new patch after trying to fix according to you comments

1. You could use list_member() in insert_property_records() to check
whether a given column is in the list of exceptions after you have
enveloped in String node.

* I have changed to code to use list_member(), but I have to make
ResTarget->name from `pstrdup(NameStr(att->attname));` to `NULL`
We are using `xml_attribute_list` for our columns list and while
making this list in gram.y we are assigning `rt->name` as NULL [2]https://github.com/postgres/postgres/blob/f5a1311fccd2ed24a9fb42aa47a17d1df7126039/src/backend/parser/gram.y#L16166,
this causes list_member() func to fail while comparing except_list
nodes. That's why I am changing rt->name from string value to NULL in
propgraphcmds.c in this patch.

* Also, in order to use list_member() func I have to add a separate
for loop to iterate through the exceptional columns to generate the
error message if col is not valid. My question is, is it ok to use two
separate for loops (one to check except cols validity &
other(list_memeber) to check existence of scanned col in except list).
In the previous patch I was using single for loop to validate both
things.

2. The SELECT with GRAPH_TABLE queries are tested in graph_table.sql.
We don't include those in create_property_graph.sql

* I have moved the graph_table queries from create_property_graph.sql
to graph_table.sql.
* But in graph_table.sql I didn't use the existing graphs because
those graphs and tables look like there for some specific test
scenario, so I created my separate graph and table for my test
scenario. I didn't drop the graph and the table as we will be dropping
the schema at the end but Peter E has this comment "-- leave for
pg_upgrade/pg_dump tests".

3. Instead of creating a new property graph in the test, you may
modify one of the existing property graphs to have a label with except
list and then query it.

* I have modified the graphs in create_property_graph.sql in order to
test except list cols in the alter command and create graph command.

We are aiming a minimal set of features in the first version. I will
let Peter E. decide whether to consider this as minimal set feature or
not. The feature looks useful to me.

Thanks if you find this patch useful. I am attaching the modified patch.

0001 - same as previous one
0002 - fixes pgperltidy complaints
0003 - fixes compilation failure
0004 - fixes issue seen on CI
0005 - adds support for WHERE clause in graph pattern missing in the
first patch.
0006 - adds full support for cyclic path patterns

0007 - adds support for except cols list in graph properties

Thanks
Imran Zaheer

[1]: https://github.com/cwida/duckpgq-extension
[2]: https://github.com/postgres/postgres/blob/f5a1311fccd2ed24a9fb42aa47a17d1df7126039/src/backend/parser/gram.y#L16166

Hello,

With the attached patch found below error when try to use "Any
directed edge" syntax.

postgres=# SELECT * FROM GRAPH_TABLE (students_graph
postgres(# MATCH
postgres(# (a IS person ) - [] - (b IS person)
postgres(# COLUMNS (a.name AS person_a, b.name AS person_b)
postgres(# );
ERROR: unsupported element pattern kind: undirected edge

If this syntax is supported then should behave as below,

PERSON_A PERSON_B
---------- ----------
Bob John
John Mary
Alice Mary
Mary Bob
Mary John
Bob Mary
John Bob
Mary Alice

8 rows selected.

Attaching the sql file for reference.

Thanks
Ajay

Hello,

Further testing found that using a property graph with the plpgsql
function crashed the server. Please take a look at the attached SQL
file for reference tables.

postgres=# create or replace function func() returns int as
postgres-# $$
postgres$# declare person_av varchar;
postgres$# begin
postgres$#
postgres$# SELECT person_a into person_av FROM GRAPH_TABLE
(students_graph
postgres$# MATCH
postgres$# (a IS person) -[e IS friends]-> (b IS person
WHERE b.name = 'Bob')
postgres$# WHERE a.name='John'
postgres$# COLUMNS (a.name AS person_a, b.name AS person_b)
postgres$# );
postgres$#
postgres$# return person_av;
postgres$# end
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select func();
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.
The connection to the server was lost. Attempting reset: Failed.
!?>

Please let me know if you need more details.

Thanks
Ajay

Hi All,

When we use a graph table and any local table, the server crashes.
Please note, It is happening when using the where clause for the local
table only.

postgres=# SELECT * FROM customers a, GRAPH_TABLE (myshop2 MATCH (c IS
customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders)
COLUMNS (c.name_redacted AS customer_name_redacted));
customer_id | name | address | customer_name_redacted
-------------+-----------+---------+------------------------
1 | customer1 | US | redacted1
2 | customer2 | CA | redacted1
3 | customer3 | GL | redacted1
(3 rows)

postgres=# SELECT * FROM customers a, GRAPH_TABLE (myshop2 MATCH (c IS
customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders)
COLUMNS (c.name_redacted AS customer_name_redacted)) where
a.customer_id=1;
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.
The connection to the server was lost. Attempting reset: Failed.
!?> \q

Note:- I have referred to graph_table.sql to get the table structure
used in the above query.

Thanks
Ajay

On Tue, Aug 13, 2024 at 3:22 PM Ajay Pal <ajay.pal.k@gmail.com> wrote:

With the attached patch found below error when try to use "Any
directed edge" syntax.

postgres=# SELECT * FROM GRAPH_TABLE (students_graph
postgres(# MATCH
postgres(# (a IS person ) - [] - (b IS person)
postgres(# COLUMNS (a.name AS person_a, b.name AS person_b)
postgres(# );
ERROR: unsupported element pattern kind: undirected edge

Earlier patches treated syntax "-[]- " as undirected edge and didn't
support it. Per standard it is specifies an edge in either direction
which is equivalent of -[]-> OR <-[]-. Implemented in the attached
patches. Also added a test case in graph_table.sql.

On Tue, Aug 13, 2024 at 4:08 PM Ajay Pal <ajay.pal.k@gmail.com> wrote:

postgres=# create or replace function func() returns int as
postgres-# $$
postgres$# declare person_av varchar;
postgres$# begin
postgres$#
postgres$# SELECT person_a into person_av FROM GRAPH_TABLE
(students_graph
postgres$# MATCH
postgres$# (a IS person) -[e IS friends]-> (b IS person
WHERE b.name = 'Bob')
postgres$# WHERE a.name='John'
postgres$# COLUMNS (a.name AS person_a, b.name AS person_b)
postgres$# );
postgres$#
postgres$# return person_av;
postgres$# end
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select func();
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.
The connection to the server was lost. Attempting reset: Failed.
!?>

Nice catch. The crash happens because earlier patches implemented
parser hooks to resolve graph property references. Those
implementations conflicted with the same hooks implemented in plpgsql
code. The attached patches fix this by adding a member to ParseState
instead of using hooks. Once this was fixed, there was another
problem. Property graph referenced in GRAPH_TABLE was not being
locked. That problem is fixed in the attached patches as well.

On Tue, Aug 20, 2024 at 5:20 PM Ajay Pal <ajay.pal.k@gmail.com> wrote:

Hi All,

When we use a graph table and any local table, the server crashes.
Please note, It is happening when using the where clause for the local
table only.

postgres=# SELECT * FROM customers a, GRAPH_TABLE (myshop2 MATCH (c IS
customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders)
COLUMNS (c.name_redacted AS customer_name_redacted));
customer_id | name | address | customer_name_redacted
-------------+-----------+---------+------------------------
1 | customer1 | US | redacted1
2 | customer2 | CA | redacted1
3 | customer3 | GL | redacted1
(3 rows)

postgres=# SELECT * FROM customers a, GRAPH_TABLE (myshop2 MATCH (c IS
customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders)
COLUMNS (c.name_redacted AS customer_name_redacted)) where
a.customer_id=1;
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.
The connection to the server was lost. Attempting reset: Failed.
!?> \q

This problem is not reproducible after fixing other problem. Please
let me know if it's reproduces for you. If it reproduces please
provide a patch adding the reproduction to graph_table.sql.

Along with this I have rebased the patches on the latest HEAD, fixed
some comments, code styles etc.

Patches 0001 - 0006 are same as the previous set.
0007 - fixes all the problems you reported till now and also the one I
found. The commit message describes the fixes in detail.

--
Best Wishes,
Ashutosh Bapat

Patches 0001 - 0006 are same as the previous set.
0007 - fixes all the problems you reported till now and also the one I
found. The commit message describes the fixes in detail.

Hi,

I found a potential bug in the parsing of the left and right arrows.
They can be broken up in - > and < - respectively. Does the SQL/PGQ
standard really allow this?

I found this while working on a patch of our own and I was trying to
figure out how you guys had solved this very same problem that we ran
into, and if you had done so in a better way. The fundamental problem is
that parsing the left arrow as one token is a bit tricky due to how
PostgreSQL treats operators ending with minus or plus.

I have attached our very ugly solution for it (broken out from our
patch) in case it helps you. Feel free to use it or ignore it. We do not
plan to work on this right now since you are already working on the same
problem.

I especially dislike the static variable in our patch. And as far as I
understand it you can avoid the static by changing the lexer to use the
push parser so it can emit multiple terminal tokens from one parsed
token, but I have not looked into push parsers and have no idea how this
would affect performance.

https://www.gnu.org/software/bison/manual/html_node/Push-Decl.html

Examples:

# SELECT count(*) FROM GRAPH_TABLE (g1 MATCH ()-[]->() COLUMNS (1 as one));
count
-------
32
(1 row)

# SELECT count(*) FROM GRAPH_TABLE (g1 MATCH ()-[]- >() COLUMNS (1 as one));
count
-------
32
(1 row)

# SELECT * FROM GRAPH_TABLE (myshop MATCH (o IS orders)<-[IS
customer_orders]-(c IS customers) COLUMNS (c.name, o.ordered_when));
name | ordered_when
-----------+--------------
customer1 | 2024-01-01
customer2 | 2024-01-02
(2 rows)

# SELECT * FROM GRAPH_TABLE (myshop MATCH (o IS orders)< -[IS
customer_orders]-(c IS customers) COLUMNS (c.name, o.ordered_when));
name | ordered_when
-----------+--------------
customer1 | 2024-01-01
customer2 | 2024-01-02
(2 rows)

Andreas