pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed(SQL/PGQ) Clean up orphaned properties when dropping a label
10+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-07-05 45d4c917ff 0e4f0827f6 Fix properties orphaned by dropping a label
* (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-06-05 14:29 zengman <zengman@halodbtech.com>
0 siblings, 1 reply; 10+ messages in thread
From: zengman @ 2026-06-05 14:29 UTC (permalink / raw)
To: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; +Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; Peter Eisentraut <peter@eisentraut.org>
Hi all,
I noticed that ALTER PROPERTY GRAPH ... DROP LABEL doesn't clean up
orphaned pg_propgraph_property entries. The cleanup condition in
RemoveRelations() only checks for drop_properties,
drop_vertex_tables, and drop_edge_tables, but not drop_label.
Before fix:
```sql
postgres@zxm-VMware-Virtual-Platform:~/code/postgres$ psql
psql (19beta1)
Type "help" for help.
postgres=# CREATE TABLE v4 (a int PRIMARY KEY, b int, c int);
CREATE TABLE
postgres=# CREATE PROPERTY GRAPH g5
VERTEX TABLES (
v4 LABEL l1 PROPERTIES (a, b, c)
LABEL l2 PROPERTIES (a)
);
CREATE PROPERTY GRAPH
postgres=# ALTER PROPERTY GRAPH g5 ALTER VERTEX TABLE v4 DROP LABEL l1;
ALTER PROPERTY GRAPH
postgres=# SELECT pgpname FROM pg_propgraph_property
WHERE pgppgid = 'g5'::regclass ORDER BY pgpname;
pgpname
---------
a
b
c
(3 rows)
```
After fix:
```sql
postgres@zxm-VMware-Virtual-Platform:~/code/postgres$ psql
psql (19beta1)
Type "help" for help.
postgres=# CREATE TABLE v4 (a int PRIMARY KEY, b int, c int);
CREATE TABLE
postgres=# CREATE PROPERTY GRAPH g5
VERTEX TABLES (
v4 LABEL l1 PROPERTIES (a, b, c)
LABEL l2 PROPERTIES (a)
);
CREATE PROPERTY GRAPH
postgres=# ALTER PROPERTY GRAPH g5 ALTER VERTEX TABLE v4 DROP LABEL l1;
ALTER PROPERTY GRAPH
postgres=# SELECT pgpname FROM pg_propgraph_property
WHERE pgppgid = 'g5'::regclass ORDER BY pgpname;
pgpname
---------
a
(1 row)
```
```sql
CREATE TABLE v4 (a int PRIMARY KEY, b int, c int);
CREATE PROPERTY GRAPH g5
VERTEX TABLES (
v4 LABEL l1 PROPERTIES (a, b, c)
LABEL l2 PROPERTIES (a)
);
ALTER PROPERTY GRAPH g5 ALTER VERTEX TABLE v4 DROP LABEL l1;
SELECT pgpname FROM pg_propgraph_property
WHERE pgppgid = 'g5'::regclass ORDER BY pgpname;
```
--
regards,
Man Zeng
Attachments:
0001-Clean-up-orphaned-properties-when-dropping-a-label.patch (application/octet-stream, 3.0K) download
From 6862fcd91808602317376e8fe24f28fcfc4de063 Mon Sep 17 00:00:00 2001
From: Man Zeng <zengman@halodbtech.com>
Date: Fri, 5 Jun 2026 22:13:47 +0800
Subject: [PATCH] Clean up orphaned properties when dropping a label
The orphaned property cleanup in RemoveRelations() was missing
the drop_label case, leaving properties that no label references.
---
src/backend/commands/propgraphcmds.c | 2 +-
src/test/regress/expected/graph_table.out | 18 ++++++++++++++++++
src/test/regress/sql/graph_table.sql | 14 ++++++++++++++
3 files changed, 33 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c
index cc516e27020..7c8f397afbf 100644
--- a/src/backend/commands/propgraphcmds.c
+++ b/src/backend/commands/propgraphcmds.c
@@ -1638,7 +1638,7 @@ AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt)
}
/* Remove any orphaned pg_propgraph_property entries */
- if (stmt->drop_properties || stmt->drop_vertex_tables || stmt->drop_edge_tables)
+ if (stmt->drop_label || stmt->drop_properties || stmt->drop_vertex_tables || stmt->drop_edge_tables)
{
foreach_oid(propoid, get_graph_property_ids(pgrelid))
{
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index cc6d80afd82..9617b4aa3e8 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -1044,4 +1044,22 @@ SELECT src.vname, count(*) FROM v1 AS src
v13 | 1
(3 rows)
+-- DROP LABEL should clean up orphaned properties
+CREATE TABLE v4 (a int PRIMARY KEY, b int, c int);
+INSERT INTO v4 VALUES (1, 2, 3);
+CREATE PROPERTY GRAPH g5
+ VERTEX TABLES (
+ v4 LABEL l1 PROPERTIES (a, b, c)
+ LABEL l2 PROPERTIES (a)
+ );
+ALTER PROPERTY GRAPH g5 ALTER VERTEX TABLE v4 DROP LABEL l1;
+SELECT pgpname FROM pg_propgraph_property
+ WHERE pgppgid = 'g5'::regclass ORDER BY pgpname;
+ pgpname
+---------
+ a
+(1 row)
+
+DROP PROPERTY GRAPH g5;
+DROP TABLE v4;
-- leave the objects behind for pg_upgrade/pg_dump tests
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 0e381ec72bc..ab89bdc1553 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -596,4 +596,18 @@ SELECT src.vname, count(*) FROM v1 AS src
HAVING count(*) >= (SELECT count(*) FROM GRAPH_TABLE (g1 MATCH (a IS vl1 | vl2) COLUMNS (a.vname AS n)) WHERE n = src.vname)
ORDER BY vname;
+-- DROP LABEL should clean up orphaned properties
+CREATE TABLE v4 (a int PRIMARY KEY, b int, c int);
+INSERT INTO v4 VALUES (1, 2, 3);
+CREATE PROPERTY GRAPH g5
+ VERTEX TABLES (
+ v4 LABEL l1 PROPERTIES (a, b, c)
+ LABEL l2 PROPERTIES (a)
+ );
+ALTER PROPERTY GRAPH g5 ALTER VERTEX TABLE v4 DROP LABEL l1;
+SELECT pgpname FROM pg_propgraph_property
+ WHERE pgppgid = 'g5'::regclass ORDER BY pgpname;
+DROP PROPERTY GRAPH g5;
+DROP TABLE v4;
+
-- leave the objects behind for pg_upgrade/pg_dump tests
--
2.45.2
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-06-11 03:55 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
parent: zengman <zengman@halodbtech.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ashutosh Bapat @ 2026-06-11 03:55 UTC (permalink / raw)
To: zengman <zengman@halodbtech.com>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; Peter Eisentraut <peter@eisentraut.org>
On Fri, Jun 5, 2026 at 7:59 PM zengman <zengman@halodbtech.com> wrote:
>
> Hi all,
>
> I noticed that ALTER PROPERTY GRAPH ... DROP LABEL doesn't clean up
> orphaned pg_propgraph_property entries. The cleanup condition in
> RemoveRelations() only checks for drop_properties,
> drop_vertex_tables, and drop_edge_tables, but not drop_label.
>
> Before fix:
>
> ```sql
> postgres@zxm-VMware-Virtual-Platform:~/code/postgres$ psql
> psql (19beta1)
> Type "help" for help.
>
> postgres=# CREATE TABLE v4 (a int PRIMARY KEY, b int, c int);
> CREATE TABLE
> postgres=# CREATE PROPERTY GRAPH g5
> VERTEX TABLES (
> v4 LABEL l1 PROPERTIES (a, b, c)
> LABEL l2 PROPERTIES (a)
> );
> CREATE PROPERTY GRAPH
> postgres=# ALTER PROPERTY GRAPH g5 ALTER VERTEX TABLE v4 DROP LABEL l1;
> ALTER PROPERTY GRAPH
> postgres=# SELECT pgpname FROM pg_propgraph_property
> WHERE pgppgid = 'g5'::regclass ORDER BY pgpname;
> pgpname
> ---------
> a
> b
> c
> (3 rows)
> ```
>
> After fix:
> ```sql
> postgres@zxm-VMware-Virtual-Platform:~/code/postgres$ psql
> psql (19beta1)
> Type "help" for help.
>
> postgres=# CREATE TABLE v4 (a int PRIMARY KEY, b int, c int);
> CREATE TABLE
> postgres=# CREATE PROPERTY GRAPH g5
> VERTEX TABLES (
> v4 LABEL l1 PROPERTIES (a, b, c)
> LABEL l2 PROPERTIES (a)
> );
> CREATE PROPERTY GRAPH
> postgres=# ALTER PROPERTY GRAPH g5 ALTER VERTEX TABLE v4 DROP LABEL l1;
> ALTER PROPERTY GRAPH
> postgres=# SELECT pgpname FROM pg_propgraph_property
> WHERE pgppgid = 'g5'::regclass ORDER BY pgpname;
> pgpname
> ---------
> a
> (1 row)
> ```
>
> ```sql
> CREATE TABLE v4 (a int PRIMARY KEY, b int, c int);
> CREATE PROPERTY GRAPH g5
> VERTEX TABLES (
> v4 LABEL l1 PROPERTIES (a, b, c)
> LABEL l2 PROPERTIES (a)
> );
> ALTER PROPERTY GRAPH g5 ALTER VERTEX TABLE v4 DROP LABEL l1;
> SELECT pgpname FROM pg_propgraph_property
> WHERE pgppgid = 'g5'::regclass ORDER BY pgpname;
> ```
Thanks for the report and the patch. The fix is on the right track. I
changed a few things as follows
a. we usually add operands to the same operator at the end, not at the
beginning.
b. create_property_graph.sql, which tests all property graph DDLs, is
the right place to add these tests. graph_table.sql tests graph query.
I have used an existing property graph in create_property_graph.sql in
the test. It removes a label from the property graph which has two
properties associated with it, one that gets orphaned and one that
doesn't. We can verify that the orphaned property gets dropped from
the property graph but not the other one by information schema query
outputs later. So didn't add any separate verification step after the
DDL.
Attached patch with those changes.
--
Best Wishes,
Ashutosh Bapat
Attachments:
v20260611-0010-Properties-orphaned-by-dropping-a-label.patch (text/x-patch, 16.1K) download
From 94f2c313519d56ba3c6ea2bf298d2852628dd060 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Wed, 10 Jun 2026 22:08:49 +0530
Subject: [PATCH v20260611 10/10] Properties orphaned by dropping a label
AlterPropGraph() cleans up properties that are orphaned by dropping an element
or properties associated with an element. But it doesn't clean up properties
that are orphaned by dropping a label. Fix this missing case.
Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Author: zengman <zengman@halodbtech.com>.
Reported by: zengman <zengman@halodbtech.com>
---
src/backend/commands/propgraphcmds.c | 3 ++-
.../expected/create_property_graph.out | 23 +++++++++----------
.../regress/sql/create_property_graph.sql | 5 ++++
3 files changed, 18 insertions(+), 13 deletions(-)
diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c
index fc998b3e244..1041be05189 100644
--- a/src/backend/commands/propgraphcmds.c
+++ b/src/backend/commands/propgraphcmds.c
@@ -1673,7 +1673,8 @@ AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt)
}
/* Remove any orphaned pg_propgraph_property entries */
- if (stmt->drop_properties || stmt->drop_vertex_tables || stmt->drop_edge_tables)
+ if (stmt->drop_properties || stmt->drop_vertex_tables ||
+ stmt->drop_edge_tables || stmt->drop_label)
{
foreach_oid(propoid, get_graph_property_ids(pgrelid))
{
diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out
index 1e733f9b572..60afeeac175 100644
--- a/src/test/regress/expected/create_property_graph.out
+++ b/src/test/regress/expected/create_property_graph.out
@@ -90,6 +90,11 @@ ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k *
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (k);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (yy); -- error
ERROR: property graph "g4" element "t2" label "t2" has no property "yy"
+-- Dropping a label should drop only orphaned properties. zz is orphaned because
+-- it is only associated with the dropped label t3l2, while x is not orphaned
+-- because it remains associated with t3l1. We will verify this in the
+-- information schema queries outputs below.
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t3 DROP LABEL t3l2;
CREATE TABLE t11 (a int PRIMARY KEY);
CREATE TABLE t12 (b int PRIMARY KEY);
CREATE TABLE t13 (
@@ -447,7 +452,6 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | g4 | t1 | t1
regression | create_property_graph_tests | g4 | t2 | t2
regression | create_property_graph_tests | g4 | t3 | t3l1
- regression | create_property_graph_tests | g4 | t3 | t3l2
regression | create_property_graph_tests | g5 | t11 | t11
regression | create_property_graph_tests | g5 | t12 | t12
regression | create_property_graph_tests | g5 | t13 | t13
@@ -459,7 +463,7 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | gt | e | e
regression | create_property_graph_tests | gt | v1 | v1
regression | create_property_graph_tests | gt | v2 | v2
-(26 rows)
+(25 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
@@ -493,7 +497,6 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g
regression | create_property_graph_tests | g4 | t2 | kk | (k * 2)
regression | create_property_graph_tests | g4 | t3 | x | x
regression | create_property_graph_tests | g4 | t3 | yy | y
- regression | create_property_graph_tests | g4 | t3 | zz | z
regression | create_property_graph_tests | g5 | t11 | a | a
regression | create_property_graph_tests | g5 | t12 | b | b
regression | create_property_graph_tests | g5 | t13 | c | c
@@ -518,7 +521,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
-(54 rows)
+(53 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
@@ -558,8 +561,6 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam
regression | create_property_graph_tests | g4 | t2 | kk
regression | create_property_graph_tests | g4 | t3l1 | x
regression | create_property_graph_tests | g4 | t3l1 | yy
- regression | create_property_graph_tests | g4 | t3l2 | x
- regression | create_property_graph_tests | g4 | t3l2 | zz
regression | create_property_graph_tests | g5 | t11 | a
regression | create_property_graph_tests | g5 | t12 | b
regression | create_property_graph_tests | g5 | t13 | c
@@ -584,7 +585,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
-(61 rows)
+(59 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
@@ -603,7 +604,6 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | g4 | t1
regression | create_property_graph_tests | g4 | t2
regression | create_property_graph_tests | g4 | t3l1
- regression | create_property_graph_tests | g4 | t3l2
regression | create_property_graph_tests | g5 | t11
regression | create_property_graph_tests | g5 | t12
regression | create_property_graph_tests | g5 | t13
@@ -615,7 +615,7 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | gt | e
regression | create_property_graph_tests | gt | v1
regression | create_property_graph_tests | gt | v2
-(26 rows)
+(25 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
@@ -641,7 +641,6 @@ SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_
regression | create_property_graph_tests | g4 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t
regression | create_property_graph_tests | g4 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
regression | create_property_graph_tests | g4 | yy | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | yy
- regression | create_property_graph_tests | g4 | zz | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | zz
regression | create_property_graph_tests | g5 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
regression | create_property_graph_tests | g5 | b | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | b
regression | create_property_graph_tests | g5 | c | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | c
@@ -659,7 +658,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
-(39 rows)
+(38 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
@@ -809,7 +808,7 @@ CREATE PROPERTY GRAPH create_property_graph_tests.g4
VERTEX TABLES (
t1 KEY (a) NO PROPERTIES,
t2 KEY (i) PROPERTIES ((i + j) AS i_j, (k * 2) AS kk),
- t3 KEY (x) LABEL t3l1 PROPERTIES (x, y AS yy) LABEL t3l2 PROPERTIES (x, z AS zz)
+ t3 KEY (x) LABEL t3l1 PROPERTIES (x, y AS yy)
)
EDGE TABLES (
e1 KEY (a, i) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (i) REFERENCES t2 (i) PROPERTIES (a, i, t),
diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql
index 0c3aa81e9ca..e7826548808 100644
--- a/src/test/regress/sql/create_property_graph.sql
+++ b/src/test/regress/sql/create_property_graph.sql
@@ -80,6 +80,11 @@ CREATE PROPERTY GRAPH g4
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS kk);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (k);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (yy); -- error
+-- Dropping a label should drop only orphaned properties. zz is orphaned because
+-- it is only associated with the dropped label t3l2, while x is not orphaned
+-- because it remains associated with t3l1. We will verify this in the
+-- information schema queries outputs below.
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t3 DROP LABEL t3l2;
CREATE TABLE t11 (a int PRIMARY KEY);
CREATE TABLE t12 (b int PRIMARY KEY);
--
2.34.1
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-06-12 01:36 zengman <zengman@halodbtech.com>
parent: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: zengman @ 2026-06-12 01:36 UTC (permalink / raw)
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; Peter Eisentraut <peter@eisentraut.org>
> Thanks for the report and the patch. The fix is on the right track. I
> changed a few things as follows
> a. we usually add operands to the same operator at the end, not at the
> beginning.
> b. create_property_graph.sql, which tests all property graph DDLs, is
> the right place to add these tests. graph_table.sql tests graph query.
>
> I have used an existing property graph in create_property_graph.sql in
> the test. It removes a label from the property graph which has two
> properties associated with it, one that gets orphaned and one that
> doesn't. We can verify that the orphaned property gets dropped from
> the property graph but not the other one by information schema query
> outputs later. So didn't add any separate verification step after the
> DDL.
> Attached patch with those changes.
Hi Ashutosh,
Thank you for your assistance. This looks better. There is a small problem that
https://commitfest.postgresql.org/patch/6848/
It seems that a 'rebase' is needed to conduct the test normally. Could you please make some adjustments again?
--
regards,
Man Zeng
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-06-23 06:39 Peter Eisentraut <peter@eisentraut.org>
parent: zengman <zengman@halodbtech.com>
0 siblings, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2026-06-23 06:39 UTC (permalink / raw)
To: zengman <zengman@halodbtech.com>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
On 12.06.26 03:36, zengman wrote:
>> Thanks for the report and the patch. The fix is on the right track. I
>> changed a few things as follows
>> a. we usually add operands to the same operator at the end, not at the
>> beginning.
>> b. create_property_graph.sql, which tests all property graph DDLs, is
>> the right place to add these tests. graph_table.sql tests graph query.
>>
>> I have used an existing property graph in create_property_graph.sql in
>> the test. It removes a label from the property graph which has two
>> properties associated with it, one that gets orphaned and one that
>> doesn't. We can verify that the orphaned property gets dropped from
>> the property graph but not the other one by information schema query
>> outputs later. So didn't add any separate verification step after the
>> DDL.
>
>> Attached patch with those changes.
>
> Hi Ashutosh,
>
> Thank you for your assistance. This looks better. There is a small problem that
>
> https://commitfest.postgresql.org/patch/6848/
>
> It seems that a 'rebase' is needed to conduct the test normally. Could you please make some adjustments again?
The most recently posted patch does not apply, and if I apply it
manually, the code change does not appear to cause any changes in the
test output. Seemingly, this depends on that some other patches that
are not shown here? Please clarify.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-06-24 06:32 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
parent: Peter Eisentraut <peter@eisentraut.org>
0 siblings, 1 reply; 10+ messages in thread
From: Ashutosh Bapat @ 2026-06-24 06:32 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: zengman <zengman@halodbtech.com>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
On Tue, Jun 23, 2026 at 12:09 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 12.06.26 03:36, zengman wrote:
> >> Thanks for the report and the patch. The fix is on the right track. I
> >> changed a few things as follows
> >> a. we usually add operands to the same operator at the end, not at the
> >> beginning.
> >> b. create_property_graph.sql, which tests all property graph DDLs, is
> >> the right place to add these tests. graph_table.sql tests graph query.
> >>
> >> I have used an existing property graph in create_property_graph.sql in
> >> the test. It removes a label from the property graph which has two
> >> properties associated with it, one that gets orphaned and one that
> >> doesn't. We can verify that the orphaned property gets dropped from
> >> the property graph but not the other one by information schema query
> >> outputs later. So didn't add any separate verification step after the
> >> DDL.
> >
> >> Attached patch with those changes.
> >
> > Hi Ashutosh,
> >
> > Thank you for your assistance. This looks better. There is a small problem that
> >
> > https://commitfest.postgresql.org/patch/6848/
> >
> > It seems that a 'rebase' is needed to conduct the test normally. Could you please make some adjustments again?
>
> The most recently posted patch does not apply, and if I apply it
> manually, the code change does not appear to cause any changes in the
> test output. Seemingly, this depends on that some other patches that
> are not shown here? Please clarify.
>
It does conflict with a fix in my branch for issues in [1]. Since
Zengman is the original author of the patch, I was expecting him to
take the minimal reproduction suggested in my patch and provide an
updated patch. But it looks like he didn't get time to do that. I
suggest that we tackle the patches in [1] first and then tackle this
issue.
[1] https://www.postgresql.org/message-id/CAHg+QDeP=mTHTV48R23zKMy1SBmCKZ_L7-z5zKnYyw+K0x-gCg@mail.gmail...
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-06-29 02:53 zengman <zengman@halodbtech.com>
parent: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: zengman @ 2026-06-29 02:53 UTC (permalink / raw)
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; Peter Eisentraut <peter@eisentraut.org>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
> It does conflict with a fix in my branch for issues in [1]. Since
> Zengman is the original author of the patch, I was expecting him to
> take the minimal reproduction suggested in my patch and provide an
> updated patch. But it looks like he didn't get time to do that. I
> suggest that we tackle the patches in [1] first and then tackle this
> issue.
Hi,
Apologies for the delay -- I've been caught up with some other commitments. Thanks, Ashutosh, for the review and the improved tests.
The fix doesn't depend on the other patches; it applies cleanly on current master. Rebased patch attached, incorporating the review comments.
--
Regards,
Man Zeng
Attachments:
0002-Properties-orphaned-by-dropping-a-label.patch (application/octet-stream, 15.9K) download
From 7475f020a6a37cc6fb10d0dd1bdc1b534cbf5f0d Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Wed, 10 Jun 2026 22:08:49 +0530
Subject: [PATCH] Properties orphaned by dropping a label
AlterPropGraph() cleans up properties that are orphaned by dropping an element
or properties associated with an element. But it doesn't clean up properties
that are orphaned by dropping a label. Fix this missing case.
Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Author: zengman <zengman@halodbtech.com>.
Reported by: zengman <zengman@halodbtech.com>
---
src/backend/commands/propgraphcmds.c | 3 ++-
.../expected/create_property_graph.out | 23 +++++++++----------
.../regress/sql/create_property_graph.sql | 5 ++++
3 files changed, 18 insertions(+), 13 deletions(-)
diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c
index cc516e27020..8d8b7318cf5 100644
--- a/src/backend/commands/propgraphcmds.c
+++ b/src/backend/commands/propgraphcmds.c
@@ -1638,7 +1638,8 @@ AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt)
}
/* Remove any orphaned pg_propgraph_property entries */
- if (stmt->drop_properties || stmt->drop_vertex_tables || stmt->drop_edge_tables)
+ if (stmt->drop_properties || stmt->drop_vertex_tables ||
+ stmt->drop_edge_tables || stmt->drop_label)
{
foreach_oid(propoid, get_graph_property_ids(pgrelid))
{
diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out
index 2f06c7ce5a8..9a6018d717a 100644
--- a/src/test/regress/expected/create_property_graph.out
+++ b/src/test/regress/expected/create_property_graph.out
@@ -82,6 +82,11 @@ CREATE PROPERTY GRAPH g4
);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS kk);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (k);
+-- Dropping a label should drop only orphaned properties. zz is orphaned because
+-- it is only associated with the dropped label t3l2, while x is not orphaned
+-- because it remains associated with t3l1. We will verify this in the
+-- information schema queries outputs below.
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t3 DROP LABEL t3l2;
CREATE TABLE t11 (a int PRIMARY KEY);
CREATE TABLE t12 (b int PRIMARY KEY);
CREATE TABLE t13 (
@@ -439,7 +444,6 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | g4 | t1 | t1
regression | create_property_graph_tests | g4 | t2 | t2
regression | create_property_graph_tests | g4 | t3 | t3l1
- regression | create_property_graph_tests | g4 | t3 | t3l2
regression | create_property_graph_tests | g5 | t11 | t11
regression | create_property_graph_tests | g5 | t12 | t12
regression | create_property_graph_tests | g5 | t13 | t13
@@ -451,7 +455,7 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | gt | e | e
regression | create_property_graph_tests | gt | v1 | v1
regression | create_property_graph_tests | gt | v2 | v2
-(26 rows)
+(25 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
@@ -485,7 +489,6 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g
regression | create_property_graph_tests | g4 | t2 | kk | (k * 2)
regression | create_property_graph_tests | g4 | t3 | x | x
regression | create_property_graph_tests | g4 | t3 | yy | y
- regression | create_property_graph_tests | g4 | t3 | zz | z
regression | create_property_graph_tests | g5 | t11 | a | a
regression | create_property_graph_tests | g5 | t12 | b | b
regression | create_property_graph_tests | g5 | t13 | c | c
@@ -510,7 +513,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
-(54 rows)
+(53 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
@@ -550,8 +553,6 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam
regression | create_property_graph_tests | g4 | t2 | kk
regression | create_property_graph_tests | g4 | t3l1 | x
regression | create_property_graph_tests | g4 | t3l1 | yy
- regression | create_property_graph_tests | g4 | t3l2 | x
- regression | create_property_graph_tests | g4 | t3l2 | zz
regression | create_property_graph_tests | g5 | t11 | a
regression | create_property_graph_tests | g5 | t12 | b
regression | create_property_graph_tests | g5 | t13 | c
@@ -576,7 +577,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
-(61 rows)
+(59 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
@@ -595,7 +596,6 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | g4 | t1
regression | create_property_graph_tests | g4 | t2
regression | create_property_graph_tests | g4 | t3l1
- regression | create_property_graph_tests | g4 | t3l2
regression | create_property_graph_tests | g5 | t11
regression | create_property_graph_tests | g5 | t12
regression | create_property_graph_tests | g5 | t13
@@ -607,7 +607,7 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | gt | e
regression | create_property_graph_tests | gt | v1
regression | create_property_graph_tests | gt | v2
-(26 rows)
+(25 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
@@ -633,7 +633,6 @@ SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_
regression | create_property_graph_tests | g4 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t
regression | create_property_graph_tests | g4 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
regression | create_property_graph_tests | g4 | yy | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | yy
- regression | create_property_graph_tests | g4 | zz | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | zz
regression | create_property_graph_tests | g5 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
regression | create_property_graph_tests | g5 | b | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | b
regression | create_property_graph_tests | g5 | c | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | c
@@ -651,7 +650,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
-(39 rows)
+(38 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
@@ -801,7 +800,7 @@ CREATE PROPERTY GRAPH create_property_graph_tests.g4
VERTEX TABLES (
t1 KEY (a) NO PROPERTIES,
t2 KEY (i) PROPERTIES ((i + j) AS i_j, (k * 2) AS kk),
- t3 KEY (x) LABEL t3l1 PROPERTIES (x, y AS yy) LABEL t3l2 PROPERTIES (x, z AS zz)
+ t3 KEY (x) LABEL t3l1 PROPERTIES (x, y AS yy)
)
EDGE TABLES (
e1 KEY (a, i) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (i) REFERENCES t2 (i) PROPERTIES (a, i, t),
diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql
index 85088ae632c..c1ceaba7384 100644
--- a/src/test/regress/sql/create_property_graph.sql
+++ b/src/test/regress/sql/create_property_graph.sql
@@ -75,6 +75,11 @@ CREATE PROPERTY GRAPH g4
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS kk);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (k);
+-- Dropping a label should drop only orphaned properties. zz is orphaned because
+-- it is only associated with the dropped label t3l2, while x is not orphaned
+-- because it remains associated with t3l1. We will verify this in the
+-- information schema queries outputs below.
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t3 DROP LABEL t3l2;
CREATE TABLE t11 (a int PRIMARY KEY);
CREATE TABLE t12 (b int PRIMARY KEY);
--
2.45.2
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-07-01 05:20 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
parent: zengman <zengman@halodbtech.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ashutosh Bapat @ 2026-07-01 05:20 UTC (permalink / raw)
To: zengman <zengman@halodbtech.com>; +Cc: Peter Eisentraut <peter@eisentraut.org>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
On Mon, Jun 29, 2026 at 8:23 AM zengman <zengman@halodbtech.com> wrote:
>
> > It does conflict with a fix in my branch for issues in [1]. Since
> > Zengman is the original author of the patch, I was expecting him to
> > take the minimal reproduction suggested in my patch and provide an
> > updated patch. But it looks like he didn't get time to do that. I
> > suggest that we tackle the patches in [1] first and then tackle this
> > issue.
>
> Hi,
>
> Apologies for the delay -- I've been caught up with some other commitments. Thanks, Ashutosh, for the review and the improved tests.
> The fix doesn't depend on the other patches; it applies cleanly on current master. Rebased patch attached, incorporating the review comments.
Thanks.
The word properties in the commit message means different things in
different contexts leading to a possible confusion. Commit message
below is clearer, I think
AlterPropGraph() cleans up pg_propgraph_property entries that are
orphaned by dropping an element or by dropping properties associated
with an element. But it doesn't clean up pg_propgraph_property entries
that are orphaned by dropping labels associated with an element. Fix this
missing case.
Also the comment in the test may read better if rewritten like below
-- Dropping a label should drop only orphaned properties. Dropping label t3l1
-- should also drop zz because it is only associated with label t3l2. But x is
-- not dropped, even if it is associated with t3l2, because it remains
-- associated with t3l1. zz will not appear in the information schema queries
-- outputs below, but x will.
I did not change the comment to mention pg_propgraph_properties since
the comment is clear even without mentioning it.
What do you think?
I have verified that the patch you have attached applies cleanly on
master. But it may not apply cleanly in case another change to
create_property_graph.sql goes in first. We will provide rebased patch
if that happens.
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-07-02 03:40 zengman <zengman@halodbtech.com>
parent: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: zengman @ 2026-07-02 03:40 UTC (permalink / raw)
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; +Cc: Peter Eisentraut <peter@eisentraut.org>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
> The word properties in the commit message means different things in
> different contexts leading to a possible confusion. Commit message
> below is clearer, I think
>
> AlterPropGraph() cleans up pg_propgraph_property entries that are
> orphaned by dropping an element or by dropping properties associated
> with an element. But it doesn't clean up pg_propgraph_property entries
> that are orphaned by dropping labels associated with an element. Fix this
> missing case.
>
> Also the comment in the test may read better if rewritten like below
>
> -- Dropping a label should drop only orphaned properties. Dropping label t3l1
> -- should also drop zz because it is only associated with label t3l2. But x is
> -- not dropped, even if it is associated with t3l2, because it remains
> -- associated with t3l1. zz will not appear in the information schema queries
> -- outputs below, but x will.
>
> I did not change the comment to mention pg_propgraph_properties since
> the comment is clear even without mentioning it.
>
> What do you think?
>
> I have verified that the patch you have attached applies cleanly on
> master. But it may not apply cleanly in case another change to
> create_property_graph.sql goes in first. We will provide rebased patch
> if that happens.
Hi Ashutosh,
Thanks for the review. I've updated the commit message per your suggestion ― your version is clearer, so I've adopted it as-is.
Regarding the test comment: I kept the original wording. The current comment already describes the case accurately (zz is orphaned because it's only associated with the dropped
label t3l2, while x is not orphaned because it remains associated with t3l1), so I left it unchanged.
No worries ― if a rebase is needed later, I'll take care of it.
Attached is the updated patch, rebased on the current master.
--
Regards,
Man Zeng
Attachments:
0003-Properties-orphaned-by-dropping-a-label.patch (application/octet-stream, 15.9K) download
From 1b2ac961c4c48841a6115a2aeaac27180623ea0f Mon Sep 17 00:00:00 2001
From: Man Zeng <zengman@halodbtech.com>
Date: Thu, 2 Jul 2026 11:16:40 +0800
Subject: [PATCH] Properties orphaned by dropping a label
AlterPropGraph() cleans up pg_propgraph_property entries that are
orphaned by dropping an element or by dropping properties associated
with an element. But it doesn't clean up pg_propgraph_property entries
that are orphaned by dropping labels associated with an element. Fix this
missing case.
Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Author: zengman <zengman@halodbtech.com>
Reported-by: zengman <zengman@halodbtech.com>
---
src/backend/commands/propgraphcmds.c | 3 ++-
.../expected/create_property_graph.out | 23 +++++++++----------
.../regress/sql/create_property_graph.sql | 5 ++++
3 files changed, 18 insertions(+), 13 deletions(-)
diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c
index cc516e27020..8d8b7318cf5 100644
--- a/src/backend/commands/propgraphcmds.c
+++ b/src/backend/commands/propgraphcmds.c
@@ -1638,7 +1638,8 @@ AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt)
}
/* Remove any orphaned pg_propgraph_property entries */
- if (stmt->drop_properties || stmt->drop_vertex_tables || stmt->drop_edge_tables)
+ if (stmt->drop_properties || stmt->drop_vertex_tables ||
+ stmt->drop_edge_tables || stmt->drop_label)
{
foreach_oid(propoid, get_graph_property_ids(pgrelid))
{
diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out
index 2f06c7ce5a8..9a6018d717a 100644
--- a/src/test/regress/expected/create_property_graph.out
+++ b/src/test/regress/expected/create_property_graph.out
@@ -82,6 +82,11 @@ CREATE PROPERTY GRAPH g4
);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS kk);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (k);
+-- Dropping a label should drop only orphaned properties. zz is orphaned because
+-- it is only associated with the dropped label t3l2, while x is not orphaned
+-- because it remains associated with t3l1. We will verify this in the
+-- information schema queries outputs below.
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t3 DROP LABEL t3l2;
CREATE TABLE t11 (a int PRIMARY KEY);
CREATE TABLE t12 (b int PRIMARY KEY);
CREATE TABLE t13 (
@@ -439,7 +444,6 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | g4 | t1 | t1
regression | create_property_graph_tests | g4 | t2 | t2
regression | create_property_graph_tests | g4 | t3 | t3l1
- regression | create_property_graph_tests | g4 | t3 | t3l2
regression | create_property_graph_tests | g5 | t11 | t11
regression | create_property_graph_tests | g5 | t12 | t12
regression | create_property_graph_tests | g5 | t13 | t13
@@ -451,7 +455,7 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | gt | e | e
regression | create_property_graph_tests | gt | v1 | v1
regression | create_property_graph_tests | gt | v2 | v2
-(26 rows)
+(25 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
@@ -485,7 +489,6 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g
regression | create_property_graph_tests | g4 | t2 | kk | (k * 2)
regression | create_property_graph_tests | g4 | t3 | x | x
regression | create_property_graph_tests | g4 | t3 | yy | y
- regression | create_property_graph_tests | g4 | t3 | zz | z
regression | create_property_graph_tests | g5 | t11 | a | a
regression | create_property_graph_tests | g5 | t12 | b | b
regression | create_property_graph_tests | g5 | t13 | c | c
@@ -510,7 +513,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
-(54 rows)
+(53 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
@@ -550,8 +553,6 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam
regression | create_property_graph_tests | g4 | t2 | kk
regression | create_property_graph_tests | g4 | t3l1 | x
regression | create_property_graph_tests | g4 | t3l1 | yy
- regression | create_property_graph_tests | g4 | t3l2 | x
- regression | create_property_graph_tests | g4 | t3l2 | zz
regression | create_property_graph_tests | g5 | t11 | a
regression | create_property_graph_tests | g5 | t12 | b
regression | create_property_graph_tests | g5 | t13 | c
@@ -576,7 +577,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
-(61 rows)
+(59 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
@@ -595,7 +596,6 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | g4 | t1
regression | create_property_graph_tests | g4 | t2
regression | create_property_graph_tests | g4 | t3l1
- regression | create_property_graph_tests | g4 | t3l2
regression | create_property_graph_tests | g5 | t11
regression | create_property_graph_tests | g5 | t12
regression | create_property_graph_tests | g5 | t13
@@ -607,7 +607,7 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | gt | e
regression | create_property_graph_tests | gt | v1
regression | create_property_graph_tests | gt | v2
-(26 rows)
+(25 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
@@ -633,7 +633,6 @@ SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_
regression | create_property_graph_tests | g4 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t
regression | create_property_graph_tests | g4 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
regression | create_property_graph_tests | g4 | yy | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | yy
- regression | create_property_graph_tests | g4 | zz | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | zz
regression | create_property_graph_tests | g5 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
regression | create_property_graph_tests | g5 | b | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | b
regression | create_property_graph_tests | g5 | c | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | c
@@ -651,7 +650,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
-(39 rows)
+(38 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
@@ -801,7 +800,7 @@ CREATE PROPERTY GRAPH create_property_graph_tests.g4
VERTEX TABLES (
t1 KEY (a) NO PROPERTIES,
t2 KEY (i) PROPERTIES ((i + j) AS i_j, (k * 2) AS kk),
- t3 KEY (x) LABEL t3l1 PROPERTIES (x, y AS yy) LABEL t3l2 PROPERTIES (x, z AS zz)
+ t3 KEY (x) LABEL t3l1 PROPERTIES (x, y AS yy)
)
EDGE TABLES (
e1 KEY (a, i) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (i) REFERENCES t2 (i) PROPERTIES (a, i, t),
diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql
index 85088ae632c..c1ceaba7384 100644
--- a/src/test/regress/sql/create_property_graph.sql
+++ b/src/test/regress/sql/create_property_graph.sql
@@ -75,6 +75,11 @@ CREATE PROPERTY GRAPH g4
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS kk);
ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (k);
+-- Dropping a label should drop only orphaned properties. zz is orphaned because
+-- it is only associated with the dropped label t3l2, while x is not orphaned
+-- because it remains associated with t3l1. We will verify this in the
+-- information schema queries outputs below.
+ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t3 DROP LABEL t3l2;
CREATE TABLE t11 (a int PRIMARY KEY);
CREATE TABLE t12 (b int PRIMARY KEY);
--
2.45.2
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-07-03 05:43 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
parent: zengman <zengman@halodbtech.com>
1 sibling, 0 replies; 10+ messages in thread
From: Ashutosh Bapat @ 2026-07-03 05:43 UTC (permalink / raw)
To: zengman <zengman@halodbtech.com>; +Cc: Peter Eisentraut <peter@eisentraut.org>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
On Thu, Jul 2, 2026 at 9:10 AM zengman <zengman@halodbtech.com> wrote:
>
> > The word properties in the commit message means different things in
> > different contexts leading to a possible confusion. Commit message
> > below is clearer, I think
> >
> > AlterPropGraph() cleans up pg_propgraph_property entries that are
> > orphaned by dropping an element or by dropping properties associated
> > with an element. But it doesn't clean up pg_propgraph_property entries
> > that are orphaned by dropping labels associated with an element. Fix this
> > missing case.
> >
> > Also the comment in the test may read better if rewritten like below
> >
> > -- Dropping a label should drop only orphaned properties. Dropping label t3l1
> > -- should also drop zz because it is only associated with label t3l2. But x is
> > -- not dropped, even if it is associated with t3l2, because it remains
> > -- associated with t3l1. zz will not appear in the information schema queries
> > -- outputs below, but x will.
> >
> > I did not change the comment to mention pg_propgraph_properties since
> > the comment is clear even without mentioning it.
> >
> > What do you think?
> >
> > I have verified that the patch you have attached applies cleanly on
> > master. But it may not apply cleanly in case another change to
> > create_property_graph.sql goes in first. We will provide rebased patch
> > if that happens.
>
> Hi Ashutosh,
>
> Thanks for the review. I've updated the commit message per your suggestion — your version is clearer, so I've adopted it as-is.
>
Thanks.
> Regarding the test comment: I kept the original wording. The current comment already describes the case accurately (zz is orphaned because it's only associated with the dropped
> label t3l2, while x is not orphaned because it remains associated with t3l1), so I left it unchanged.
>
Let's defer this to committer's judgement.
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: (SQL/PGQ) Clean up orphaned properties when dropping a label
@ 2026-07-05 11:57 Peter Eisentraut <peter@eisentraut.org>
parent: zengman <zengman@halodbtech.com>
1 sibling, 0 replies; 10+ messages in thread
From: Peter Eisentraut @ 2026-07-05 11:57 UTC (permalink / raw)
To: zengman <zengman@halodbtech.com>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
On 02.07.26 05:40, zengman wrote:
>> The word properties in the commit message means different things in
>> different contexts leading to a possible confusion. Commit message
>> below is clearer, I think
>>
>> AlterPropGraph() cleans up pg_propgraph_property entries that are
>> orphaned by dropping an element or by dropping properties associated
>> with an element. But it doesn't clean up pg_propgraph_property entries
>> that are orphaned by dropping labels associated with an element. Fix this
>> missing case.
>>
>> Also the comment in the test may read better if rewritten like below
>>
>> -- Dropping a label should drop only orphaned properties. Dropping label t3l1
>> -- should also drop zz because it is only associated with label t3l2. But x is
>> -- not dropped, even if it is associated with t3l2, because it remains
>> -- associated with t3l1. zz will not appear in the information schema queries
>> -- outputs below, but x will.
>>
>> I did not change the comment to mention pg_propgraph_properties since
>> the comment is clear even without mentioning it.
>>
>> What do you think?
>>
>> I have verified that the patch you have attached applies cleanly on
>> master. But it may not apply cleanly in case another change to
>> create_property_graph.sql goes in first. We will provide rebased patch
>> if that happens.
>
> Hi Ashutosh,
>
> Thanks for the review. I've updated the commit message per your suggestion — your version is clearer, so I've adopted it as-is.
>
> Regarding the test comment: I kept the original wording. The current comment already describes the case accurately (zz is orphaned because it's only associated with the dropped
> label t3l2, while x is not orphaned because it remains associated with t3l1), so I left it unchanged.
>
> No worries — if a rebase is needed later, I'll take care of it.
>
> Attached is the updated patch, rebased on the current master.
Committed, thanks.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-07-05 11:57 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-05 14:29 (SQL/PGQ) Clean up orphaned properties when dropping a label zengman <zengman@halodbtech.com>
2026-06-11 03:55 ` Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
2026-06-12 01:36 ` zengman <zengman@halodbtech.com>
2026-06-23 06:39 ` Peter Eisentraut <peter@eisentraut.org>
2026-06-24 06:32 ` Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
2026-06-29 02:53 ` zengman <zengman@halodbtech.com>
2026-07-01 05:20 ` Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
2026-07-02 03:40 ` zengman <zengman@halodbtech.com>
2026-07-03 05:43 ` Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
2026-07-05 11:57 ` Peter Eisentraut <peter@eisentraut.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox