Hi Andres, Thanks for all the reports. I am going through them one by one. I will post patches as I create them. Also responding to your questions that I can respond to right now.
On Sun, Aug 23, 2026 at 1:03 AM Andres Freund <[email protected]> wrote: > > - Why do property graphs have pg_attribute entries? > > As far as I can tell, the system attributes don't make any sense for a > property graph as system attributes aren't ever referenced? Other objects > for which system attributes, like composite types, don't have pg_attribute > rows for system attributes? > > And, IIUC, there aren't any other kind of attributes for property graph > relations? > Those attributes are not needed and I think we can avoid creating them safely. If we do that in PG 19, it may need a catversion bump - since a property graph created before the change will have those attributes and that after the change won't. The change itself won't remove the attributes. AFAIU, even if we don't fix this in PG 19 to avoid catversion bump those attributes are useless and harmless. We will fix the code in PG 20 for sure. Peter, what do you think? > > - Pretty sure pg_dump's dependency handling for property graphs is > insufficient? > > I see there's code to handle dependencies via pg_propgraph_element, but > there's also dependencies like pg_propgraph_property.pgtypid? I don't > immediately see such a dependency would be visible to pg_dump, as > getDependencies() only additionally queries dependencies via > pg_propgraph_element > > There probably are unhandled dependencies other than > pg_propgraph_property.pgtypid. > A property graph has three types of components: elements, labels and properties. Query on pg_propgraph_element transfers the dependency of elements to the property graph. Query on pg_propgraph_property transfers the dependency of properties to the property graph. Labels are dependent on the property graph and its elements. They are not dependent on any object outside the property graph. We don't need a separate query for pg_propgraph_labels. Same is the case with pg_propgraph_element_label objects, which depend upon pg_propgraph_label and pg_propgraph_element, nothing outside the property graph. pg_propgraph_label_properties depends upon the expressions (types, functions, columns etc.) which are outside a property graph. So, we need a query to transfer dependencies from pg_propgraph_label_properties to property graph. Ddependecies of pg_propgraph_label_property also cover the dependencies of pg_propgraph_property. But it's better to process each catalog that can have dependencies outside the property graph. With that, we have covered all the dependency transfers for property graphs in getDependencies(). I have added one test each for dependency transfer from pg_propgraph_property and pg_propgraph_label_properties to property graph. On a related note, we may avoid any dependency transfers if we handle property graph components as dumpable objects and dump them separately using ALTER PROPERTY GRAPH commands. But that will slow down the dump and restore process. > > - The prior UNION arms in getDependencies() prevent dependencies on itself - > but I don't think the pg_propgraph_element query does? > > Compare with e.g. the amproc case which has > "AND NOT (refclassid = 'pg_opfamily'::regclass AND amprocfamily = > refobjid)\n"); > Fixed. Also eliminated the dependency of edges on vertexes. > > - Why are property graphs dumped as part of dumpTableSchema()? > > I think it's already pretty weird that views are created as part of > dumpTableSchema(), but they at least share some infrastructure with > tables. I don't see any reason for propgraphs to not have been redirected in > dumpTable(), just like it's done for dumpSequence()? > Need to look into this one. Will do that soon. > > > - More curiosity: Why do property graphs have pg_class entries at all? As far > as I can tell it doesn't use anything from it? > Andrew has answered it already. There's one more. SQL/PGQ standard section 11.19, syntax rule 4, note 157 mentions that property graphs share the same namespace as the tables. Adding them to pg_class makes it easy. > > - Harmless, but it's a bit odd for the propgraph portion of getDependencies() > to filter deptype = 'p' away, given how long that has not existed. > > Perhaps getDependencies() code should just have a comment about why the > queries include 'p', despite that being an unknown kind of dependency these > days. > I think it's just to be consistent with the other queries. A comment on getDependencies() would be good. > - External cascades leave orphan graph metadata > > ALTER PROPERTY GRAPH explicitly removes unused labels/properties, but > generic > dependency deletion bypasses that cleanup: > > CREATE TABLE v (id int PRIMARY KEY); > CREATE PROPERTY GRAPH g > VERTEX TABLES (v LABEL l PROPERTIES (id AS p)); > DROP TABLE v CASCADE; > > g retains global label l and integer property p. Adding a new text property > named p then incorrectly > reports a type mismatch. > > > SELECT count(*) AS elements > FROM pg_propgraph_element > WHERE pgepgid = 'g'::regclass; > > SELECT count(*) AS labels > FROM pg_propgraph_label > WHERE pglpgid = 'g'::regclass; > > SELECT count(*) AS properties > FROM pg_propgraph_property > WHERE pgppgid = 'g'::regclass; > > CREATE TABLE v2 (id text PRIMARY KEY); > > ALTER PROPERTY GRAPH g > ADD VERTEX TABLES ( > v2 LABEL l PROPERTIES (id AS p) > ); > > ERROR: 42601: property "p" data type mismatch: integer vs. text > DETAIL: In a property graph, a property of the same name has to have the > same data type in each label. > Thanks for reporting it. I did not find any existing code which deals with delete-when-reference-drops-to-zero behaviour. Ideally, we should invent a new kind of dependency that will delete the dependent objects when the number of references to the object drops to zero. But it's possibly too late for that kind of change for PG 19 and the semantics of such a dependency need to be carefully thought through to be applicable beyond property graphs. We can rework this in a future release and introduce such a dependency cleanup mechanism. For now, I am adding a special handling in performDeletion() and performMultipleDeletions() to collect and delete all the orphaned property graph objects. To make it easy to convert it entirely driven by dependency mechanism later, the code in the patch makes use of pg_depend to find the orphaned property graph objects instead of using the property graph catalog as much as possible. To avoid code duplication, AlterPropGraph() also uses the same routines to delete orphaned property graph objects. Current AlterPropGraph() cleans up all the orphaned property graph objects once per command but it examines every object irrespective of whether its parent object was deleted or not. With this change we examine only objects downstream to the objects that are being deleted but it means we might be doing it multiple times for the same object. Given that ALTER PROPERTY GRAPH can drop only one parent object at a time, the probability of that leading to multiple deletions cascading to a single orphaned object is low. So, I think it's a net win. This fix is in 0002 patch which is WIP. I will be working more on it tomorrow. Early comments are welcome. I will go through rest of the issues and update the patchset in this thread. -- Best Wishes, Ashutosh Bapat
From 4dd9fb201ead3325f085481e9adca9f2fd8acec3 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat <[email protected]> Date: Thu, 27 Aug 2026 21:07:12 +0530 Subject: [PATCH v20260827 2/2] WIP: DROP CASCADE and orphaned property graph labels and properties When dropping objects from a property graph through ALTER PROPERTY GRAPH, it explicitly drops the orphaned labels and properties. But when a DROP CASCADEs to property graph objects, it may leave orphaned labels and properties in the database. This leads to errors when a label with the same name as the orphaned label or a property with the same name as the orphaned property are created with characteristics different from the orphaned one. A pg_propgraph_property entry needs to be removed when the last referencing pg_propgraph_label_property entry is deleted. Similarly a pg_propgraph_label entry needs to be removed when the last referencing pg_propgraph_element_label entry is deleted. Right now there is no way to express a dependency which depends upon the reference count in pg_depend. Ideally we should add invent a new dependency for this and let the dependency tracking infrastructure drop the orphaned properties and labels. But doing so nearer to the release is not possible and the mechanism needs to be well thought through. Instead we modify performDeletion() and performMultipleDeletions() to collect the pg_propgraph_property and pg_propgraph_label entries from the property graph objects being dropped. We use this collection to find out the possibly orphaned entries and delete them. While doing so we take the lock on their parent property graphs so as to avoid inconsistencies being caused by a concurrent ALTER PROPERTY GRAPH. In order to avoid code duplication both AlterPropGraph() and perform*Deletion() code use the same function to remove orphaned objects. Before this change, AlterPropGraph() collected and removed all orphaned entries only ones at the end of the command. Now it would do this for every deletion. Given that ALTER PROPERTY GRAPH can drop only one parent object at a time, the probability of that leading to multiple deletions cascading to a single orphaned object is low. So there's net performance gain with the new approach. Reported-by: Andres Freund <[email protected]> Author: Ashutosh Bapat <[email protected]> Reviewed-by: TBD Discussion: https://postgr.es/m/dqa5mstx5mna3i7s23pdwl4m6bek7gqsgfccef44wjpswizufi@3aa6vzri3cat Backpatch-through: 19 --- src/backend/catalog/dependency.c | 98 ++++++++++ src/backend/commands/propgraphcmds.c | 177 ++++++++++-------- src/include/commands/propgraphcmds.h | 3 + .../expected/alter-propgraph-drop-cascade.out | 51 +++++ src/test/isolation/isolation_schedule | 1 + .../specs/alter-propgraph-drop-cascade.spec | 47 +++++ .../expected/create_property_graph.out | 9 + .../regress/sql/create_property_graph.sql | 6 + 8 files changed, 312 insertions(+), 80 deletions(-) create mode 100644 src/test/isolation/expected/alter-propgraph-drop-cascade.out create mode 100644 src/test/isolation/specs/alter-propgraph-drop-cascade.spec diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index 55ee42f8dfc..3f23765ef30 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -76,6 +76,7 @@ #include "commands/event_trigger.h" #include "commands/extension.h" #include "commands/policy.h" +#include "commands/propgraphcmds.h" #include "commands/publicationcmds.h" #include "commands/seclabel.h" #include "commands/sequence.h" @@ -163,6 +164,10 @@ static void reportDependentObjects(const ObjectAddresses *targetObjects, static void deleteOneObject(const ObjectAddress *object, Relation *depRel, int32 flags); static void doDeletion(const ObjectAddress *object, int flags); +static void collectPropGraphCleanupCandidates(const ObjectAddresses *targetObjects, + Relation depRel, + List **labeloids, + List **propoids); static bool find_expr_references_walker(Node *node, find_expr_references_context *context); static void process_function_rte_ref(RangeTblEntry *rte, AttrNumber attnum, @@ -236,6 +241,77 @@ deleteObjectsInList(ObjectAddresses *targetObjects, Relation *depRel, } } +/* + * Collect labels and properties that might become orphaned when deleting + * property graph component objects. Existing dependency types cannot express + * deleting a referenced object only when its last dependent is removed, so + * these objects require a post-deletion cleanup pass. + */ +static void +collectPropGraphCleanupCandidates(const ObjectAddresses *targetObjects, + Relation depRel, List **labeloids, + List **propoids) +{ + for (int i = 0; i < targetObjects->numrefs; i++) + { + const ObjectAddress *object = &targetObjects->refs[i]; + Oid refobjid = InvalidOid; + Oid refclassid; + List **oids; + ScanKeyData key[2]; + SysScanDesc scan; + HeapTuple tup; + + if (object->classId == PropgraphElementLabelRelationId) + { + refclassid = PropgraphLabelRelationId; + oids = labeloids; + } + else if (object->classId == PropgraphLabelPropertyRelationId) + { + refclassid = PropgraphPropertyRelationId; + oids = propoids; + } + else + continue; + + ScanKeyInit(&key[0], + Anum_pg_depend_classid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(object->classId)); + ScanKeyInit(&key[1], + Anum_pg_depend_objid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(object->objectId)); + + scan = systable_beginscan(depRel, DependDependerIndexId, true, + NULL, 2, key); + while (HeapTupleIsValid(tup = systable_getnext(scan))) + { + Form_pg_depend depform = (Form_pg_depend) GETSTRUCT(tup); + + if (depform->refclassid == refclassid && + depform->deptype == DEPENDENCY_AUTO) + { + if (OidIsValid(refobjid)) + elog(ERROR, "multiple references found for property graph component %u", + object->objectId); + + refobjid = depform->refobjid; + } + } + systable_endscan(scan); + + /* + * Each pg_propgraph_label_property entry should have one and only one + * pg_propgraph_property entry. Similarly for + * pg_propgraph_element_label entry and pg_propgraph_label entry. + */ + Assert(OidIsValid(refobjid)); + *oids = list_append_unique_oid(*oids, refobjid); + } +} + /* * performDeletion: attempt to drop the specified object. If CASCADE * behavior is specified, also drop any dependent objects (recursively). @@ -281,6 +357,8 @@ performDeletion(const ObjectAddress *object, { Relation depRel; ObjectAddresses *targetObjects; + List *labeloids = NIL; + List *propoids = NIL; /* * We save some cycles by opening pg_depend just once and passing the @@ -315,6 +393,8 @@ performDeletion(const ObjectAddress *object, behavior, flags, object); + collectPropGraphCleanupCandidates(targetObjects, depRel, + &labeloids, &propoids); /* do the deed */ deleteObjectsInList(targetObjects, &depRel, flags); @@ -323,6 +403,13 @@ performDeletion(const ObjectAddress *object, free_object_addresses(targetObjects); table_close(depRel, RowExclusiveLock); + + RemoveOrphanedPropGraphObjects(labeloids, propoids, behavior, + flags & (PERFORM_DELETION_INTERNAL | + PERFORM_DELETION_QUIETLY | + PERFORM_DELETION_SKIP_EXTENSIONS)); + list_free(labeloids); + list_free(propoids); } /* @@ -340,6 +427,8 @@ performMultipleDeletions(const ObjectAddresses *objects, { Relation depRel; ObjectAddresses *targetObjects; + List *labeloids = NIL; + List *propoids = NIL; int i; /* No work if no objects... */ @@ -391,6 +480,8 @@ performMultipleDeletions(const ObjectAddresses *objects, behavior, flags, (objects->numrefs == 1 ? objects->refs : NULL)); + collectPropGraphCleanupCandidates(targetObjects, depRel, + &labeloids, &propoids); /* do the deed */ deleteObjectsInList(targetObjects, &depRel, flags); @@ -399,6 +490,13 @@ performMultipleDeletions(const ObjectAddresses *objects, free_object_addresses(targetObjects); table_close(depRel, RowExclusiveLock); + + RemoveOrphanedPropGraphObjects(labeloids, propoids, behavior, + flags & (PERFORM_DELETION_INTERNAL | + PERFORM_DELETION_QUIETLY | + PERFORM_DELETION_SKIP_EXTENSIONS)); + list_free(labeloids); + list_free(propoids); } /* diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c index 2bdce331c49..9c22c539163 100644 --- a/src/backend/commands/propgraphcmds.c +++ b/src/backend/commands/propgraphcmds.c @@ -38,6 +38,7 @@ #include "parser/parse_oper.h" #include "parser/parse_relation.h" #include "parser/parse_target.h" +#include "storage/lmgr.h" #include "utils/acl.h" #include "utils/array.h" #include "utils/builtins.h" @@ -96,7 +97,6 @@ static Oid get_element_relid(Oid peid); static List *get_graph_label_ids(Oid graphid); static List *get_label_element_label_ids(Oid labelid); static List *get_element_label_property_names(Oid ellabeloid); -static List *get_graph_property_ids(Oid graphid); /* @@ -318,6 +318,102 @@ CreatePropGraph(ParseState *pstate, const CreatePropGraphStmt *stmt) return pgaddress; } +/* + * Remove labels and properties left unused after deleting property graph + * component objects. + */ +void +RemoveOrphanedPropGraphObjects(const List *labeloids, const List *propoids, + DropBehavior behavior, int flags) +{ + List *graphoids = NIL; + + foreach_oid(labeloid, labeloids) + { + HeapTuple tuple; + + tuple = SearchSysCache1(PROPGRAPHLABELOID, + ObjectIdGetDatum(labeloid)); + if (HeapTupleIsValid(tuple)) + { + Form_pg_propgraph_label label = (Form_pg_propgraph_label) GETSTRUCT(tuple); + + graphoids = list_append_unique_oid(graphoids, label->pglpgid); + ReleaseSysCache(tuple); + } + } + + foreach_oid(propoid, propoids) + { + HeapTuple tuple; + + tuple = SearchSysCache1(PROPGRAPHPROPOID, + ObjectIdGetDatum(propoid)); + if (HeapTupleIsValid(tuple)) + { + Form_pg_propgraph_property property = (Form_pg_propgraph_property) GETSTRUCT(tuple); + + graphoids = list_append_unique_oid(graphoids, property->pgppgid); + ReleaseSysCache(tuple); + } + } + + list_sort(graphoids, list_oid_cmp); + foreach_oid(graphoid, graphoids) + LockRelationOid(graphoid, ShareRowExclusiveLock); + list_free(graphoids); + + foreach_oid(labeloid, labeloids) + { + List *ellabeloids; + + if (!SearchSysCacheExists1(PROPGRAPHLABELOID, + ObjectIdGetDatum(labeloid))) + continue; + + ellabeloids = get_label_element_label_ids(labeloid); + if (!ellabeloids) + { + ObjectAddress object; + + ObjectAddressSet(object, PropgraphLabelRelationId, labeloid); + performDeletion(&object, behavior, flags); + } + list_free(ellabeloids); + } + + foreach_oid(propoid, propoids) + { + Relation rel; + SysScanDesc scan; + ScanKeyData key[1]; + bool in_use; + + if (!SearchSysCacheExists1(PROPGRAPHPROPOID, + ObjectIdGetDatum(propoid))) + continue; + + rel = table_open(PropgraphLabelPropertyRelationId, RowShareLock); + ScanKeyInit(&key[0], + Anum_pg_propgraph_label_property_plppropid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(propoid)); + /* XXX no suitable index */ + scan = systable_beginscan(rel, InvalidOid, true, NULL, 1, key); + in_use = HeapTupleIsValid(systable_getnext(scan)); + systable_endscan(scan); + table_close(rel, RowShareLock); + + if (!in_use) + { + ObjectAddress object; + + ObjectAddressSet(object, PropgraphPropertyRelationId, propoid); + performDeletion(&object, behavior, flags); + } + } +} + /* * Process the key clause specified for an element. If key_clause is non-NIL, * then it is a list of column names. Otherwise, the primary key of the @@ -1494,21 +1590,6 @@ AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt) performDeletion(&obj, stmt->drop_behavior, 0); } - /* Remove any orphaned pg_propgraph_label entries */ - if (stmt->drop_vertex_tables || stmt->drop_edge_tables) - { - foreach_oid(labeloid, get_graph_label_ids(pgrelid)) - { - if (!get_label_element_label_ids(labeloid)) - { - ObjectAddress obj; - - ObjectAddressSet(obj, PropgraphLabelRelationId, labeloid); - performDeletion(&obj, stmt->drop_behavior, 0); - } - } - } - foreach(lc, stmt->add_labels) { PropGraphLabelAndProperties *lp = lfirst_node(PropGraphLabelAndProperties, lc); @@ -1610,13 +1691,6 @@ AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt) ObjectAddressSet(obj, PropgraphElementLabelRelationId, ellabeloid); performDeletion(&obj, stmt->drop_behavior, 0); - - /* Remove any orphaned pg_propgraph_label entries */ - if (!get_label_element_label_ids(labeloid)) - { - ObjectAddressSet(obj, PropgraphLabelRelationId, labeloid); - performDeletion(&obj, stmt->drop_behavior, 0); - } } if (stmt->add_properties) @@ -1714,35 +1788,6 @@ AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt) check_element_label_properties(ellabeloid); } - /* Remove any orphaned pg_propgraph_property entries */ - if (stmt->drop_properties || stmt->drop_vertex_tables || stmt->drop_edge_tables || stmt->drop_label) - { - foreach_oid(propoid, get_graph_property_ids(pgrelid)) - { - Relation rel; - SysScanDesc scan; - ScanKeyData key[1]; - - rel = table_open(PropgraphLabelPropertyRelationId, RowShareLock); - ScanKeyInit(&key[0], - Anum_pg_propgraph_label_property_plppropid, - BTEqualStrategyNumber, F_OIDEQ, - ObjectIdGetDatum(propoid)); - /* XXX no suitable index */ - scan = systable_beginscan(rel, InvalidOid, true, NULL, 1, key); - if (!systable_getnext(scan)) - { - ObjectAddress obj; - - ObjectAddressSet(obj, PropgraphPropertyRelationId, propoid); - performDeletion(&obj, stmt->drop_behavior, 0); - } - - systable_endscan(scan); - table_close(rel, RowShareLock); - } - } - /* * Invalidate relcache entry of the property graph so that the queries in * the cached plans referencing the property graph will be rewritten @@ -1929,31 +1974,3 @@ get_element_label_property_names(Oid ellabeloid) return result; } - -/* - * Get a list of all property OIDs of a graph. - */ -static List * -get_graph_property_ids(Oid graphid) -{ - Relation rel; - SysScanDesc scan; - ScanKeyData key[1]; - HeapTuple tuple; - List *result = NIL; - - rel = table_open(PropgraphPropertyRelationId, AccessShareLock); - ScanKeyInit(&key[0], - Anum_pg_propgraph_property_pgppgid, - BTEqualStrategyNumber, - F_OIDEQ, ObjectIdGetDatum(graphid)); - scan = systable_beginscan(rel, PropgraphPropertyNameIndexId, true, NULL, 1, key); - while (HeapTupleIsValid(tuple = systable_getnext(scan))) - { - result = lappend_oid(result, ((Form_pg_propgraph_property) GETSTRUCT(tuple))->oid); - } - systable_endscan(scan); - table_close(rel, AccessShareLock); - - return result; -} diff --git a/src/include/commands/propgraphcmds.h b/src/include/commands/propgraphcmds.h index 1bf7d9ea217..b45f9cff143 100644 --- a/src/include/commands/propgraphcmds.h +++ b/src/include/commands/propgraphcmds.h @@ -19,5 +19,8 @@ extern ObjectAddress CreatePropGraph(ParseState *pstate, const CreatePropGraphStmt *stmt); extern ObjectAddress AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt); +extern void RemoveOrphanedPropGraphObjects(const List *labeloids, + const List *propoids, + DropBehavior behavior, int flags); #endif /* PROPGRAPHCMDS_H */ diff --git a/src/test/isolation/expected/alter-propgraph-drop-cascade.out b/src/test/isolation/expected/alter-propgraph-drop-cascade.out new file mode 100644 index 00000000000..1dcad71e034 --- /dev/null +++ b/src/test/isolation/expected/alter-propgraph-drop-cascade.out @@ -0,0 +1,51 @@ +Parsed test spec with 2 sessions + +starting permutation: s1_begin s1_add_label s2_drop s1_commit s2_check +step s1_begin: BEGIN; +step s1_add_label: + ALTER PROPERTY GRAPH pgg ALTER VERTEX TABLE pgt_keep + ADD LABEL pgl PROPERTIES (a AS p); + +s2: NOTICE: drop cascades to vertex pgt_drop of property graph pgg +step s2_drop: DROP TABLE pgt_drop CASCADE; <waiting ...> +step s1_commit: COMMIT; +step s2_drop: <... completed> +step s2_check: + SELECT + (SELECT count(*) + FROM information_schema.pg_labels + WHERE property_graph_name = 'pgg' AND label_name = 'pgl') AS labels, + (SELECT count(*) + FROM information_schema.pg_property_data_types + WHERE property_graph_name = 'pgg' AND property_name = 'p') AS properties; + +labels|properties +------+---------- + 1| 1 +(1 row) + + +starting permutation: s1_begin s1_add_label s2_drop s1_rollback s2_check +step s1_begin: BEGIN; +step s1_add_label: + ALTER PROPERTY GRAPH pgg ALTER VERTEX TABLE pgt_keep + ADD LABEL pgl PROPERTIES (a AS p); + +s2: NOTICE: drop cascades to vertex pgt_drop of property graph pgg +step s2_drop: DROP TABLE pgt_drop CASCADE; <waiting ...> +step s1_rollback: ROLLBACK; +step s2_drop: <... completed> +step s2_check: + SELECT + (SELECT count(*) + FROM information_schema.pg_labels + WHERE property_graph_name = 'pgg' AND label_name = 'pgl') AS labels, + (SELECT count(*) + FROM information_schema.pg_property_data_types + WHERE property_graph_name = 'pgg' AND property_name = 'p') AS properties; + +labels|properties +------+---------- + 0| 0 +(1 row) + diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 1fcf4e63238..8769a9d3332 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -130,3 +130,4 @@ test: for-portion-of test: ddl-dependency-locking test: pub-concurrent-drop test: drop-owned-grant +test: alter-propgraph-drop-cascade diff --git a/src/test/isolation/specs/alter-propgraph-drop-cascade.spec b/src/test/isolation/specs/alter-propgraph-drop-cascade.spec new file mode 100644 index 00000000000..d00f1a39320 --- /dev/null +++ b/src/test/isolation/specs/alter-propgraph-drop-cascade.spec @@ -0,0 +1,47 @@ +# Test a cascaded element-table drop concurrent with property graph changes. + +setup +{ + CREATE TABLE pgt_drop (a int PRIMARY KEY); + CREATE TABLE pgt_keep (a int PRIMARY KEY); + CREATE PROPERTY GRAPH pgg + VERTEX TABLES ( + pgt_drop LABEL pgl PROPERTIES (a AS p), + pgt_keep); +} + +teardown +{ + DROP PROPERTY GRAPH pgg; + DROP TABLE IF EXISTS pgt_drop; + DROP TABLE pgt_keep; +} + +session s1 +step s1_begin { BEGIN; } +step s1_add_label +{ + ALTER PROPERTY GRAPH pgg ALTER VERTEX TABLE pgt_keep + ADD LABEL pgl PROPERTIES (a AS p); +} +step s1_commit { COMMIT; } +step s1_rollback { ROLLBACK; } + +session s2 +step s2_drop { DROP TABLE pgt_drop CASCADE; } +step s2_check +{ + SELECT + (SELECT count(*) + FROM information_schema.pg_labels + WHERE property_graph_name = 'pgg' AND label_name = 'pgl') AS labels, + (SELECT count(*) + FROM information_schema.pg_property_data_types + WHERE property_graph_name = 'pgg' AND property_name = 'p') AS properties; +} + +# The committed association keeps the shared label and property. +permutation s1_begin s1_add_label s2_drop s1_commit s2_check + +# With the association rolled back, the drop removes the orphaned metadata. +permutation s1_begin s1_add_label s2_drop s1_rollback s2_check diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out index 45288f99723..04696fefc6e 100644 --- a/src/test/regress/expected/create_property_graph.out +++ b/src/test/regress/expected/create_property_graph.out @@ -118,6 +118,15 @@ ERROR: property graph "g4" element "t2" label "t2" has no property "yy" -- 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; +-- Cascaded drop should clean up the orphaned labels and properties. We will +-- verify this in the information schema queries outputs below. +CREATE TABLE t_tmp (p_tmp int primary key); +ALTER PROPERTY GRAPH g4 ADD VERTEX TABLES (t_tmp); +ALTER PROPERTY GRAPH g3 ADD VERTEX TABLES (t_tmp); +DROP TABLE t_tmp CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to vertex t_tmp of property graph g4 +drop cascades to vertex t_tmp of property graph g3 CREATE TABLE t11 (a int PRIMARY KEY); CREATE TABLE t12 (b int PRIMARY KEY); CREATE TABLE t13 ( diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql index d74af881639..e407648d852 100644 --- a/src/test/regress/sql/create_property_graph.sql +++ b/src/test/regress/sql/create_property_graph.sql @@ -103,6 +103,12 @@ ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (yy -- 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; +-- Cascaded drop should clean up the orphaned labels and properties. We will +-- verify this in the information schema queries outputs below. +CREATE TABLE t_tmp (p_tmp int primary key); +ALTER PROPERTY GRAPH g4 ADD VERTEX TABLES (t_tmp); +ALTER PROPERTY GRAPH g3 ADD VERTEX TABLES (t_tmp); +DROP TABLE t_tmp CASCADE; CREATE TABLE t11 (a int PRIMARY KEY); CREATE TABLE t12 (b int PRIMARY KEY); -- 2.34.1
From 2d8e9aca165edc6fb487043d07cbda9d3c307c99 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat <[email protected]> Date: Wed, 26 Aug 2026 12:36:25 +0530 Subject: [PATCH v20260827 1/2] pg_dump: Transfer dependencies of properties to property graph getDependencies() transferred depencies of pg_propgraph_element entries to the parent property graph but missed doing so for pg_propgraph_property and pg_propgraph_label_property. Fix this omission. One of the symptoms of this omission is seen as a failure to restore a property graph which has a property with a table's row type as its data type. Table's row type depends upon the table which has the same dump priority as the property graph. Without an appropriate dependency transfer, the property graph may get dumped before the table. We may avoid any dependency transfers if we handle all the property graph components as dumpable objects and dump them separately using ALTER PROPERTY GRAPH commands. But that will slow down the dump and restore process. Add a regression test to verify the dependency properties on user data types (in the form of table row types). 002_pg_upgrade, which upgrades a regression database, verifies the resulting dump order. Notes to reviewers: The test modifies an existing property graph by giving two of its labels a property which involves table types. Previously, these labels used default properties, but those are now covered by other tests in this file or in graph_table.sql. So these changes do not affect the test coverage or any tests downstream. Adding a user-defined type widens the user_defined_type_schema column in the output of the information_schema.pg_property_data_types query. This results in a large expected-output diff. It is unavoidable because user-defined types reside in a user-created schema. That schema has a relatively long name in this test. We may eventually want to make such output less sensitive to changes in column width. Reported-by: Andres Freund <[email protected]> Author: Ashutosh Bapat <[email protected]> Reviewed-by: TBD Discussion: https://postgr.es/m/dqa5mstx5mna3i7s23pdwl4m6bek7gqsgfccef44wjpswizufi@3aa6vzri3cat Backpatch-through: 19 --- src/bin/pg_dump/pg_dump.c | 41 +++++- .../expected/create_property_graph.out | 134 ++++++++++-------- .../regress/sql/create_property_graph.sql | 20 ++- 3 files changed, 131 insertions(+), 64 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index db14834e430..8191e3521df 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -20260,15 +20260,50 @@ getDependencies(Archive *fout) "AND NOT (refclassid = 'pg_opfamily'::regclass AND amprocfamily = refobjid)\n"); /* - * Translate dependencies of pg_propgraph_element entries into - * dependencies of their parent pg_class entry. + * Translate dependencies of components of a property graph into + * dependencies of their parent (property graph) pg_class entry. These + * components are stored in pg_propgraph_element, pg_propgraph_label, + * pg_propgraph_property and pg_propgraph_element_label, + * pg_propgraph_label_property. Out of these pg_propgraph_label and + * pg_propgraph_element_label entries do not have dependencies outside the + * property graph, so we don't need to translate those. */ if (fout->remoteVersion >= 190000) + { + /* + * Eliminate dependencies of edges on vertexes since we will dump a + * single CREATE PROPERTY GRAPH statement containing all the elements + * together. + */ appendPQExpBufferStr(query, "UNION ALL\n" "SELECT 'pg_class'::regclass AS classid, pgepgid AS objid, refclassid, refobjid, deptype " "FROM pg_depend d, pg_propgraph_element pge " "WHERE deptype NOT IN ('p', 'e', 'i') AND " - "classid = 'pg_propgraph_element'::regclass AND objid = pge.oid\n"); + "classid = 'pg_propgraph_element'::regclass AND objid = pge.oid " + "AND NOT (refclassid = 'pg_class'::regclass AND pgepgid = refobjid) " + "AND refclassid <> 'pg_propgraph_element'::regclass\n"); + + appendPQExpBufferStr(query, "UNION ALL\n" + "SELECT 'pg_class'::regclass AS classid, pgppgid AS objid, refclassid, refobjid, deptype " + "FROM pg_depend d, pg_propgraph_property pgp " + "WHERE deptype NOT IN ('p', 'e', 'i') AND " + "classid = 'pg_propgraph_property'::regclass AND objid = pgp.oid " + "AND NOT (refclassid = 'pg_class'::regclass AND pgppgid = refobjid)\n"); + + /* + * Eliminate dependencies of pg_propgraph_label_properties on + * pg_propgraph_element_label and pg_propgraph_property since they are + * internal to the property graph which will be dumped as a single + * DDL. + */ + appendPQExpBufferStr(query, "UNION ALL\n" + "SELECT 'pg_class'::regclass AS classid, pgp.pgppgid AS objid, refclassid, refobjid, deptype " + "FROM pg_depend d, pg_propgraph_label_property pglp, pg_propgraph_property pgp " + "WHERE deptype NOT IN ('p', 'e', 'i') AND " + "classid = 'pg_propgraph_label_property'::regclass AND objid = pglp.oid AND pglp.plppropid = pgp.oid " + "AND NOT (refclassid IN ('pg_propgraph_element_label'::regclass, 'pg_propgraph_property'::regclass))\n"); + } + /* Sort the output for efficiency below */ appendPQExpBufferStr(query, "ORDER BY 1,2"); diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out index 5bbd6477ed6..45288f99723 100644 --- a/src/test/regress/expected/create_property_graph.out +++ b/src/test/regress/expected/create_property_graph.out @@ -15,8 +15,21 @@ CREATE TABLE t3 (x int, y text, z text); -- INCLUDE column must not become part of the inferred element key CREATE TABLE e1 (a int, i int, t text, PRIMARY KEY (a, i) INCLUDE (t)); CREATE TABLE e2 (a int, x int, t text); +-- The table types used to test graph properties with user-defined +-- datatypes. Unlike other user defined types these types depend upon tables +-- which have same dump priority as the property graph. 002_pg_upgrade will use +-- this property graph to test dependency ordering in pg_dump. +CREATE TABLE t_udt1 (a int, b int); +CREATE TABLE t_udt2 (a int, b int); CREATE PROPERTY GRAPH g2 - VERTEX TABLES (t1 KEY (a), t2 DEFAULT LABEL, t3 KEY (x) LABEL t3l1 LABEL t3l2) + VERTEX TABLES ( + t1 KEY (a), + t2 DEFAULT LABEL, + t3 KEY (x) + -- property with user-defined type + LABEL t3l1 PROPERTIES ((row(x, 1)::t_udt1) AS p1) + -- property with user-defined type inside expression + LABEL t3l2 PROPERTIES ((row(x, 1)::t_udt2).a AS p2)) EDGE TABLES ( e1 SOURCE KEY (a) REFERENCES t1 (a) @@ -42,7 +55,15 @@ DETAIL: vertex t1 of property graph g2 depends on column a of table t1 edge e1 of property graph g2 depends on column a of table t1 edge e2 of property graph g2 depends on column a of table t1 HINT: Use DROP ... CASCADE to drop the dependent objects too. --- like g2 but assembled with ALTER +DROP TABLE t_udt1; -- fail +ERROR: cannot drop table t_udt1 because other objects depend on it +DETAIL: property p1 of property graph g2 depends on type t_udt1 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP TABLE t_udt2; -- fail +ERROR: cannot drop table t_udt2 because other objects depend on it +DETAIL: property p2 of label t3l2 of vertex t3 of property graph g2 depends on table t_udt2 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- property graph assembled using ALTER CREATE PROPERTY GRAPH g3; ALTER PROPERTY GRAPH g3 ADD VERTEX TABLES (t1 KEY (a), t2 DEFAULT LABEL); ALTER PROPERTY GRAPH g3 @@ -506,9 +527,8 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g regression | create_property_graph_tests | g2 | t2 | i | i regression | create_property_graph_tests | g2 | t2 | j | j regression | create_property_graph_tests | g2 | t2 | k | k - regression | create_property_graph_tests | g2 | t3 | x | x - regression | create_property_graph_tests | g2 | t3 | y | y - regression | create_property_graph_tests | g2 | t3 | z | z + regression | create_property_graph_tests | g2 | t3 | p1 | ROW(x, 1)::t_udt1 + regression | create_property_graph_tests | g2 | t3 | p2 | (ROW(x, 1)::t_udt2).a regression | create_property_graph_tests | g3 | t1 | a | a regression | create_property_graph_tests | g3 | t1 | b | b regression | create_property_graph_tests | g3 | t3 | x | x @@ -552,7 +572,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 -(57 rows) +(56 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 @@ -568,12 +588,8 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam regression | create_property_graph_tests | g2 | t2 | i regression | create_property_graph_tests | g2 | t2 | j regression | create_property_graph_tests | g2 | t2 | k - regression | create_property_graph_tests | g2 | t3l1 | x - regression | create_property_graph_tests | g2 | t3l1 | y - regression | create_property_graph_tests | g2 | t3l1 | z - regression | create_property_graph_tests | g2 | t3l2 | x - regression | create_property_graph_tests | g2 | t3l2 | y - regression | create_property_graph_tests | g2 | t3l2 | z + regression | create_property_graph_tests | g2 | t3l1 | p1 + regression | create_property_graph_tests | g2 | t3l2 | p2 regression | create_property_graph_tests | g3 | t1 | a regression | create_property_graph_tests | g3 | t1 | b regression | create_property_graph_tests | g3 | t3l1 | x @@ -620,7 +636,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 -(63 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 @@ -654,50 +670,50 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n (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 -------------------------+-----------------------------+---------------------+---------------+-------------------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+---------------------------+--------------------------+------------------------+---------------+--------------+------------+---------------------+---------------- - regression | create_property_graph_tests | g2 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a - regression | create_property_graph_tests | g2 | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b - regression | create_property_graph_tests | g2 | i | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i - regression | create_property_graph_tests | g2 | j | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | j - regression | create_property_graph_tests | g2 | k | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | k - regression | create_property_graph_tests | g2 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t - regression | create_property_graph_tests | g2 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x - regression | create_property_graph_tests | g2 | y | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | y - regression | create_property_graph_tests | g2 | z | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | z - regression | create_property_graph_tests | g3 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a - regression | create_property_graph_tests | g3 | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b - regression | create_property_graph_tests | g3 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x - regression | create_property_graph_tests | g3 | y | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | y - regression | create_property_graph_tests | g3 | z | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | z - regression | create_property_graph_tests | g4 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a - regression | create_property_graph_tests | g4 | i | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i - regression | create_property_graph_tests | g4 | i_j | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i_j - regression | create_property_graph_tests | g4 | kk | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | kk - 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 | 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 - regression | create_property_graph_tests | g5 | d | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | d - regression | create_property_graph_tests | g5 | e | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | e - regression | create_property_graph_tests | gc1 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a - regression | create_property_graph_tests | gc1 | b | character varying | | | | | | regression | pg_catalog | C | | | | | | | regression | pg_catalog | varchar | | | | | b - 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 - regression | create_property_graph_tests | gt | k1 | bigint | | | | | | regression | | | | | | | | | regression | pg_catalog | int8 | | | | | k1 - 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 + 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 +------------------------+-----------------------------+---------------------+---------------+-------------------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+---------------------------+-----------------------------+------------------------+---------------+--------------+------------+---------------------+---------------- + regression | create_property_graph_tests | g2 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a + regression | create_property_graph_tests | g2 | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b + regression | create_property_graph_tests | g2 | i | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i + regression | create_property_graph_tests | g2 | j | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | j + regression | create_property_graph_tests | g2 | k | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | k + regression | create_property_graph_tests | g2 | p1 | USER-DEFINED | | | | | | regression | | | | | | | | | regression | create_property_graph_tests | t_udt1 | | | | | p1 + regression | create_property_graph_tests | g2 | p2 | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | p2 + regression | create_property_graph_tests | g2 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t + regression | create_property_graph_tests | g2 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x + regression | create_property_graph_tests | g3 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a + regression | create_property_graph_tests | g3 | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b + regression | create_property_graph_tests | g3 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x + regression | create_property_graph_tests | g3 | y | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | y + regression | create_property_graph_tests | g3 | z | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | z + regression | create_property_graph_tests | g4 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a + regression | create_property_graph_tests | g4 | i | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i + regression | create_property_graph_tests | g4 | i_j | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i_j + regression | create_property_graph_tests | g4 | kk | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | kk + 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 | 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 + regression | create_property_graph_tests | g5 | d | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | d + regression | create_property_graph_tests | g5 | e | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | e + regression | create_property_graph_tests | gc1 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a + regression | create_property_graph_tests | gc1 | b | character varying | | | | | | regression | pg_catalog | C | | | | | | | regression | pg_catalog | varchar | | | | | b + 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 + regression | create_property_graph_tests | gt | k1 | bigint | | | | | | regression | | | | | | | | | regression | pg_catalog | int8 | | | | | k1 + 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 (42 rows) SELECT * FROM information_schema.pg_property_graph_privileges WHERE grantee LIKE 'regress%' ORDER BY property_graph_name, grantor, grantee, privilege_type; @@ -831,7 +847,7 @@ CREATE PROPERTY GRAPH create_property_graph_tests.g2 VERTEX TABLES ( t1 KEY (a) PROPERTIES (a, b), t2 KEY (i) PROPERTIES (i, j, k), - t3 KEY (x) LABEL t3l1 PROPERTIES (x, y, z) LABEL t3l2 PROPERTIES (x, y, z) + t3 KEY (x) LABEL t3l1 PROPERTIES (ROW(x, 1)::t_udt1 AS p1) LABEL t3l2 PROPERTIES ((ROW(x, 1)::t_udt2).a AS p2) ) EDGE TABLES ( e1 KEY (a, i) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (i) REFERENCES t2 (i) PROPERTIES (a, i, t), @@ -909,7 +925,7 @@ CREATE PROPERTY GRAPH create_property_graph_tests.g2 VERTEX TABLES ( t1 KEY (a) PROPERTIES (a, b), t2 KEY (i) PROPERTIES (i, j, k), - t3 KEY (x) LABEL t3l1 PROPERTIES (x, y, z) LABEL t3l2 PROPERTIES (x, y, z) + t3 KEY (x) LABEL t3l1 PROPERTIES (ROW(x, 1)::t_udt1 AS p1) LABEL t3l2 PROPERTIES ((ROW(x, 1)::t_udt2).a AS p2) ) 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 2b8269d66ec..d74af881639 100644 --- a/src/test/regress/sql/create_property_graph.sql +++ b/src/test/regress/sql/create_property_graph.sql @@ -21,8 +21,22 @@ CREATE TABLE t3 (x int, y text, z text); CREATE TABLE e1 (a int, i int, t text, PRIMARY KEY (a, i) INCLUDE (t)); CREATE TABLE e2 (a int, x int, t text); +-- The table types used to test graph properties with user-defined +-- datatypes. Unlike other user defined types these types depend upon tables +-- which have same dump priority as the property graph. 002_pg_upgrade will use +-- this property graph to test dependency ordering in pg_dump. +CREATE TABLE t_udt1 (a int, b int); +CREATE TABLE t_udt2 (a int, b int); + CREATE PROPERTY GRAPH g2 - VERTEX TABLES (t1 KEY (a), t2 DEFAULT LABEL, t3 KEY (x) LABEL t3l1 LABEL t3l2) + VERTEX TABLES ( + t1 KEY (a), + t2 DEFAULT LABEL, + t3 KEY (x) + -- property with user-defined type + LABEL t3l1 PROPERTIES ((row(x, 1)::t_udt1) AS p1) + -- property with user-defined type inside expression + LABEL t3l2 PROPERTIES ((row(x, 1)::t_udt2).a AS p2)) EDGE TABLES ( e1 SOURCE KEY (a) REFERENCES t1 (a) @@ -37,8 +51,10 @@ CREATE PROPERTY GRAPH g2 DROP TABLE t1; -- fail ALTER TABLE t1 DROP COLUMN b; -- non-key column; fail ALTER TABLE t1 DROP COLUMN a; -- key column; fail +DROP TABLE t_udt1; -- fail +DROP TABLE t_udt2; -- fail --- like g2 but assembled with ALTER +-- property graph assembled using ALTER CREATE PROPERTY GRAPH g3; ALTER PROPERTY GRAPH g3 ADD VERTEX TABLES (t1 KEY (a), t2 DEFAULT LABEL); ALTER PROPERTY GRAPH g3 base-commit: 58ff54aee8b252b23b35c7a6297bf9189731bca3 -- 2.34.1
