Hi Peter, All, A label in a property graph can be shared by both vertex and edge elements. When such a label is used in a view, it can be rendered useless by dropping that label from all vertex elements or edge elements as follows.
To run the reproducer you need to first run graph_table.sql from regression. CREATE VIEW v_empty_label AS SELECT * FROM GRAPH_TABLE (g1 MATCH (v WHERE v.vprop1 = 10) COLUMNS (v.elname)); ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v1 DROP LABEL l1; ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v2 DROP LABEL l1; ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v3 DROP LABEL l1; SELECT * FROM v_empty_label; ERROR: no property graph element of type "vertex" has label "l1" associated with it in property graph "g1" The three ALTER TABLE statements drop label l1 from all the vertex elements. But that does not lead to removing that label from the property graph since there are edge elements associated with that label. The SELECT at the end throws an error because the rewriter can not find an element associated with the label mentioned in the view. Myself and Peter this problem in PGConf.dev and thought that the error is an artifact of the term "vertex labels" (or edge label) in the standard; a term which is not properly defined in the standard. If a label is associated with an element types (vertex or edge) in the catalog itself i.e. element type is also part of the unique key of catalog pg_propgraph_label, we will be able to add a dependency between the view and the element type specific label. This in turn will prohibit dropping the element type specific label from the catalog. The view won't be rendered invalid then. This isn't that straight forward since such a label still needs to have the same properties associated with it independent of the element type. So we need pg_propgraph_label to contain only the label name and have a separate catalog pg_proprgaph_elem_type_label (or some such) which associates a label with an element type. The view should add dependency on the row in the latter catalog. Otherwise, standard needs to define the result of the query when a label exists but is not associated with any element of required type - which essentially would mean returning 0 rows since there are no elements associated with the label. I think we need the standard to define the behaviour in such a case. Attached patch adds a test case to graph_table.sql showing this problem. We may want to commit the test case so that someone coming across the issue knows that this is the expected behaviour pending for clarification from the standard. Oracle prohibits sharing labels across vertex and edge elements, but other graph databases allow it and so does the SQL/PGQ standard. I think it's a useful feature, so I'm not thinking of doing it the Oracle way myself. But if there are more people who think otherwise, I am ok with it. [1] https://www.postgresql.org/message-id/CAExHW5twGP5Zuk4Zch4kz8XDrSpckWQipMs=ysaj8gmqna2...@mail.gmail.com -- Best Wishes, Ashutosh Bapat
From f084cb6219a81f43c3cefb048dbf133888b2e952 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat <[email protected]> Date: Thu, 28 May 2026 02:47:33 +0530 Subject: [PATCH v20260807] View referencing labels shared by vertex and edge tables Add a test for view containing labels which are shared by both vertex and edge tables. When such a label is dropped from only vertex tables or only edge tables, the view may be rendered invalid because it does not find any elements associated with the label. The fix depends upon how the SQL/PGQ standard specifies the behaviour in such a case. Author: Ashutosh Bapat <[email protected]> --- src/test/regress/expected/graph_table.out | 26 +++++++++++++++++++++-- src/test/regress/sql/graph_table.sql | 20 +++++++++++++++-- 2 files changed, 42 insertions(+), 4 deletions(-) diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out index cde3114ebf4..924269babc2 100644 --- a/src/test/regress/expected/graph_table.out +++ b/src/test/regress/expected/graph_table.out @@ -999,8 +999,7 @@ ALTER PROPERTY GRAPH myshop ALTER VERTEX TABLE products ERROR: cannot drop property price of property graph myshop because other objects depend on it DETAIL: view customers_us depends on property price of property graph myshop HINT: Use DROP ... CASCADE to drop the dependent objects too. --- ruleutils reverse parsing -SELECT pg_get_viewdef('customers_us'::regclass); +SELECT pg_get_viewdef('customers_us'::regclass); -- ruleutils reverse parsing pg_get_viewdef ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT g.customer_name, + @@ -1012,6 +1011,29 @@ SELECT pg_get_viewdef('customers_us'::regclass); ORDER BY g.customer_name, g.product_name; (1 row) +-- l1 is shared by all vertex tables and edge tables. Dropping it only from all +-- vertex tables renders a view unusable. This is because the standard considers +-- a label shared between vertex labels and edge labels as two different labels +-- vertex label and edge label respectively. Further note that the standard +-- still requires the two labels to have the same properties associated with +-- them. We need the standard to clarify the behaviour in this case: should we +-- prohibit dropping an element specific label or return no rows when the label +-- exists but is not associated with any element of required type. +CREATE VIEW v_shared_label AS SELECT * FROM GRAPH_TABLE (g1 MATCH (v IS l1) COLUMNS (v.elname)); +BEGIN; +ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v1 DROP LABEL l1; +ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v2 DROP LABEL l1; +ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v3 DROP LABEL l1; +SELECT * FROM v_shared_label; +ERROR: no property graph element of type "vertex" has label "l1" associated with it in property graph "g1" +ROLLBACK; +SELECT pg_get_viewdef('v_shared_label'::regclass); -- ruleutils reverse parsing + pg_get_viewdef +------------------------------------------------------------------------ + SELECT elname + + FROM GRAPH_TABLE (g1 MATCH (v IS l1) COLUMNS (v.elname AS elname)); +(1 row) + -- test view/graph nesting CREATE VIEW customers_view AS SELECT customer_id, 'redacted' || customer_id AS name_redacted, address FROM customers; SELECT * FROM customers; diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql index 7a4189833d8..013c48de5c8 100644 --- a/src/test/regress/sql/graph_table.sql +++ b/src/test/regress/sql/graph_table.sql @@ -561,8 +561,24 @@ ALTER PROPERTY GRAPH myshop ALTER VERTEX TABLE customers ALTER LABEL customers DROP PROPERTIES (address); -- error ALTER PROPERTY GRAPH myshop ALTER VERTEX TABLE products ALTER LABEL products DROP PROPERTIES (price); -- error --- ruleutils reverse parsing -SELECT pg_get_viewdef('customers_us'::regclass); +SELECT pg_get_viewdef('customers_us'::regclass); -- ruleutils reverse parsing + +-- l1 is shared by all vertex tables and edge tables. Dropping it only from all +-- vertex tables renders a view unusable. This is because the standard considers +-- a label shared between vertex labels and edge labels as two different labels +-- vertex label and edge label respectively. Further note that the standard +-- still requires the two labels to have the same properties associated with +-- them. We need the standard to clarify the behaviour in this case: should we +-- prohibit dropping an element specific label or return no rows when the label +-- exists but is not associated with any element of required type. +CREATE VIEW v_shared_label AS SELECT * FROM GRAPH_TABLE (g1 MATCH (v IS l1) COLUMNS (v.elname)); +BEGIN; +ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v1 DROP LABEL l1; +ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v2 DROP LABEL l1; +ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v3 DROP LABEL l1; +SELECT * FROM v_shared_label; +ROLLBACK; +SELECT pg_get_viewdef('v_shared_label'::regclass); -- ruleutils reverse parsing -- test view/graph nesting base-commit: bf80a4c2d238834073b09c231294c4479157f16e -- 2.34.1
