Debug PostgreSQL pgRouting SQL for Robot Trajectories

A Python + SQL project that demonstrates debugging PostgreSQL pgRouting queries for computing minimum-energy robot trajectories over probabilistic roadmap (PRM) graphs. Includes a buggy pipeline and its fixed counterpart.

---

The Problem

In autonomous robotics navigation, reproducing a robotics report's minimum-energy waypoint trajectory requires computing path costs across a Probabilistic Roadmap (PRM) stored in a PostgreSQL database with pgRouting.

The initial implementation contained critical failures that prevented accurate trajectory generation and cost matrix calculations:

• Stale Column Names: Queries referenced obsolete schema definitions instead of standard pgRouting fields.
• SQL Injection Vulnerabilities: Dynamic SQL queries concatenated raw input strings directly without proper parameterization or escaping.
• Invalid pgRouting Function Calls: Malformed function calls passed incorrect argument types or signature structures to pgr_dijkstraCostMatrix.
• Wrong Directionality: Graph traversal was incorrectly evaluated as directed, ignoring valid bidirectional robot movements across PRM edges.
• Missing DDL & Schema Setup: Required PostGIS/pgRouting extensions and database tables were omitted or incomplete.

---

Bug Categories

The pipeline identifies and fixes five primary categories of SQL/pgRouting bugs:

1. Stale / Incorrect Column Names in SQL Query

pgRouting inner queries require specific standard column names (id, source, target, cost, and optionally reverse_cost). Stale schema names such as from_node/to_node or weight cause runtime failures or silent execution errors within pgRouting functions.

2. SQL Injection Vulnerabilities

Constructing dynamic SQL queries via string format specifiers or f-strings with raw array values introduces SQL injection risks and formatting errors. Proper escaping or parameterized dynamic query generation ensures secure execution.

3. Invalid pgRouting Function Calls / Signatures

Calling pgr_dijkstraCostMatrix with invalid parameters, out-of-order arguments, or incorrect column types in the inner query causes PostgreSQL function signature mismatch errors.

4. Wrong Directionality / Undirected Graph Misconfiguration

Robotic PRM graphs frequently permit bidirectional traversal. Setting directed => true without populating reverse_cost or configuring directed => false results in missing paths or infinite aggregate cost calculations between reachable waypoints.

5. Missing DDL / Table Schema Mismatches

Missing CREATE EXTENSION IF NOT EXISTS pgrouting; directives or incomplete table definitions prevent database initialization and cause missing-table runtime exceptions.

---

Project Structure

• README.md - Project overview, bug taxonomy, usage guide, and documentation index.
• requirements.txt - Python dependencies for running pipeline scripts and test suites.
• docs/pgrouting_api_contract.md - Local API contract and specification for pgr_dijkstraCostMatrix.

---

The pgRouting API Contract

For detailed parameter rules, return types, edge SQL schema requirements, and behavioral notes on cost matrix generation, refer to the local cached documentation:

• docs/pgrouting_api_contract.md

---

Requirements

• Python: 3.9 or higher
• PostgreSQL: 12+ with PostGIS and pgRouting extensions enabled (required for live query execution; project generation and offline validation work standalone)
• Python Libraries: psycopg2-binary, requests, pytest

Install dependencies:

pip install -r requirements.txt

---

Usage

Run Test Suite

pytest

Run Buggy Pipeline (Demonstration of Failure Modes)

python buggy_pipeline.py

Run Fixed Pipeline (Verified Execution)

python fixed_pipeline.py