public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ashutosh Bapat <[email protected]>
To: Noah Misch <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: [email protected]
Subject: Re: Wrong query result w/ propgraph single lateral col reference
Date: Wed, 8 Jul 2026 12:24:28 +0530
Message-ID: <CAExHW5sgk_QrxY475uYqrPyW_tMripfM3Z2OoUkkmkj8E_vECg@mail.gmail.com> (raw)
In-Reply-To: <CAExHW5tdnJDhUUXZ9V8n7aBnW2hqRC0MZgsHnuZj092om3PhQg@mail.gmail.com>
References: <[email protected]>
<CAExHW5sjypF-x1rcutfwxafBxw1mKt4FAQRMFTafMQaZQXAUAg@mail.gmail.com>
<CAExHW5tdnJDhUUXZ9V8n7aBnW2hqRC0MZgsHnuZj092om3PhQg@mail.gmail.com>
Hi,
Peter has committed several other patches. Here are rebased patches
for this thread.
On Fri, Jul 3, 2026 at 10:11 AM Ashutosh Bapat
<[email protected]> wrote:
Issue 1
> > >
> > > CREATE TABLE v (flag boolean, id int PRIMARY KEY, name text);
> > > INSERT INTO v VALUES (true,1,'a'), (false,2,'b'), (true,3,'c');
> > > CREATE PROPERTY GRAPH g VERTEX TABLES (v KEY (id) LABEL l PROPERTIES (id, flag, name));
> > >
> > > Findings:
> > >
> > > 0. A WHERE clause that is a single lateral col reference gives wrong query results:
> > >
> > > SELECT t.b, gt.name FROM (VALUES (false)) AS t(b),
> > > GRAPH_TABLE (g MATCH (x IS l) WHERE t.b COLUMNS (x.name AS name)) gt;
> > > -- got 2 rows, want 0
> > >
> > > Adding "AND true" corrects the result:
> > >
> > > SELECT t.b, gt.name FROM (VALUES (false)) AS t(b),
> > > GRAPH_TABLE (g MATCH (x IS l) WHERE t.b AND true COLUMNS (x.name AS name)) gt;
> > > -- got 0 rows, want 0
> > >
> > > 1. A WHERE clause that is a single property reference gives a spurious error:
> > >
> > > SELECT name FROM GRAPH_TABLE (g MATCH (x IS l) WHERE x.flag COLUMNS (x.name));
> > > -- ERROR: unrecognized node type: 63
> > >
> > > Adding "AND true" again corrects the result:
> > >
> > > SELECT name FROM GRAPH_TABLE (g MATCH (x IS l) WHERE x.flag AND true COLUMNS (x.name));
> > > -- got 2 rows, want 2
> > >
> > > For (0) and (1), Opus's opinion is that the right fix is:
> > >
> > > --- a/src/backend/rewrite/rewriteGraphTable.c
> > > +++ b/src/backend/rewrite/rewriteGraphTable.c
> > > @@ replace_property_refs(Oid propgraphid, Node *node, const List *mappings)
> > > context.mappings = mappings;
> > > context.propgraphid = propgraphid;
> > >
> > > - return expression_tree_mutator(node, replace_property_refs_mutator, &context);
> > > + return replace_property_refs_mutator(node, &context);
> > > }
> > >
> >
> > This matches the pattern in the other expression mutators, and also
> > fixes the problems. Thanks for the report, that was quite subtle.
>
> Fix attached here.
That's 0003 patch attached here.
Issue 2
>
> >
> > >
> > > 2. A property whose value is an untyped literal stays type unknown.
> > >
> > > CREATE PROPERTY GRAPH gu VERTEX TABLES (v KEY (id) LABEL lu PROPERTIES ('hi' AS p));
> > > SELECT p FROM GRAPH_TABLE (gu MATCH (n IS lu) COLUMNS (n.p));
> > > -- ERROR: failed to find conversion function from unknown to text
> > >
> >
> > I think property's data type should be set to text, not unknown. I
> > think we should combine the fix for this in
> > https://www.postgresql.org/message-id/[email protected]....
> >
>
> Attaching the fix here. But I will also report it on the other thread
> [2] and discussion can continue there.
>
The patch in that thread was committed. Attaching fix for this issue
as 0002 patch.
Issue 3
>
> >
> > >
> > > 4. [cosmetic] Duplicate property names found only via catalog unique key
> > >
> > > CREATE PROPERTY GRAPH g3 VERTEX TABLES (v KEY (id) LABEL lu PROPERTIES (flag, flag));
> > > -- ERROR: duplicate key value violates unique constraint "pg_propgraph_property_name_index"
> > >
> > > Opus thinks this is unintentional and cites lack of CommandCounterIncrement()
> > > between property inserts.
> >
> > We get the same error even if we split the duplicate properties across
> > two commands.
> > CREATE PROPERTY GRAPH g3 VERTEX TABLES (v KEY (id) LABEL lu PROPERTIES (flag));
> > alter property graph g3 alter vertex table v alter label lu add
> > properties(flag);
> >
> > I think we need to check for duplicate records in
> > pg_propgraph_label_property catalog in insert_property_record(). The
> > function already checks for duplicate records in
> > pg_propgraph_property. Additionally we might need
> > CommandCounterIncrement().
>
When investigating this issue I found that duplicate labels also have
the same issue. Fixed both the issues in patch 0001.
I have added a noop else in insert_label_record() after ereport()
since it ties the following code block well with the if condition. But
we can remove it if it's not improving readability.
There's slight inconsistency in the error messages for duplicate
labels and properties. In case of labels, we always report "label ...
already exists". But in case of properties we report two different
errors. We report "property \"%s\" specified more than once" when
duplicate properties appear in the same command. But we report
"property \"%s\" already exists" when duplicate properties appear
across commands. I think the difference is minor enough that we can
entirely ignore it and avoid spending more code on it. If we feel
strongly about being consistent, either we can detect duplicate labels
in the same command and report "label ... specified more than once" or
we can make the same error being reported in case of properties in
both the cases.
--
Best Wishes,
Ashutosh Bapat
Attachments:
[text/x-patch] v20260708-0003-replace_property_refs-ignores-the-root-of-.patch (8.3K, ../CAExHW5sgk_QrxY475uYqrPyW_tMripfM3Z2OoUkkmkj8E_vECg@mail.gmail.com/2-v20260708-0003-replace_property_refs-ignores-the-root-of-.patch)
download | inline diff:
From be38b872ec90bebdeed6fdf1a035378dc8f6e944 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email protected]>
Date: Thu, 2 Jul 2026 10:33:13 +0530
Subject: [PATCH v20260708 3/7] replace_property_refs() ignores the root of
expression tree
replace_property_refs() called expression_tree_mutator() with the root of the
expression tree as the input node. expression_tree_mutator() does not call the
mutator function on the root node, so the root node remains unchanged. If the
root node is a property reference or a lateral reference -- the two node kinds
that replace_property_refs_mutator() rewrites -- it is returned unchanged.
Modules after the rewriter do not know about property reference nodes resulting
in "ERROR: unrecognized node type: 63". Since varlevelsup of lateral references
is not incremented, they are not resolved correctly in the planner, leading to
many different symptoms. Fix this by calling replace_property_refs_mutator()
directly from replace_property_refs(), similar to how other mutator functions
do.
The only case when a property reference or a lateral reference can be
the root of a GRAPH_TABLE expression tree is when it is a bare property
reference or a bare lateral reference in the WHERE clause. The COLUMNS
clause is passed to replace_property_refs() as a targetlist. Every other
expression has at least one expression node covering the property
reference or a lateral reference in the expression tree. That explains
why this bug was not seen so far.
Author: Ashutosh Bapat <[email protected]>
Reported by: Noah Misch <[email protected]>
Fix suggested by: Noah Misch <[email protected]>
Discussion: https://www.postgresql.org/message-id/[email protected]
---
src/backend/rewrite/rewriteGraphTable.c | 2 +-
src/test/regress/expected/graph_table.out | 38 +++++++++++++++++------
src/test/regress/sql/graph_table.sql | 17 ++++++----
3 files changed, 41 insertions(+), 16 deletions(-)
diff --git a/src/backend/rewrite/rewriteGraphTable.c b/src/backend/rewrite/rewriteGraphTable.c
index 7db17bec312..cdb1f4c0dca 100644
--- a/src/backend/rewrite/rewriteGraphTable.c
+++ b/src/backend/rewrite/rewriteGraphTable.c
@@ -1159,7 +1159,7 @@ replace_property_refs(Oid propgraphid, Node *node, const List *mappings)
context.mappings = mappings;
context.propgraphid = propgraphid;
- return expression_tree_mutator(node, replace_property_refs_mutator, &context);
+ return replace_property_refs_mutator(node, &context);
}
/*
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index bd603ea0d77..a3d78a7ac43 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -248,12 +248,12 @@ SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers)->(o IS orders) COLUMNS
-- Use table with a column name same as a property in the property graph so as
-- to test resolution preferences. Property references are preferred over
-- lateral table references.
-CREATE TABLE x1 (a int, address text);
-INSERT INTO x1 VALUES (1, 'one'), (2, 'two');
+CREATE TABLE x1 (a int, address text, flag boolean);
+INSERT INTO x1 VALUES (1, 'one', true), (2, 'two', false);
SELECT * FROM x1, GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US' AND c.customer_id = x1.a)-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name, c.customer_id AS cid));
- a | address | customer_name | cid
----+---------+---------------+-----
- 1 | one | customer1 | 1
+ a | address | flag | customer_name | cid
+---+---------+------+---------------+-----
+ 1 | one | t | customer1 | 1
(1 row)
SELECT x1.a, g.* FROM x1, GRAPH_TABLE (myshop MATCH (x1 IS customers WHERE x1.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (x1.name AS customer_name, x1.customer_id AS cid, o.order_id)) g;
@@ -263,6 +263,13 @@ SELECT x1.a, g.* FROM x1, GRAPH_TABLE (myshop MATCH (x1 IS customers WHERE x1.ad
2 | customer1 | 1 | 1
(2 rows)
+-- bare lateral reference in WHERE clause
+SELECT * FROM x1, GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.customer_id = x1.a) WHERE x1.flag COLUMNS (c.name AS customer_name));
+ a | address | flag | customer_name
+---+---------+------+---------------
+ 1 | one | t | customer1
+(1 row)
+
-- lateral reference with multi-label pattern, which is rewritten as UNION of
-- path queries
SELECT x1.a, g.* FROM x1,
@@ -864,12 +871,12 @@ CREATE TABLE cv2 () INHERITS (pv);
INSERT INTO pv VALUES (1, 10);
INSERT INTO cv1 VALUES (2, 20);
INSERT INTO cv2 VALUES (3, 30);
-CREATE TABLE pe (id int, src int, dest int, val int);
+CREATE TABLE pe (id int, src int, dest int, val int, flag boolean);
CREATE TABLE ce1 () INHERITS (pe);
CREATE TABLE ce2 () INHERITS (pe);
-INSERT INTO pe VALUES (1, 1, 2, 100);
-INSERT INTO ce1 VALUES (2, 2, 3, 200);
-INSERT INTO ce2 VALUES (3, 3, 1, 300);
+INSERT INTO pe VALUES (1, 1, 2, 100, false);
+INSERT INTO ce1 VALUES (2, 2, 3, 200, false);
+INSERT INTO ce2 VALUES (3, 3, 1, 300, true);
CREATE PROPERTY GRAPH g3
NODE TABLES (
pv KEY (id)
@@ -887,6 +894,19 @@ SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe]->(d IS pv) COLUMNS (s.va
30 | 300 | 10
(3 rows)
+-- bare property reference in WHERE clause
+SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe WHERE e.flag]->(d IS pv) COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
+ val | val | val
+-----+-----+-----
+ 30 | 300 | 10
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe]->(d IS pv) WHERE e.flag COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
+ val | val | val
+-----+-----+-----
+ 30 | 300 | 10
+(1 row)
+
-- temporary property graph
CREATE TEMPORARY PROPERTY GRAPH gtmp
VERTEX TABLES (
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 5c8049ed242..6aacc2d4aa5 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -156,10 +156,12 @@ SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers)->(o IS orders) COLUMNS
-- Use table with a column name same as a property in the property graph so as
-- to test resolution preferences. Property references are preferred over
-- lateral table references.
-CREATE TABLE x1 (a int, address text);
-INSERT INTO x1 VALUES (1, 'one'), (2, 'two');
+CREATE TABLE x1 (a int, address text, flag boolean);
+INSERT INTO x1 VALUES (1, 'one', true), (2, 'two', false);
SELECT * FROM x1, GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US' AND c.customer_id = x1.a)-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name, c.customer_id AS cid));
SELECT x1.a, g.* FROM x1, GRAPH_TABLE (myshop MATCH (x1 IS customers WHERE x1.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (x1.name AS customer_name, x1.customer_id AS cid, o.order_id)) g;
+-- bare lateral reference in WHERE clause
+SELECT * FROM x1, GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.customer_id = x1.a) WHERE x1.flag COLUMNS (c.name AS customer_name));
-- lateral reference with multi-label pattern, which is rewritten as UNION of
-- path queries
SELECT x1.a, g.* FROM x1,
@@ -483,12 +485,12 @@ CREATE TABLE cv2 () INHERITS (pv);
INSERT INTO pv VALUES (1, 10);
INSERT INTO cv1 VALUES (2, 20);
INSERT INTO cv2 VALUES (3, 30);
-CREATE TABLE pe (id int, src int, dest int, val int);
+CREATE TABLE pe (id int, src int, dest int, val int, flag boolean);
CREATE TABLE ce1 () INHERITS (pe);
CREATE TABLE ce2 () INHERITS (pe);
-INSERT INTO pe VALUES (1, 1, 2, 100);
-INSERT INTO ce1 VALUES (2, 2, 3, 200);
-INSERT INTO ce2 VALUES (3, 3, 1, 300);
+INSERT INTO pe VALUES (1, 1, 2, 100, false);
+INSERT INTO ce1 VALUES (2, 2, 3, 200, false);
+INSERT INTO ce2 VALUES (3, 3, 1, 300, true);
CREATE PROPERTY GRAPH g3
NODE TABLES (
pv KEY (id)
@@ -499,6 +501,9 @@ CREATE PROPERTY GRAPH g3
DESTINATION KEY(dest) REFERENCES pv(id)
);
SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe]->(d IS pv) COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
+-- bare property reference in WHERE clause
+SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe WHERE e.flag]->(d IS pv) COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
+SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe]->(d IS pv) WHERE e.flag COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
-- temporary property graph
CREATE TEMPORARY PROPERTY GRAPH gtmp
VERTEX TABLES (
--
2.34.1
[text/x-patch] v20260708-0002-Resolve-unknown-type-literals-in-property-.patch (20.5K, ../CAExHW5sgk_QrxY475uYqrPyW_tMripfM3Z2OoUkkmkj8E_vECg@mail.gmail.com/3-v20260708-0002-Resolve-unknown-type-literals-in-property-.patch)
download | inline diff:
From 4195478723038fb6f9fb17e4a166dc5c0c0f690b Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email protected]>
Date: Wed, 29 Apr 2026 19:07:13 +0530
Subject: [PATCH v20260708 2/7] Resolve unknown-type literals in property
expressions
When a string literal is provided as a property expression, the data
type of the property was set to "unknown", which may lead to various
failures when the property is used in GRAPH_TABLE or when its data type
is compared against other properties with the same name. To fix this,
call resolveTargetListUnknowns() on the targetlist of new properties
being added to resolve unknown type literals.
Reported by: Noah Misch <[email protected]>
Author: Ashutosh Bapat <[email protected]>
Discussion: https://www.postgresql.org/message-id/[email protected]
---
src/backend/commands/propgraphcmds.c | 2 +
.../expected/create_property_graph.out | 37 +++++++++++++++----
.../regress/sql/create_property_graph.sql | 5 +++
3 files changed, 36 insertions(+), 8 deletions(-)
diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c
index 107c0449302..9c9f4f2b299 100644
--- a/src/backend/commands/propgraphcmds.c
+++ b/src/backend/commands/propgraphcmds.c
@@ -904,6 +904,8 @@ insert_property_records(Oid graphid, Oid ellabeloid, Oid pgerelid, const PropGra
table_close(rel, NoLock);
tp = transformTargetList(pstate, proplist, EXPR_KIND_PROPGRAPH_PROPERTY);
+ if (pstate->p_resolve_unknowns)
+ resolveTargetListUnknowns(pstate, tp);
assign_expr_collations(pstate, (Node *) tp);
/*
diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out
index f8730e96340..4a8b3d91219 100644
--- a/src/test/regress/expected/create_property_graph.out
+++ b/src/test/regress/expected/create_property_graph.out
@@ -351,6 +351,10 @@ CREATE PROPERTY GRAPH gt
SOURCE KEY (k1) REFERENCES v1(a)
DESTINATION KEY (k2) REFERENCES v2(m)
);
+-- data types of constant property values
+CREATE PROPERTY GRAPH glc VERTEX TABLES (
+ v1 KEY (a) LABEL l1 PROPERTIES ('foo' AS p1, 123 AS p2, 3.14 AS p3, true AS p4)
+);
-- information schema
SELECT * FROM information_schema.property_graphs ORDER BY property_graph_name;
property_graph_catalog | property_graph_schema | property_graph_name
@@ -361,8 +365,9 @@ SELECT * FROM information_schema.property_graphs ORDER BY property_graph_name;
regression | create_property_graph_tests | g4
regression | create_property_graph_tests | g5
regression | create_property_graph_tests | gc1
+ regression | create_property_graph_tests | glc
regression | create_property_graph_tests | gt
-(7 rows)
+(8 rows)
SELECT * FROM information_schema.pg_element_tables ORDER BY property_graph_name, element_table_alias;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | element_table_kind | table_catalog | table_schema | table_name | element_table_definition
@@ -387,10 +392,11 @@ SELECT * FROM information_schema.pg_element_tables ORDER BY property_graph_name,
regression | create_property_graph_tests | gc1 | tc1 | VERTEX | regression | create_property_graph_tests | tc1 |
regression | create_property_graph_tests | gc1 | tc2 | VERTEX | regression | create_property_graph_tests | tc2 |
regression | create_property_graph_tests | gc1 | tc3 | VERTEX | regression | create_property_graph_tests | tc3 |
+ regression | create_property_graph_tests | glc | v1 | VERTEX | regression | create_property_graph_tests | v1 |
regression | create_property_graph_tests | gt | e | EDGE | regression | create_property_graph_tests | e |
regression | create_property_graph_tests | gt | v1 | VERTEX | regression | create_property_graph_tests | v1 |
regression | create_property_graph_tests | gt | v2 | VERTEX | regression | create_property_graph_tests | v2 |
-(23 rows)
+(24 rows)
SELECT * FROM information_schema.pg_element_table_key_columns ORDER BY property_graph_name, element_table_alias, ordinal_position;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | column_name | ordinal_position
@@ -421,11 +427,12 @@ SELECT * FROM information_schema.pg_element_table_key_columns ORDER BY property_
regression | create_property_graph_tests | gc1 | tc1 | a | 1
regression | create_property_graph_tests | gc1 | tc2 | a | 1
regression | create_property_graph_tests | gc1 | tc3 | a | 1
+ regression | create_property_graph_tests | glc | v1 | a | 1
regression | create_property_graph_tests | gt | e | k1 | 1
regression | create_property_graph_tests | gt | e | k2 | 2
regression | create_property_graph_tests | gt | v1 | a | 1
regression | create_property_graph_tests | gt | v2 | m | 1
-(30 rows)
+(31 rows)
SELECT * FROM information_schema.pg_edge_table_components ORDER BY property_graph_name, edge_table_alias, edge_end DESC, ordinal_position;
property_graph_catalog | property_graph_schema | property_graph_name | edge_table_alias | vertex_table_alias | edge_end | edge_table_column_name | vertex_table_column_name | ordinal_position
@@ -475,10 +482,11 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | gc1 | tc1 | tc1
regression | create_property_graph_tests | gc1 | tc2 | tc2
regression | create_property_graph_tests | gc1 | tc3 | tc3
+ regression | create_property_graph_tests | glc | v1 | l1
regression | create_property_graph_tests | gt | e | e
regression | create_property_graph_tests | gt | v1 | v1
regression | create_property_graph_tests | gt | v2 | v2
-(25 rows)
+(26 rows)
SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_graph_name, element_table_alias, property_name;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | property_name | property_expression
@@ -529,6 +537,10 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g
regression | create_property_graph_tests | gc1 | tc2 | b | ((b)::character varying COLLATE "C")
regression | create_property_graph_tests | gc1 | tc3 | a | a
regression | create_property_graph_tests | gc1 | tc3 | b | (b)::character varying
+ regression | create_property_graph_tests | glc | v1 | p1 | 'foo'::text
+ regression | create_property_graph_tests | glc | v1 | p2 | 123
+ regression | create_property_graph_tests | glc | v1 | p3 | 3.14
+ regression | create_property_graph_tests | glc | v1 | p4 | true
regression | create_property_graph_tests | gt | e | c | c
regression | create_property_graph_tests | gt | e | k1 | k1
regression | create_property_graph_tests | gt | e | k2 | k2
@@ -536,7 +548,7 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g
regression | create_property_graph_tests | gt | v1 | b | b
regression | create_property_graph_tests | gt | v2 | m | m
regression | create_property_graph_tests | gt | v2 | n | n
-(53 rows)
+(57 rows)
SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_name, label_name, property_name;
property_graph_catalog | property_graph_schema | property_graph_name | label_name | property_name
@@ -593,6 +605,10 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam
regression | create_property_graph_tests | gc1 | tc2 | b
regression | create_property_graph_tests | gc1 | tc3 | a
regression | create_property_graph_tests | gc1 | tc3 | b
+ regression | create_property_graph_tests | glc | l1 | p1
+ regression | create_property_graph_tests | glc | l1 | p2
+ regression | create_property_graph_tests | glc | l1 | p3
+ regression | create_property_graph_tests | glc | l1 | p4
regression | create_property_graph_tests | gt | e | c
regression | create_property_graph_tests | gt | e | k1
regression | create_property_graph_tests | gt | e | k2
@@ -600,7 +616,7 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam
regression | create_property_graph_tests | gt | v1 | b
regression | create_property_graph_tests | gt | v2 | m
regression | create_property_graph_tests | gt | v2 | n
-(59 rows)
+(63 rows)
SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_name;
property_graph_catalog | property_graph_schema | property_graph_name | label_name
@@ -627,10 +643,11 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | gc1 | tc1
regression | create_property_graph_tests | gc1 | tc2
regression | create_property_graph_tests | gc1 | tc3
+ regression | create_property_graph_tests | glc | l1
regression | create_property_graph_tests | gt | e
regression | create_property_graph_tests | gt | v1
regression | create_property_graph_tests | gt | v2
-(25 rows)
+(26 rows)
SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_name, property_name;
property_graph_catalog | property_graph_schema | property_graph_name | property_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier
@@ -666,6 +683,10 @@ SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_
regression | create_property_graph_tests | gc1 | eb | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | eb
regression | create_property_graph_tests | gc1 | ek1 | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | ek1
regression | create_property_graph_tests | gc1 | ek2 | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | ek2
+ regression | create_property_graph_tests | glc | p1 | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | p1
+ regression | create_property_graph_tests | glc | p2 | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | p2
+ regression | create_property_graph_tests | glc | p3 | numeric | | | | | | regression | | | | | | | | | regression | pg_catalog | numeric | | | | | p3
+ regression | create_property_graph_tests | glc | p4 | boolean | | | | | | regression | | | | | | | | | regression | pg_catalog | bool | | | | | p4
regression | create_property_graph_tests | gt | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
regression | create_property_graph_tests | gt | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b
regression | create_property_graph_tests | gt | c | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | c
@@ -673,7 +694,7 @@ SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_
regression | create_property_graph_tests | gt | k2 | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | k2
regression | create_property_graph_tests | gt | m | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | m
regression | create_property_graph_tests | gt | n | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | n
-(38 rows)
+(42 rows)
SELECT * FROM information_schema.pg_property_graph_privileges WHERE grantee LIKE 'regress%' ORDER BY property_graph_name, grantor, grantee, privilege_type;
grantor | grantee | property_graph_catalog | property_graph_schema | property_graph_name | privilege_type | is_grantable
diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql
index 72885be949a..1667896f558 100644
--- a/src/test/regress/sql/create_property_graph.sql
+++ b/src/test/regress/sql/create_property_graph.sql
@@ -290,6 +290,11 @@ CREATE PROPERTY GRAPH gt
DESTINATION KEY (k2) REFERENCES v2(m)
);
+-- data types of constant property values
+CREATE PROPERTY GRAPH glc VERTEX TABLES (
+ v1 KEY (a) LABEL l1 PROPERTIES ('foo' AS p1, 123 AS p2, 3.14 AS p3, true AS p4)
+);
+
-- information schema
SELECT * FROM information_schema.property_graphs ORDER BY property_graph_name;
--
2.34.1
[text/x-patch] v20260708-0001-Report-duplicate-property-and-label-names-.patch (6.4K, ../CAExHW5sgk_QrxY475uYqrPyW_tMripfM3Z2OoUkkmkj8E_vECg@mail.gmail.com/4-v20260708-0001-Report-duplicate-property-and-label-names-.patch)
download | inline diff:
From cb435e0f7d8f5936a2ec05bf8b9f9eaa2bd0b008 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email protected]>
Date: Thu, 2 Jul 2026 15:27:15 +0530
Subject: [PATCH v20260708 1/7] Report duplicate property and label names with
a proper error
Adding a property with the same name multiple times to a label on an
element, either within a single PROPERTIES clause or across statements
via ALTER PROPERTY GRAPH ... ADD PROPERTIES, previously failed with a
unique-index violation on pg_propgraph_label_property. The same class
of bug existed for labels: listing the same label multiple times on one
element in CREATE PROPERTY GRAPH, or adding a label to an element that
already has it via ALTER PROPERTY GRAPH ... ADD LABEL, failed with a
unique-index violation on pg_propgraph_element_label. Detect the
duplicates up front and raise a friendlier error in both cases.
For properties, the cross-statement duplicate is caught by a syscache
probe before insertion. The in-clause duplicate could have been caught
by the same probe if we issued a CommandCounterIncrement() between
property inserts, but that forces a catalog invalidation per property
purely to detect a condition we can check for free on the in-memory
target list. The list-based check is only needed when the properties
are listed explicitly; when they are derived from the table's
attributes, names are already unique.
For labels, a single syscache probe on pg_propgraph_element_label
suffices for both the in-clause and cross-statement cases because
insert_element_record() already issues CommandCounterIncrement()
between successive label inserts.
Author: Ashutosh Bapat <[email protected]>
Reported by: Noah Misch <[email protected]>
Discussion: https://www.postgresql.org/message-id/[email protected]
---
src/backend/commands/propgraphcmds.c | 37 +++++++++++++++++--
.../expected/create_property_graph.out | 14 +++++++
.../regress/sql/create_property_graph.sql | 12 ++++++
3 files changed, 60 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c
index 6939a448895..107c0449302 100644
--- a/src/backend/commands/propgraphcmds.c
+++ b/src/backend/commands/propgraphcmds.c
@@ -784,6 +784,13 @@ insert_label_record(Oid graphid, Oid peoid, const char *label)
/*
* Insert into pg_propgraph_element_label
*/
+ if (SearchSysCacheExists2(PROPGRAPHELEMENTLABELELEMENTLABEL,
+ ObjectIdGetDatum(peoid),
+ ObjectIdGetDatum(labeloid)))
+ ereport(ERROR,
+ errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("label \"%s\" already exists", label));
+ else
{
Relation rel;
Datum values[Natts_pg_propgraph_element_label] = {0};
@@ -899,12 +906,30 @@ insert_property_records(Oid graphid, Oid ellabeloid, Oid pgerelid, const PropGra
tp = transformTargetList(pstate, proplist, EXPR_KIND_PROPGRAPH_PROPERTY);
assign_expr_collations(pstate, (Node *) tp);
- foreach(lc, tp)
+ /*
+ * When properties are derived from the table's attributes, names are
+ * already unique. Reject duplicate property names within an explicit
+ * PROPERTIES clause. Do this after transformTargetList() so that any
+ * names derived by transformTargetList() are considered.
+ */
+ if (!properties->all)
{
- TargetEntry *te = lfirst_node(TargetEntry, lc);
+ List *seen = NIL;
- insert_property_record(graphid, ellabeloid, pgerelid, te->resname, te->expr);
+ foreach_node(TargetEntry, te, tp)
+ {
+ String *name = makeString(te->resname);
+
+ if (list_member(seen, name))
+ ereport(ERROR,
+ errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("property \"%s\" specified more than once", te->resname));
+ seen = lappend(seen, name);
+ }
}
+
+ foreach_node(TargetEntry, te, tp)
+ insert_property_record(graphid, ellabeloid, pgerelid, te->resname, te->expr);
}
/*
@@ -1001,6 +1026,12 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
/*
* Insert into pg_propgraph_label_property
*/
+ if (SearchSysCacheExists2(PROPGRAPHLABELPROP, ObjectIdGetDatum(ellabeloid),
+ ObjectIdGetDatum(propoid)))
+ ereport(ERROR,
+ errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("property \"%s\" already exists", propname));
+ else
{
Relation rel;
Datum values[Natts_pg_propgraph_label_property] = {0};
diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out
index 3638f7f9f68..f8730e96340 100644
--- a/src/test/regress/expected/create_property_graph.out
+++ b/src/test/regress/expected/create_property_graph.out
@@ -207,6 +207,20 @@ CREATE PROPERTY GRAPH gx
);
DROP PROPERTY GRAPH gx;
DROP TABLE t1x, t2x;
+CREATE PROPERTY GRAPH gx
+ VERTEX TABLES (
+ t1 KEY (a) LABEL l1 PROPERTIES (a AS p, a AS p) -- duplicate property on label
+ );
+ERROR: property "p" specified more than once
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS i_j); -- duplicate property on label
+ERROR: property "i_j" already exists
+CREATE PROPERTY GRAPH gx
+ VERTEX TABLES (
+ t1 KEY (a) LABEL l1 LABEL l1 -- duplicate label on element
+ );
+ERROR: label "l1" already exists
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t3 ADD LABEL t3l1 NO PROPERTIES; -- duplicate label on element
+ERROR: label "t3l1" already exists
CREATE PROPERTY GRAPH gx
VERTEX TABLES (
t1 KEY (a) LABEL l1 PROPERTIES (a, a AS aa),
diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql
index 5262971342c..72885be949a 100644
--- a/src/test/regress/sql/create_property_graph.sql
+++ b/src/test/regress/sql/create_property_graph.sql
@@ -161,6 +161,18 @@ CREATE PROPERTY GRAPH gx
DROP PROPERTY GRAPH gx;
DROP TABLE t1x, t2x;
+CREATE PROPERTY GRAPH gx
+ VERTEX TABLES (
+ t1 KEY (a) LABEL l1 PROPERTIES (a AS p, a AS p) -- duplicate property on label
+ );
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS i_j); -- duplicate property on label
+
+CREATE PROPERTY GRAPH gx
+ VERTEX TABLES (
+ t1 KEY (a) LABEL l1 LABEL l1 -- duplicate label on element
+ );
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t3 ADD LABEL t3l1 NO PROPERTIES; -- duplicate label on element
+
CREATE PROPERTY GRAPH gx
VERTEX TABLES (
t1 KEY (a) LABEL l1 PROPERTIES (a, a AS aa),
base-commit: 8daeaa9b642c3c23cbc516da80d50aade6f4dc07
--
2.34.1
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Wrong query result w/ propgraph single lateral col reference
In-Reply-To: <CAExHW5sgk_QrxY475uYqrPyW_tMripfM3Z2OoUkkmkj8E_vECg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox