Getting Started

Minimal Steps

Installation:
```
composer require 1tomany/postgres-bundle
```
Ensure your Laravel app uses Doctrine ORM (via spatie/laravel-doctrine-orm or Symfony bridge).

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

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

First Use Case: Use the AdvisoryLockManager to acquire and release locks in a multi-step workflow:

use OneTomany\PostgresBundle\Lock\AdvisoryLockManager;

// Inject via Laravel's service container
$lockManager = app(AdvisoryLockManager::class);

// Acquire a composite lock (e.g., "user:123:profile")
$lock = $lockManager->acquire('user:123:profile');

// Simulate multi-step workflow
try {
    // Step 1: Update profile
    $lockManager->unlockPartial('profile'); // Release partial lock

    // Step 2: Update orders (new lock)
    $lockManager->acquire('user:123:orders');
    // ...
} finally {
    $lockManager->release(); // Release all remaining locks
}

Where to Look First

Lock Manager: OneTomany\PostgresBundle\Lock\AdvisoryLockManager (core class for lock operations).
Configuration: config/packages/onetomany_postgres.yaml (adjust connection and time_zone).
Middleware: Built-in DBAL middleware for PostgreSQL-specific behavior (e.g., time-zone handling).

Implementation Patterns

Usage Patterns

Multi-Step Workflows: Use acquire() for initial lock, then unlockPartial() to release segments incrementally:

$lockManager->acquire('order:123:payment'); // Lock entire order
// Process payment...
$lockManager->unlockPartial('payment');    // Release payment segment
// Process inventory...
$lockManager->unlockPartial('inventory');  // Release inventory segment

Retry Logic: Release locks only after compensating actions succeed:

try {
    $lockManager->acquire('user:456:transaction');
    // Attempt operation...
} catch (OperationFailedException $e) {
    $lockManager->unlockPartial('transaction'); // Release before retry
    throw $e;
}

Batch Processing: Guard partial progress in large datasets:

$lockManager->acquire('batch:789:segment_1');
// Process segment 1...
$lockManager->unlockPartial('segment_1');
$lockManager->acquire('batch:789:segment_2');
// Process segment 2...

Workflows

Queue Workers: Integrate with Laravel queues for async lock management:

public function handle(Job $job) {
    $lockManager = app(AdvisoryLockManager::class);
    $lock = $lockManager->acquire('job:'.$job->id.':processing');
    // Process job...
    $lockManager->release();
}

Database Transactions: Combine with Doctrine transactions for atomicity:

$entityManager = app('doctrine')->getManager();
$lockManager = app(AdvisoryLockManager::class);

$entityManager->beginTransaction();
$lockManager->acquire('user:789:update');
try {
    // Update entity...
    $entityManager->flush();
    $lockManager->release();
    $entityManager->commit();
} catch (\Exception $e) {
    $lockManager->release();
    $entityManager->rollback();
    throw $e;
}

Integration Tips

Service Container Binding: Bind the AdvisoryLockManager in Laravel’s AppServiceProvider:

public function register() {
    $this->app->bind(
        OneTomany\PostgresBundle\Lock\AdvisoryLockManager::class,
        function ($app) {
            return new AdvisoryLockManager(
                $app['db']->connection('pgsql')->getDoctrineConnection()
            );
        }
    );
}

Lock Naming Strategy: Use composite keys for partial releases:

resource:type:id:segment  // e.g., "order:123:payment:shipping"

Middleware Integration: Extend DBAL middleware for custom logic:

use OneTomany\PostgresBundle\Middleware\TimeZoneMiddleware;

$connection->getConfiguration()->addMiddleware(
    new TimeZoneMiddleware('America/New_York')
);

Testing: Mock the AdvisoryLockManager in unit tests:

$lockManager = Mockery::mock(AdvisoryLockManager::class);
$lockManager->shouldReceive('acquire')->andReturnTrue();
$lockManager->shouldReceive('unlockPartial')->andReturnTrue();

Gotchas and Tips

Pitfalls

PostgreSQL Version:
- Advisory locks require PostgreSQL 9.5+. Test with:
```
SELECT version();
```
- Fix: Upgrade PostgreSQL or use a compatibility layer.

Lock Leakage:

Partially released locks may linger if exceptions occur during unlockPartial().

Fix: Use finally blocks or wrap in transactions:

try {
    $lockManager->acquire('lock:key');
    // ...
    $lockManager->unlockPartial('segment');
} finally {
    $lockManager->release(); // Ensure all locks are released
}

Deadlocks:
- Iterative releases can create circular dependencies (e.g., lock('A:B') and lock('B:A')).
- Fix: Design lock hierarchies (e.g., always lock user:123:profile before user:123:orders).
Transaction Boundaries:
- Advisory locks are not transactional. A ROLLBACK does not release them.
- Fix: Explicitly release locks in finally blocks or use SIGNAL/RESIGNAL in PostgreSQL.
Composite Key Complexity:
- Overly granular segments (e.g., user:123:profile:address:city) may cause performance issues.
- Fix: Balance granularity with simplicity (e.g., user:123:profile vs. user:123:profile:address).
Time-Zone Mismatches:
- Middleware time-zone settings (e.g., UTC) may conflict with application time zones.
- Fix: Align onetomany_postgres.yaml with your app’s default time zone.

Debugging

Check Active Locks: Query PostgreSQL for active advisory locks:
```
SELECT * FROM pg_locks WHERE locktype = 'advisory';
```
Log Lock Operations: Enable debug logging in config/packages/onetomany_postgres.yaml:
```
onetomany_postgres:
    debug: true
```
Monitor Contention: Use pg_stat_activity to identify blocking locks:
```
SELECT * FROM pg_stat_activity WHERE state = 'active';
```

Config Quirks

Default Connection: The bundle defaults to the first Doctrine connection. Explicitly set connection in config:

onetomany_postgres:
    advisory_lock_manager:
        connection: "pgsql"  # Must match Laravel's DB config

Middleware Order: DBAL middleware runs before queries. Ensure time-zone middleware is last:

onetomany_postgres:
    middleware:
        - time_zone
        - custom_middleware  # Add your middleware here

Case Sensitivity: Lock names are case-sensitive in PostgreSQL. Use consistent formatting:

// Avoid:
$lockManager->acquire('User:123:Profile'); // May fail if stored as 'user:123:profile'

// Prefer:
$lockManager->acquire('user:123:profile');

Extension Points

Custom Lock Manager: Extend AdvisoryLockManager for project-specific logic:

class CustomLockManager extends AdvisoryLockManager {
    public function acquireWithRetry($key, $retries = 3) {
        for ($i = 0; $i < $retries; $i++) {
            if ($this->acquire($key)) {
                return true;
            }
            sleep(1);
        }
        return false

Postgres Bundle Laravel Package