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.pa...@gmail.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.z...@gmail.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
testcase.sql
Description: Binary data
