Re: (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