Re: SQL Property Graph Queries (SQL/PGQ)

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