Re: SQL Property Graph Queries (SQL/PGQ)

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 4:08 PM Ajay Pal <ajay(dot)pal(dot)k(at)gmail(dot)com> wrote:
>
> 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
>
> On Tue, Aug 13, 2024 at 3:22 PM Ajay Pal <ajay(dot)pal(dot)k(at)gmail(dot)com> wrote:
> >
> > 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
> >
> > On Sat, Aug 10, 2024 at 2:52 PM Imran Zaheer <imran(dot)zhir(at)gmail(dot)com> wrote:
> > >
> > > 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], 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],
> > > 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