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.
Attachment
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
Patch conflicted with changes in ef5e2e90859a39efdd3a78e528c544b585295a78. Attached patch with the conflict resolved.
--
Best Wishes,
Ashutosh Bapat
Attachment
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.)
Attachment
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 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.
Best Wishes,
Ashutosh Bapat
Attachment
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
Attachment
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
Attachment
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
Attachment
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
Attachment
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
Attachment
- 0003-Fix-compilation-error-20240805.patch
- 0004-Fix-spurious-column-not-found-error-20240805.patch
- 0006-Support-cyclic-path-pattern-20240805.patch
- 0002-pgperltidy-fixes-20240805.patch
- 0005-support-WHERE-clause-in-graph-pattern-20240805.patch
- 0001-WIP-SQL-Property-Graph-Queries-SQL-PGQ-20240805.patch
- 0007-v2-Support-for-EXCEPT-list-in-properties.patch
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
Attachment
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
Attachment
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
On 05/11/2024 16:41, Ashutosh Bapat wrote:
On Wed, Aug 28, 2024 at 3:48 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:Patches 0001 - 0006 are same as the previous set. 0007 - fixes all the problems you reported till now and also the one I found. The commit message describes the fixes in detail.Here's an updated patchset based on the latest HEAD.
I have been looking at this patchset from a user's and standards' perspective and I am quite pleased with what I am seeing for the most part. I have not been looking much at the code itself, although I do plan on reviewing some of the code in the future.
There are a few things that stick out to me.
1.
I don't see any way to view the structure of of a property graph. For example:
postgres=# CREATE TABLE objects (id INTEGER, color VARCHAR, shape VARCHAR, size INTEGER);
CREATE TABLE
postgres=# CREATE PROPERTY GRAPH propgraph VERTEX TABLES (objects KEY (id) PROPERTIES ALL COLUMNS);
CREATE PROPERTY GRAPH
postgres=# \dG propgraph
List of relations
Schema | Name | Type | Owner
-------------------+-----------+----------------+-------------
graph_table_tests | propgraph | property graph | vik.fearing
(1 row)
postgres=# \d propgraph
Property graph "graph_table_tests.propgraph"
Column | Type
--------+------
I don't really know what to do with that.
2.
There is a missing newline in the \? help of psql.
HELP0(" \\dFt[+] [PATTERN] list text search templates\n");
HELP0(" \\dg[S+] [PATTERN] list roles\n");
HELP0(" \\dG[S+] [PATTERN] list property graphs"); <---
HELP0(" \\di[S+] [PATTERN] list indexes\n");
HELP0(" \\dl[+] list large objects, same as \\lo_list\n");
3.
The noise word "ARE" is missing from the <element table properties alternatives> clause.
There is also no support for the EXCEPT clause, but I imagine that can be added at a later time.
4.
I notice that tables in pg_catalog are not allowed in a property graph. What are the reasons for this? It is true that this might cause some problems with upgrades if a column is removed, but it shouldn't cause trouble for columns being added. That case works with user tables.
5.
The ascii art characters (I am loathe to call them operators) allow junk in between them. For example:
MATCH (c) -[:lbl]-> (d)
can be written as
MATCH (c) -
[:lbl] -
/* a comment here */
> (d)
Is that intentional?
----
I will continue to review this feature from the user's perspective. Thank you for working on it, I am very excited to get this in.
--
Vik Fearing
On Fri, Nov 22, 2024 at 7:29 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Tue, Nov 19, 2024 at 10:08 PM Vik Fearing <vik@postgresfriends.org> wrote:
> >
> >
> > On 05/11/2024 16:41, Ashutosh Bapat wrote:
> >
> > On Wed, Aug 28, 2024 at 3:48 PM Ashutosh Bapat
> > <ashutosh.bapat.oss@gmail.com> wrote:
> >
> > Patches 0001 - 0006 are same as the previous set.
> > 0007 - fixes all the problems you reported till now and also the one I
> > found. The commit message describes the fixes in detail.
> >
> > Here's an updated patchset based on the latest HEAD.
> >
> >
> >
> > I have been looking at this patchset from a user's and standards' perspective and I am quite pleased with what I am
seeingfor the most part. I have not been looking much at the code itself, although I do plan on reviewing some of the
codein the future.
>
> Thanks for looking at the patches.
>
> >
> >
> > There are a few things that stick out to me.
> >
> >
> > 1.
> > I don't see any way to view the structure of of a property graph. For example:
> >
> >
> > postgres=# CREATE TABLE objects (id INTEGER, color VARCHAR, shape VARCHAR, size INTEGER);
> > CREATE TABLE
> > postgres=# CREATE PROPERTY GRAPH propgraph VERTEX TABLES (objects KEY (id) PROPERTIES ALL COLUMNS);
> > CREATE PROPERTY GRAPH
> > postgres=# \dG propgraph
> > List of relations
> > Schema | Name | Type | Owner
> > -------------------+-----------+----------------+-------------
> > graph_table_tests | propgraph | property graph | vik.fearing
> > (1 row)
> >
> > postgres=# \d propgraph
> > Property graph "graph_table_tests.propgraph"
> > Column | Type
> > --------+------
> >
> > I don't really know what to do with that.
>
> Yes. It is on my TODO list. IMO we should just print the first line
> property graph "...". There are no predefined columns in this
> relation. And somehow redirect them to use \dG instead.
\d+ propgraph prints the definition of property graph. I find \dG
similar to \di which doesn't print the structure of an index. Instead
\d+ <index name> prints it.
In the attached patch series I have added patch 0008 to remove
unnecessary header
> > Column | Type
> > --------+------
It still prints an extra line but I haven't found a way to eliminate
it. Hence 0008 is WIP. I have listed TODOs in the commit message of
that patch.
> >
> >
> > 2.
> > There is a missing newline in the \? help of psql.
> > HELP0(" \\dFt[+] [PATTERN] list text search templates\n");
> > HELP0(" \\dg[S+] [PATTERN] list roles\n");
> > HELP0(" \\dG[S+] [PATTERN] list property graphs"); <---
> > HELP0(" \\di[S+] [PATTERN] list indexes\n");
> > HELP0(" \\dl[+] list large objects, same as \\lo_list\n");
> >
>
> Will fix that in the next set.
Done. The fix is part of 0001 now.
>
> >
> > I will continue to review this feature from the user's perspective. Thank you for working on it, I am very excited
toget this in.
>
here's patchset rebased on 792b2c7e6d926e61e8ff3b33d3e22d7d74e7a437
with conflicts in rowsecurity.sql/out fixed.
>
> 0001 - 0005 are the same as the previous set.
> 0007 - has RLS tests. It is the same as 0006 from the previous patch set.
This is same as the previous patchset.
>
> 0006 - is new addressing collation and edge-vertex link qual creation.
> This patch adds code to store collation and typmod to
> pg_propgraph_property catalog and propagate it to property graph
> references in GRAPH_TABLE. Collation is used by
> assign_query_collations and assign_expr_collations to propagate
> collation up the query and expression tree respectively. typmod is
> used to report typmod of property reference from exprTypemod().
>
> While finishing code to create vertex-edge link quals, I found that we
> need to find and store the operator to be used for key matching in
> those quals. I think we have to do something similar to what primary
> key handling code does - find the equality operator when creating edge
> descriptor and store it in pg_propgraph_element. I am not sure whether
> we should add a dependency on the operator. I will look into this
> next.
0006 in the attached patch series completes this work. Now we find the
equality operators to be used for vertex-edge quals and save it in
pg_propgraph_element catalog and also add a dependency between the
edge element and the equality operators.
0008 - has WIP fix for \d and \d+
--
Best Wishes,
Ashutosh Bapat
Hi Ashutosh,
On Fri, Dec 6, 2024 at 12:34 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> Sorry, I forgot to attach patches. PFA the patches.
>
> On Thu, Dec 5, 2024 at 4:38 PM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> >
> > On Fri, Nov 22, 2024 at 7:29 PM Ashutosh Bapat
> > <ashutosh.bapat.oss@gmail.com> wrote:
> > >
> > > On Tue, Nov 19, 2024 at 10:08 PM Vik Fearing <vik@postgresfriends.org> wrote:
> > > >
> > > >
> > > > On 05/11/2024 16:41, Ashutosh Bapat wrote:
> > > >
> > > > On Wed, Aug 28, 2024 at 3:48 PM Ashutosh Bapat
> > > > <ashutosh.bapat.oss@gmail.com> wrote:
> > > >
> > > > Patches 0001 - 0006 are same as the previous set.
> > > > 0007 - fixes all the problems you reported till now and also the one I
> > > > found. The commit message describes the fixes in detail.
> > > >
> > > > Here's an updated patchset based on the latest HEAD.
> > > >
> > > >
> > > >
> > > > I have been looking at this patchset from a user's and standards' perspective and I am quite pleased with what
Iam seeing for the most part. I have not been looking much at the code itself, although I do plan on reviewing some of
thecode in the future.
> > >
> > > Thanks for looking at the patches.
> > >
> > > >
> > > >
> > > > There are a few things that stick out to me.
> > > >
> > > >
> > > > 1.
> > > > I don't see any way to view the structure of of a property graph. For example:
> > > >
> > > >
> > > > postgres=# CREATE TABLE objects (id INTEGER, color VARCHAR, shape VARCHAR, size INTEGER);
> > > > CREATE TABLE
> > > > postgres=# CREATE PROPERTY GRAPH propgraph VERTEX TABLES (objects KEY (id) PROPERTIES ALL COLUMNS);
> > > > CREATE PROPERTY GRAPH
> > > > postgres=# \dG propgraph
> > > > List of relations
> > > > Schema | Name | Type | Owner
> > > > -------------------+-----------+----------------+-------------
> > > > graph_table_tests | propgraph | property graph | vik.fearing
> > > > (1 row)
> > > >
> > > > postgres=# \d propgraph
> > > > Property graph "graph_table_tests.propgraph"
> > > > Column | Type
> > > > --------+------
> > > >
> > > > I don't really know what to do with that.
> > >
> > > Yes. It is on my TODO list. IMO we should just print the first line
> > > property graph "...". There are no predefined columns in this
> > > relation. And somehow redirect them to use \dG instead.
> >
> > \d+ propgraph prints the definition of property graph. I find \dG
> > similar to \di which doesn't print the structure of an index. Instead
> > \d+ <index name> prints it.
> >
> > In the attached patch series I have added patch 0008 to remove
> > unnecessary header
> > > > Column | Type
> > > > --------+------
> >
> > It still prints an extra line but I haven't found a way to eliminate
> > it. Hence 0008 is WIP. I have listed TODOs in the commit message of
> > that patch.
> >
> >
> > > >
> > > >
> > > > 2.
> > > > There is a missing newline in the \? help of psql.
> > > > HELP0(" \\dFt[+] [PATTERN] list text search templates\n");
> > > > HELP0(" \\dg[S+] [PATTERN] list roles\n");
> > > > HELP0(" \\dG[S+] [PATTERN] list property graphs"); <---
> > > > HELP0(" \\di[S+] [PATTERN] list indexes\n");
> > > > HELP0(" \\dl[+] list large objects, same as \\lo_list\n");
> > > >
> > >
> > > Will fix that in the next set.
> >
> > Done. The fix is part of 0001 now.
> >
> >
> >
> > >
> > > >
> > > > I will continue to review this feature from the user's perspective. Thank you for working on it, I am very
excitedto get this in.
> > >
> >
> > here's patchset rebased on 792b2c7e6d926e61e8ff3b33d3e22d7d74e7a437
> > with conflicts in rowsecurity.sql/out fixed.
> >
> > >
> > > 0001 - 0005 are the same as the previous set.
> > > 0007 - has RLS tests. It is the same as 0006 from the previous patch set.
> >
> > This is same as the previous patchset.
> >
> > >
> > > 0006 - is new addressing collation and edge-vertex link qual creation.
> > > This patch adds code to store collation and typmod to
> > > pg_propgraph_property catalog and propagate it to property graph
> > > references in GRAPH_TABLE. Collation is used by
> > > assign_query_collations and assign_expr_collations to propagate
> > > collation up the query and expression tree respectively. typmod is
> > > used to report typmod of property reference from exprTypemod().
> > >
> > > While finishing code to create vertex-edge link quals, I found that we
> > > need to find and store the operator to be used for key matching in
> > > those quals. I think we have to do something similar to what primary
> > > key handling code does - find the equality operator when creating edge
> > > descriptor and store it in pg_propgraph_element. I am not sure whether
> > > we should add a dependency on the operator. I will look into this
> > > next.
> >
> > 0006 in the attached patch series completes this work. Now we find the
> > equality operators to be used for vertex-edge quals and save it in
> > pg_propgraph_element catalog and also add a dependency between the
> > edge element and the equality operators.
> >
> > 0008 - has WIP fix for \d and \d+
> >
> > --
> > Best Wishes,
> > Ashutosh Bapat
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
I'm looking at the catalog definition, I have some questions which
might be silly.
Each pg element can have multiple labels(whose properties belong
to the same pg element), can we have multiple elements share the
same label?
If we have a 1..* relation between element and label, I think maybe
we don't need _pg_propgraph_label_.
From the name _pg_propgraph_label_property_, I tend to think it is
referencing _pg_propgraph_label_, but actually it is referencing
_pg_propgraph_element_label_.
--
Regards
Junwang Zhao
Hi Junwang, > > I'm looking at the catalog definition, I have some questions which > might be silly. Thanks for your interest in SQL/PGQ. > > Each pg element can have multiple labels(whose properties belong > to the same pg element), can we have multiple elements share the > same label? Yes. A label can be shared between edges and vertices as well. > > If we have a 1..* relation between element and label, I think maybe > we don't need _pg_propgraph_label_. pg_propgraph_label is used to map a label name in a given property graph to its OID. pg_propgraph_element_label - can be used to find all the elements with a given label and all the labels of a given element. The relationship is many to many. > > From the name _pg_propgraph_label_property_, I tend to think it is > referencing _pg_propgraph_label_, but actually it is referencing > _pg_propgraph_element_label_. > Right. -- Best Wishes, Ashutosh Bapat
On Mon, Dec 16, 2024 at 6:14 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote: > > Hi Junwang, > > > > > I'm looking at the catalog definition, I have some questions which > > might be silly. > > Thanks for your interest in SQL/PGQ. > > > > > Each pg element can have multiple labels(whose properties belong > > to the same pg element), can we have multiple elements share the > > same label? > > Yes. A label can be shared between edges and vertices as well. > > > > > If we have a 1..* relation between element and label, I think maybe > > we don't need _pg_propgraph_label_. > > pg_propgraph_label is used to map a label name in a given property > graph to its OID. pg_propgraph_element_label - can be used to find all > the elements with a given label and all the labels of a given element. > The relationship is many to many. Ok, then it makes sense to me. Thanks for the explanation. > > > > > From the name _pg_propgraph_label_property_, I tend to think it is > > referencing _pg_propgraph_label_, but actually it is referencing > > _pg_propgraph_element_label_. > > > > Right. > > -- > Best Wishes, > Ashutosh Bapat -- Regards Junwang Zhao