“Total sales for every department under the group” leaves important questions unanswered: can the root own orders, which organizational date applies, and can a department have multiple parents? Recursive SQL discovers paths; it cannot decide how often their amounts should contribute. This article uses PostgreSQL 18 semantics, starting with a single-parent forest before considering multi-parent graphs. All data are constructed; no database experiment was run.
Separate node sets, traversal paths and business facts
Double counting in multi-table joins shows why amounts need reconciliation by fact identity. Hierarchies introduce another source of multiplication: the same department can be reached from several starting points or through several paths. Decide whether the requested result is a node set, a list of paths or quantities expanded along paths before deciding what to deduplicate.
Consider one tenant's organization version: G is the root; A and B report to G; C reports to A. A, B and C each own one order worth 100, 200 and 50 respectively, while G owns none. G and all descendants comprise G, A, B and C, with total sales of 350. Include the root in the set, or real orders booked directly to it will disappear.
If a request returns separate subtree rows for G and A, the correct figures are 350 and 150. Both rows can be correct while their sum, 500, is not the group total: A and C overlap. If the request means the union of the two scopes, the answer remains 350. Whether output groups are disjoint belongs in the metric definition.
Define tenant_id, hierarchy_version, node_id and parent_id before implementation, together with the fact key, monetary unit, status and time filters. Names are not node identities. Names may repeat, and identifiers may be reused across tenants or versions. A query generator needs this contract and the authorized organizational scope.
An existing parent does not prove that the data form a tree
PostgreSQL documents self-referential foreign keys: a null parent can identify a top-level node, while a non-null parent must reference an existing row.[2] In this model, use tenant_id, hierarchy_version and node_id as a composite primary key, and reference that key using the same tenant and version plus parent_id. Tenant and version must be non-null; only parent_id is nullable for roots.
Yet A can refer to B while B refers to A: both parents exist, but the structure cycles. A row-level check that parent_id differs from node_id excludes self-loops only, not longer cycles. Acyclicity, root counts and complete reachability require additional validation; passing foreign-key checks does not establish them.
Before activating an import, check duplicate node identities, cross-tenant or cross-version parent references, missing parents and self-loops, then multi-node cycles and expected roots. Quarantine legacy orphans with an explicit list rather than silently converting them into roots. Otherwise amounts can be reassigned to an apparently valid top-level organization.
A forest can have several legitimate roots. A single-tree contract requires one root and reachability of every node. Traversal downward from valid roots misses a disconnected cyclic component. Compare all nodes with the root-reachable set and diagnose the remainder; finding no cycle along the inspected paths is not proof that the entire table is acyclic.
Retain traversal failures instead of disguising truncation as completeness
For a single-parent hierarchy, anchor the traversal at the authorized roots for one tenant and organization version. Repeatedly match each child's parent_id to the current node_id while preserving tenant and version at every step. Useful output includes root_id, node_id, depth and a diagnostic path. If depth starts at zero, roots have depth zero; review both the anchor rule and the direction of the recursive join.
PostgreSQL 18 offers CYCLE syntax to track paths and mark cyclic rows, as well as the option of maintaining a visited-node array explicitly.[1] Cycle identity must include the full node key, or node_id alone where tenant and version are already fixed. A marked row is evidence of invalid structure. Filtering it out does not make the remaining subtotal a certified complete answer.
Replacing UNION ALL with UNION is not a general cycle remedy. With increasing depth or path columns, a repeated node can still produce a distinct complete row.[1] An outer LIMIT is also unsuitable as the production safeguard: sorting or joining may require the entire recursive output first. Apply execution timeouts, resource budgets and depth bounds, treating exhausted bounds as incomplete work.
A depth bound limits work; it does not prove correctness. If maximum allowed depth is two and a depth-two node still has legitimate children, report truncation. Wide, shallow graphs can generate many paths without exceeding that bound. Monitor expanded node count, path count and execution time, and stop publication of monetary results when traversal is incomplete or structurally invalid.
Deduplicate identities for node rollups, but preserve path multipliers
For organizational subtree sales, first construct a unique node_id set within each root_id, then join eligible order facts. If each order has exactly one confirmed department, it contributes once per root. Different roots can overlap, so explain when output rows cannot be added. For a union of several roots, construct a unique department set across the roots before aggregation.
Represent multiple parents with an explicit edge table and determine whether edges mean management, collaboration or allocation. Suppose G reaches A and B, both of which reach C. An order worth 50 at C reaches G by two paths. If the measure covers unique departments, it contributes 50, not 100. Allocating it to two responsibility centers instead requires approved weights summing to one.
DISTINCT on the amount cannot repair this. Separate orders worth 50 at A and C are still two facts. Check contributions by order identity and target-root identity. An order assigned to multiple departments without an allocation rule is a semantic ambiguity, not a recursive-syntax problem; block certified aggregation and return the conflicting records.
A bill of materials is the crucial counterexample. Suppose one P needs two A and three B; each A needs four X and each B needs five X. One P requires 2×4+3×5=23 X. The two paths encode different requirements: multiply along each path, then add. Deduplicating X as a node and retaining only one occurrence destroys quantity semantics. This example excludes scrap, substitutions and unit conversion, all of which require separate rules in a real BOM.
Fix the relationship version before choosing a storage strategy
Current parent-child relationships cannot automatically answer every historical question after a reorganization. Transaction-time ownership, restatement under today's organization and reproduction of a previously released report are different requirements. Bind certified reporting to a validated relationship version. Where temporal calculation is necessary, design effective dates and unique-version checks; do not mix old and new relationships in one expansion.
An adjacency list stores direct relationships and is relatively straightforward to update, but requires traversal for rollups. A closure table precomputes ancestor-descendant relationships to reduce repeated query work; moving a subtree, deleting an edge or changing a version requires maintained and validated derived relationships. For organizational sets, a closure key typically includes version, ancestor and descendant. A set table alone cannot represent path multiplicities or BOM multipliers.
PostgreSQL's ltree extension stores hierarchical label paths and provides ancestor/descendant operators with index support.[3] It is an implementation option for path queries, not automatic governance. Applications still need rules and tests for whether renaming changes paths, how moving a subtree updates descendants, and whether multiple parents require multiple stored paths.
Validated derived structures can suit frequent queries, while infrequent or rapidly changing relationships may favor on-demand expansion. Compare update amplification, rebuild time, storage and cutover cost alongside query latency. Prepare a new version, validate structure and control totals, then switch the read pointer. On failure retain the prior certified version rather than expose partially updated relationships.
Accept the system through counterexamples and set reconciliation
First use G, A, B and C to require G=350, A=150 and the union of G and A's scopes=350. Add an order worth 20 directly to G: G should become 370 while A remains unchanged. Second, introduce equal amounts on distinct order identifiers; both must contribute, exposing amount-based deduplication.
Third, inject a missing parent, self-loop, two-node cycle, disconnected cyclic component and wrong-version reference. Require explicit detection and blocked certified publication, not merely query termination. Fourth, add a diamond with multiple paths and separately verify unique departments and path counts. Use the 23-X BOM example to ensure those meanings are not conflated.
Fifth, check boundaries. Departments without orders can still belong to the organizational scope. Enforce explicit authorization for nodes and facts; recursion must not expand access. If permission filtering produces an incomplete visible subtree, state that scope rather than label the result a complete group total. Depth truncation, timeouts and stale derived versions must produce identifiable incomplete states.
Finally, retain expected and actual department sets, uncovered nodes, repeated fact contributions within each root and an independent control total from the facts. Totals alone can conceal an omission offset by double counting elsewhere. This article supplies an acceptance design and semantic derivations. Run them against the target database, permissions and real scale, and retain raw results before making correctness or performance commitments.