Re: SQL Property Graph Queries (SQL/PGQ)

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