(SQL/PGQ) Clean up orphaned properties when dropping a label

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

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

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.

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]/messages/by-id/CAHg+QDeP=mTHTV48R23zKMy1SBmCKZ_L7-z5zKnYyw+K0x-gCg@mail.gmail.com. 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]/messages/by-id/CAHg+QDeP=mTHTV48R23zKMy1SBmCKZ_L7-z5zKnYyw+K0x-gCg@mail.gmail.com first and then tackle this
issue.

[1]: /messages/by-id/CAHg+QDeP=mTHTV48R23zKMy1SBmCKZ_L7-z5zKnYyw+K0x-gCg@mail.gmail.com

--
Best Wishes,
Ashutosh Bapat

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

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

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

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

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.