Postgres Bundle Laravel Package

Technical Evaluation

Architecture fit The 1tomany/postgres-bundle introduces PostgreSQL-specific advisory lock enhancements tailored for Symfony/Doctrine, which can be adapted to Laravel via Doctrine ORM integration. Its iterative lock release feature is a critical fit for Laravel applications requiring:

• Multi-step transactions (e.g., payment processing, inventory reservations).
• Distributed concurrency control (e.g., queue workers, microservices).
• Fault-tolerant workflows where partial progress must be guarded (e.g., retries, rollbacks).

Key architectural strengths:

• Additive to Doctrine: Extends existing lock mechanisms without replacing them, enabling gradual adoption.
• PostgreSQL-native: Avoids external dependencies (e.g., Redis), reducing latency and infrastructure complexity.
• Middleware support: Built-in time-zone handling (e.g., UTC) simplifies distributed systems.

Integration feasibility

• Moderate risk: Requires:
  • Doctrine ORM (via spatie/laravel-doctrine-orm or Symfony bridge).
  • PostgreSQL (advisory locks are PostgreSQL-specific).
  • Composite lock naming (e.g., user:123:profile) for iterative releases.
• Migration path:
  1. Replace custom advisory lock logic with the bundle’s AdvisoryLockManager.
  2. Refactor lock names to composite format.
  3. Test iterative release patterns (e.g., unlockPartial()).

Technical risk

• High for custom lock logic:
  • Backward compatibility: Existing atomic unlocks may break with iterative releases.
  • Lock leakage: Partial releases could linger if segment tracking fails (e.g., PostgreSQL connection drops).
  • Deadlocks: Iterative releases may introduce circular dependencies (e.g., lock('A:B') + lock('B:A')).
• Key risks:
  • Transaction boundaries: Advisory locks are not transactional; test with BEGIN/COMMIT rollbacks.
  • Performance: Advisory locks in PostgreSQL are heavier than Redis; benchmark under high contention.
  • PostgreSQL version: Requires 9.5+ (verify compatibility).

Key questions

1. How does the bundle handle transaction boundaries? (e.g., Does unlockPartial() persist across ROLLBACK?)
2. Are there performance benchmarks for iterative releases vs. atomic unlocks?
3. How are lock timeouts managed for partial releases? (e.g., Does unlockPartial() reset the TTL?)
4. Does the bundle support custom lock stores (e.g., Redis fallback), or is PostgreSQL mandatory?
5. What happens if a PostgreSQL connection drops during a partial release?
6. Are there tools to audit lock segments (e.g., getActiveSegments())?
7. How does this interact with Laravel’s queue workers? (e.g., Will partial releases work across job retries?)
8. Laravel-specific: How to bind AdvisoryLockManager to Laravel’s service container?
9. Monitoring: Are there metrics for lock contention or partial release failures?
10. Rollback: How to disable iterative releases if they introduce instability?

Integration Approach

Stack fit

• Laravel compatibility:
  • Doctrine ORM: Requires spatie/laravel-doctrine-orm or manual Symfony bridge setup.
  • PostgreSQL: Mandatory for advisory locks; no MySQL/SQLite support.
  • Service container: Integrates via Symfony’s AdvisoryLockManager; bind to Laravel’s container using:

    $this->app->bind('advisory_lock_manager', function ($app) {
        return new \Onetomany\PostgresBundle\Lock\AdvisoryLockManager(
            $app['dbal.connection'],
            $app['onetomany_postgres.config']
        );
    });
    

• Alternative stacks:
  • Symfony: Native fit; minimal changes needed.
  • Plain PHP: Possible but requires manual Doctrine setup.

Migration path

1. Prerequisites:
   • Upgrade to Laravel 10.x (for Symfony 8.1 compatibility).
   • Install spatie/laravel-doctrine-orm and symfony/dom-coder if not using Symfony.
   • Configure PostgreSQL advisory locks in postgresql.conf (if needed).
2. Installation:

   composer require 1tomany/postgres-bundle symfony/dom-coder
   

3. Configuration:
   • Add onetomany_postgres.yaml to config/packages/:

     onetomany_postgres:
         advisory_lock_manager:
             connection: "pgsql"  # Laravel's DB connection name
         middleware:
             time_zone: "UTC"
     

   • Bind the manager in AppServiceProvider:

     public function register()
     {
         $this->app->register(\Onetomany\PostgresBundle\PostgresBundle::class);
     }
     

4. Lock refactoring:
   • Replace custom advisory lock logic:

     // Before
     DB::select("SELECT pg_advisory_lock(12345)");
     
     // After
     $lockManager = $this->app->make('advisory_lock_manager');
     $lockManager->acquire('user:123:profile');
     

   • Update lock names to composite format (e.g., user:123:profile).
5. Testing:
   • Unit tests: Verify acquire(), unlockPartial(), and release() methods.
   • Integration tests: Test multi-step workflows (e.g., payment → inventory).
   • Chaos tests: Simulate PostgreSQL failures (e.g., pg_advisory_lock timeouts).

Compatibility

• Laravel versions: Tested with Laravel 10.x (Symfony 8.1); may require compatibility layer for 9.x.
• Doctrine versions: Requires DBAL 3.5+ and ORM 2.10+.
• PostgreSQL versions: Advisory locks require 9.5+.
• Third-party conflicts:
  • Low: No conflicts with Laravel’s native locking (e.g., Semaphore, Redis).
  • High: Custom lock implementations (e.g., LockManager extensions) may need updates.

Sequencing

• Critical path:
  1. Deploy to a staging environment with PostgreSQL.
  2. Canary release: Enable iterative locks for non-critical workflows (e.g., batch jobs).
  3. Monitor: Track lock-related errors (e.g., AdvisoryLockException) and PostgreSQL logs (pg_stat_activity).
• Rollback plan:
  • Downgrade to v0.0.7 if partial releases introduce instability.
  • Use feature flags to disable iterative unlocking temporarily:

    if (config('features.iterative_locks')) {
        $lockManager->unlockPartial('profile');
    } else {
        $lockManager->release();
    }
    

Operational Impact

Maintenance

• Increased:
  • Lock debugging: Requires understanding composite segments (e.g., "Why is user:123:orders still locked?").
    • Tooling: Add logging for lock acquisition/release:

      $lockManager->setLogger($this->app->make('logger'));
      

  • PostgreSQL monitoring: Track advisory lock contention:

    SELECT * FROM pg_stat_activity WHERE locktype = 'advisory';
    

  • Configuration management: Time-zone and connection settings in onetomany_postgres.yaml.
• Reduced:
  • Custom lock logic: Eliminates need for manual advisory lock handling.
  • Deadlocks: Iterative releases reduce contention in long-running workflows (e.g., payment retries).

Support

• Documentation gaps:
  • Lock naming conventions: Document composite key formats (e.g., resource:type:id).
    • Example: user:123:profile, order:456:inventory.
  • Iterative release patterns: Provide workflow examples:

    // Multi-step payment processing
    $lockManager->acquire('payment:789');
    try {
        $lockManager->unlockPartial('authorization'); // Release after auth
        // Process payment...
        $lockManager->unlockPartial('capture');      // Release after capture
    } catch (\Exception $e) {
        $lockManager->release(); // Full release on failure
    }
    

  • Error handling: Document exceptions (e.g., AdvisoryLockTimeoutException).
• Support channels:
  • GitHub Issues: Limited activity; expect community-driven support.
  • PostgreSQL expertise: Requires familiarity with advisory locks (e.g., pg_advisory_lock).

Scaling

• Performance considerations:
  • Advisory lock overhead: Benchmark under