Thread: SQL Property Graph Queries (SQL/PGQ)

Here is a prototype implementation of SQL property graph queries
(SQL/PGQ), following SQL:2023.  This was talked about briefly at the
FOSDEM developer meeting, and a few people were interested, so I
wrapped up what I had in progress into a presentable form.

There is some documentation to get started in doc/src/sgml/ddl.sgml
and doc/src/sgml/queries.sgml.

To learn more about this facility, here are some external resources:

* An article about a competing product:
   https://oracle-base.com/articles/23c/sql-property-graphs-and-sql-pgq-23c
   (All the queries in the article work, except the ones using
   vertex_id() and edge_id(), which are non-standard, and the JSON
   examples at the end, which require some of the in-progress JSON
   functionality for PostgreSQL.)

* An academic paper related to another competing product:
   https://www.cidrdb.org/cidr2023/papers/p66-wolde.pdf (The main part
   of this paper discusses advanced functionality that my patch doesn't
   have.)

* A 2019 presentation about graph databases:
   https://www.pgcon.org/2019/schedule/events/1300.en.html (There is
   also a video.)

* (Vik has a recent presentation "Property Graphs: When the Relational
   Model Is Not Enough", but I haven't found the content posted
   online.)

The patch is quite fragile, and treading outside the tested paths will
likely lead to grave misbehavior.  Use with caution.  But I feel that
the general structure is ok, and we just need to fill in the
proverbial few thousand lines of code in the designated areas.

Hi,

On 2024-02-16 15:53:11 +0100, Peter Eisentraut wrote:
> The patch is quite fragile, and treading outside the tested paths will
> likely lead to grave misbehavior.  Use with caution.  But I feel that
> the general structure is ok, and we just need to fill in the
> proverbial few thousand lines of code in the designated areas.

One aspect that I m concerned with structurally is that the transformation,
from property graph queries to something postgres understands, is done via the
rewrite system. I doubt that that is a good idea. For one it bars the planner
from making plans that benefit from the graph query formulation. But more
importantly, we IMO should reduce usage of the rewrite system, not increase
it.

Greetings,

Andres Freund

On 16.02.24 20:23, Andres Freund wrote:
> One aspect that I m concerned with structurally is that the transformation,
> from property graph queries to something postgres understands, is done via the
> rewrite system. I doubt that that is a good idea. For one it bars the planner
> from making plans that benefit from the graph query formulation. But more
> importantly, we IMO should reduce usage of the rewrite system, not increase
> it.

PGQ is meant to be implemented like that, like views expanding to joins 
and unions.  This is what I have gathered during the specification 
process, and from other implementations, and from academics.  There are 
certainly other ways to combine relational and graph database stuff, 
like with native graph storage and specialized execution support, but 
this is not that, and to some extent PGQ was created to supplant those 
other approaches.

Many people will agree that the rewriter is sort of weird and archaic at 
this point.  But I'm not aware of any plans or proposals to do anything 
about it.  As long as the view expansion takes place there, it makes 
sense to align with that.  For example, all the view security stuff 
(privileges, security barriers, etc.) will eventually need to be 
considered, and it would make sense to do that in a consistent way.  So 
for now, I'm working with what we have, but let's see where it goes.

(Note to self: Check that graph inside view inside graph inside view ... 
works.)

On 2/23/24 17:15, Peter Eisentraut wrote:
> On 16.02.24 20:23, Andres Freund wrote:
>> One aspect that I m concerned with structurally is that the
>> transformation,
>> from property graph queries to something postgres understands, is done
>> via the
>> rewrite system. I doubt that that is a good idea. For one it bars the
>> planner
>> from making plans that benefit from the graph query formulation. But more
>> importantly, we IMO should reduce usage of the rewrite system, not
>> increase
>> it.
> 
> PGQ is meant to be implemented like that, like views expanding to joins
> and unions.  This is what I have gathered during the specification
> process, and from other implementations, and from academics.  There are
> certainly other ways to combine relational and graph database stuff,
> like with native graph storage and specialized execution support, but
> this is not that, and to some extent PGQ was created to supplant those
> other approaches.
> 

I understand PGQ was meant to be implemented as a bit of a "syntactic
sugar" on top of relations, instead of inventing some completely new
ways to store/query graph data.

But does that really mean it needs to be translated to relations this
early / in rewriter? I haven't thought about it very deeply, but won't
that discard useful information about semantics of the query, which
might be useful when planning/executing the query?

I've somehow imagined we'd be able to invent some new index types, or
utilize some other type of auxiliary structure, maybe some special
executor node, but it seems harder without this extra info ...

> Many people will agree that the rewriter is sort of weird and archaic at
> this point.  But I'm not aware of any plans or proposals to do anything
> about it.  As long as the view expansion takes place there, it makes
> sense to align with that.  For example, all the view security stuff
> (privileges, security barriers, etc.) will eventually need to be
> considered, and it would make sense to do that in a consistent way.  So
> for now, I'm working with what we have, but let's see where it goes.
> 
> (Note to self: Check that graph inside view inside graph inside view ...
> works.)
> 

AFAIK the "policy" regarding rewriter was that we don't want to use it
for user stuff (e.g. people using it for partitioning), but I'm not sure
about internal stuff.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Feb 23, 2024 at 11:08 PM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
>
> On 2/23/24 17:15, Peter Eisentraut wrote:
> > On 16.02.24 20:23, Andres Freund wrote:
> >> One aspect that I m concerned with structurally is that the
> >> transformation,
> >> from property graph queries to something postgres understands, is done
> >> via the
> >> rewrite system. I doubt that that is a good idea. For one it bars the
> >> planner
> >> from making plans that benefit from the graph query formulation. But more
> >> importantly, we IMO should reduce usage of the rewrite system, not
> >> increase
> >> it.
> >
> > PGQ is meant to be implemented like that, like views expanding to joins
> > and unions.  This is what I have gathered during the specification
> > process, and from other implementations, and from academics.  There are
> > certainly other ways to combine relational and graph database stuff,
> > like with native graph storage and specialized execution support, but
> > this is not that, and to some extent PGQ was created to supplant those
> > other approaches.
> >
>
> I understand PGQ was meant to be implemented as a bit of a "syntactic
> sugar" on top of relations, instead of inventing some completely new
> ways to store/query graph data.
>
> But does that really mean it needs to be translated to relations this
> early / in rewriter? I haven't thought about it very deeply, but won't
> that discard useful information about semantics of the query, which
> might be useful when planning/executing the query?
>
> I've somehow imagined we'd be able to invent some new index types, or
> utilize some other type of auxiliary structure, maybe some special
> executor node, but it seems harder without this extra info ...

I am yet to look at the implementation but ...
1. If there are optimizations that improve performance of some path
patterns, they are likely to improve the performance of joins used to
implement those. In such cases, loosing some information might be ok.
2. Explicit graph annotatiion might help to automate some things like
creating indexes automatically on columns that appear in specific
patterns OR create extended statistics automatically on the columns
participating in specific patterns. OR interpreting statistics/costing
in differently than normal query execution. Those kind of things will
require retaining annotations in views, planner/execution trees etc.
3. There are some things like aggregates/operations on paths which
might require stuff like new execution nodes. But I am not sure we
have reached that stage yet.

There might be things we may not see right now in the standard e.g.
indexes on graph properties. For those mapping the graph objects unto
database objects might prove useful. That goes back to Peter's comment
--- quote
As long as the view expansion takes place there, it makes
sense to align with that.  For example, all the view security stuff
(privileges, security barriers, etc.) will eventually need to be
considered, and it would make sense to do that in a consistent way.
--- unquote

--
Best Wishes,
Ashutosh Bapat

Here is a new version of this patch.  I have been working together with 
Ashutosh on this.  While the version 0 was more of a fragile demo, this 
version 1 has a fairly complete minimal feature set and should be useful 
for playing around with.  We do have a long list of various internal 
bits that still need to be fixed or revised or looked at again, so there 
is by no means a claim that everything is completed.

Documentation to get started is included (ddl.sgml and queries.sgml). 
(Of course, feedback on the getting-started documentation would be most 
welcome.)

In the ddl.sgml, I’d swap the first two paragraphs.
I find the first one a bit confusing as-is. As far as I can tell, it’s an implementation detail.
The first paragraph should answer, “I have some data modeled as a graph G=(V, E). Can Postgres help me?”.

Then, introducing property graphs makes more sense.

I'd also use the examples and fake data in `graph_table.sql` in ddl/queries.sgml).
I was bummed that that copy-pasting didn't work as is.
I’d keep explaining how a graph query translates to a relational one later in the page.

As for the implementation, I can’t have an opinion yet,
but for those not familiar, Apache Age uses a slightly different approach
that mimics jsonpath (parses a sublanguage expression into an internal execution engine etc.).
However, the standard requires mapping this to the relational model, which makes sense for core Postgres.


> On 27 Jun 2024, at 3:31 PM, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> Here is a new version of this patch.  I have been working together with Ashutosh on this.  While the version 0 was
moreof a fragile demo, this version 1 has a fairly complete minimal feature set and should be useful for playing around
with. We do have a long list of various internal bits that still need to be fixed or revised or looked at again, so
thereis by no means a claim that everything is completed. 
>
> Documentation to get started is included (ddl.sgml and queries.sgml). (Of course, feedback on the getting-started
documentationwould be most welcome.) 
> <v1-0001-WIP-SQL-Property-Graph-Queries-SQL-PGQ.patch>

On Mon, Jul 8, 2024 at 7:07 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
>
>
> On Thu, Jun 27, 2024 at 6:01 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>>
>> Here is a new version of this patch.  I have been working together with
>> Ashutosh on this.  While the version 0 was more of a fragile demo, this
>> version 1 has a fairly complete minimal feature set and should be useful
>> for playing around with.  We do have a long list of various internal
>> bits that still need to be fixed or revised or looked at again, so there
>> is by no means a claim that everything is completed.
>
>
> PFA the patchset fixing compilation error reported by CI bot.
> 0001 - same as previous one
> 0002 - fixes compilation error
> 0003 - adds support for WHERE clause in graph pattern missing in the first patch.
>

There's a test failure reported by CI. Property graph related tests
are failing when regression is run from perl tests. The failure is
reported only on Free BSD. I have added one patch in the series which
will help narrow the failure. The patch changes the code to report the
location of an error reported when handling implicit properties or
labels.
0001 - same as previous one
0002 - fixes pgperltidy complaints
0003 - fixes compilation failure
0004 - same as 0003 in previous set
0005 - patch to report parse location of error



--
Best Wishes,
Ashutosh Bapat

On Wed, Jul 17, 2024 at 11:04 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Mon, Jul 8, 2024 at 7:07 PM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> >
> >
> >
> > On Thu, Jun 27, 2024 at 6:01 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> >>
> >> Here is a new version of this patch.  I have been working together with
> >> Ashutosh on this.  While the version 0 was more of a fragile demo, this
> >> version 1 has a fairly complete minimal feature set and should be useful
> >> for playing around with.  We do have a long list of various internal
> >> bits that still need to be fixed or revised or looked at again, so there
> >> is by no means a claim that everything is completed.
> >
> >
> > PFA the patchset fixing compilation error reported by CI bot.
> > 0001 - same as previous one
> > 0002 - fixes compilation error
> > 0003 - adds support for WHERE clause in graph pattern missing in the first patch.
> >
>
> There's a test failure reported by CI. Property graph related tests
> are failing when regression is run from perl tests. The failure is
> reported only on Free BSD.

I thought it's related to FreeBSD but the bug could be observed
anywhere with -DRELCACHE_FORCE_RELEASE. It's also reported indirectly
by valgrind.

When infering properties of an element from the underlying table's
attributes, the attribute name pointed to the memory in the heap tuple
of pg_attribute row. Thus when the tuple was released, it pointed to a
garbage instead of actual column name resulting in column not found
error.

Attached set of patches with an additional patch to fix the bug.

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.

Once reviewed, patches 0002 to 0005 should be merged into 0001.

--
Best Wishes,
Ashutosh Bapat

Hi
I am attaching a new patch for a minor feature addition.

- Adding support for 'Labels and properties: EXCEPT list'

Please let me know if something is missing.

Thanks and Regards
Imran Zaheer

On Mon, Jul 22, 2024 at 9:02 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Wed, Jul 17, 2024 at 11:04 AM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> >
> > On Mon, Jul 8, 2024 at 7:07 PM Ashutosh Bapat
> > <ashutosh.bapat.oss@gmail.com> wrote:
> > >
> > >
> > >
> > > On Thu, Jun 27, 2024 at 6:01 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> > >>
> > >> Here is a new version of this patch.  I have been working together with
> > >> Ashutosh on this.  While the version 0 was more of a fragile demo, this
> > >> version 1 has a fairly complete minimal feature set and should be useful
> > >> for playing around with.  We do have a long list of various internal
> > >> bits that still need to be fixed or revised or looked at again, so there
> > >> is by no means a claim that everything is completed.
> > >
> > >
> > > PFA the patchset fixing compilation error reported by CI bot.
> > > 0001 - same as previous one
> > > 0002 - fixes compilation error
> > > 0003 - adds support for WHERE clause in graph pattern missing in the first patch.
> > >
> >
> > There's a test failure reported by CI. Property graph related tests
> > are failing when regression is run from perl tests. The failure is
> > reported only on Free BSD.
>
> I thought it's related to FreeBSD but the bug could be observed
> anywhere with -DRELCACHE_FORCE_RELEASE. It's also reported indirectly
> by valgrind.
>
> When infering properties of an element from the underlying table's
> attributes, the attribute name pointed to the memory in the heap tuple
> of pg_attribute row. Thus when the tuple was released, it pointed to a
> garbage instead of actual column name resulting in column not found
> error.
>
> Attached set of patches with an additional patch to fix the bug.
>
> 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.
>
> Once reviewed, patches 0002 to 0005 should be merged into 0001.
>
> --
> Best Wishes,
> Ashutosh Bapat

On Mon, Jul 22, 2024 at 5:31 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>

I found that the patches do not support cyclic paths correctly. A
cyclic path pattern is a path patterns where an element pattern
variable repeats e.g. (a)->(b)->(a). In such a path pattern the
element patterns with the same variable indicate the same element in
the path. In the given example (a) specifies that the path should
start and end with the same vertex. Patch 0006 supports cyclic path
patterns.

Elements which share the variable name should have the same element
type. The element patterns sharing the same variable name should have
same label expression. They may be constrained by different conditions
which are finally ANDed since they all represent the same element. The
patch creates a separate abstraction "path_factor" which combines all
the GraphElementPatterns into one element pattern. SQL/PGQ standard
uses path_factor for such an entity, so I chose that as the structure
name. But suggestions are welcome.

A path_factor is further expanded into a list of path_element objects
each representing a vertex or edge table that satisfies the label
expression in GraphElementPattern. In the previous patch set, the
consecutive elements were considered to be connected to each other.
Cyclic paths change that. For example, in path pattern (a)->(b)->(a),
(b) is connected to the first element on both sides (forming a cycle)
instead of first and third element. Patch 0006 has code changes to
appropriately link the elements. As a side effect, I have eliminated
the confusion between variables with name gep and gpe.

While it's easy to imagine a repeated vertex pattern, a repeated edge
pattern is slightly complex. An edge connects only two vertices, and
thus a repeated edge pattern constrains the adjacent vertex patterns
even if they have different variable names. Such patterns are not
supported. E.g. (a)-[b]->(c)-[b]->(d) would mean that (d) and (a)
represent the same vertex even if the variable names are different.
Such patterns are not supported for now. But (a)-[b]->(a)-[b]->(a) OR
(a)-[b]->(c)<-[b]-(a) are supported since the vertices adjacent to
repeated edges are constrained by the variable name anyway.

The patch also changes many foreach() to use foreach_* macros as appropriate.

> 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

Once reviewed, patches 0002 to 0006 should be merged into 0001.

--
Best Wishes,
Ashutosh Bapat

Hi Imran,

On Sun, Aug 4, 2024 at 12:32 PM Imran Zaheer <imran.zhir@gmail.com> wrote:
>
> Hi
> I am attaching a new patch for a minor feature addition.
>
> - Adding support for 'Labels and properties: EXCEPT list'

Do you intend to support EXCEPT in the label expression as well or
just properties?

>
> Please let me know if something is missing.

I think the code changes are in the right place. I didn't review the
patch thoroughly. But here are some comments and some advice.

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.

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?

Some comments on the code
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.
2. The SELECT with GRAPH_TABLE queries are tested in graph_table.sql.
We don't include those in create_property_graph.sql
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.

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.

--
Best Wishes,
Ashutosh Bapat

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

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.zhir@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

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.pal.k@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.zhir@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

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.pal.k@gmail.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.pal.k@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.zhir@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