Re: SQL Property Graph Queries (SQL/PGQ)

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