Download the PHP package aeatech/transaction-manager-postgresql without Composer

AEATech Transaction Manager – PostgreSQL

Lightweight module for generating safe and efficient PostgreSQL statements:

• INSERT
• INSERT ... ON CONFLICT DO NOTHING (aka INSERT IGNORE)
• INSERT ... ON CONFLICT ... DO UPDATE (UPSERT)
• DELETE, DELETE with LIMIT (via ctid)
• UPDATE, UPDATE with CASE WHEN ... THEN ...

This package is an extension of aeatech/transaction-manager-core. It only builds SQL and parameters; the core package handles execution, retries, and transaction boundaries. For Doctrine DBAL users, there is an adapter package: aeatech/transaction-manager-doctrine-adapter.

System requirements:

• PHP >= 8.2
• ext-pdo
• ext-pdo_pgsql
• PostgreSQL 12+ (tested with 16; ctid-based DELETE LIMIT is PostgreSQL-specific)

Installation (Composer):

Quick start

Usage examples

1) INSERT

Prepared Statement Reuse Hint

This package supports the optional prepared statement reuse hint via StatementReusePolicy from the Core package. It is a best‑effort performance hint that may be ignored by connection implementations.

Options:

• StatementReusePolicy::None — no reuse (default)
• StatementReusePolicy::PerTransaction — attempt to reuse within a single DB transaction
• StatementReusePolicy::PerConnection — attempt to reuse across transactions while the physical connection remains open

Example with a PostgreSQL transaction factory:

Notes:

• This is a performance hint only; do not depend on it for correctness or idempotency.
• A connection adapter may ignore the hint due to driver limitations, reconnections, or internal safety choices.

2) INSERT IGNORE (ON CONFLICT DO NOTHING)

3) UPSERT (ON CONFLICT DO UPDATE) by columns

4) UPSERT (ON CONFLICT DO UPDATE) by constraint name

5) DELETE by identifiers

6) DELETE with LIMIT (PostgreSQL only, via ctid)

7) UPDATE by identifiers

8) UPDATE WHEN ... THEN ...

9) Raw SQL

Parameters and types

• rows: array of homogeneous associative arrays like ['column' => value, ...]. All rows must have the same set of keys (columns).
• columnTypes: array<string, int|string> — mapping column => parameter type (PDO::PARAM_, `Doctrine\DBAL\ParameterType::` or string type names supported by DBAL). Optional — DBAL will try to infer types.
• isIdempotent: a flag for the transaction manager indicating retry safety. Semantics depend on your retry policy:
  • false (default): a re-run may change the outcome (e.g., plain INSERT).
  • true: the statement is designed to be idempotent (e.g., UPSERT or DO NOTHING), allowing the manager to apply more aggressive retries.

Additional notes for delete/update:

• identifierColumn / identifiers:
  • Use a primary key or another unique column to avoid unintended data changes.
  • Provide a non-empty array of scalar identifiers.
• DELETE with LIMIT:
  • Implemented via ctid selection inside a subquery. Guarantees that at most N physical rows are deleted, even if the identifier column is not unique.
  • limit must be a positive integer; not all provided identifiers may be deleted in one run.
• UPDATE by identifiers:
  • updateColumnTypes apply to the columns in SET clause; the identifier type is provided separately via identifierColumnType.
• UPDATE WHEN ... THEN:
  • rows must include the identifier column and all columns listed in updateColumns.

PostgreSQL specifics and nuances

• ON CONFLICT targets:
  • conflictTargetByColumns([col1, col2, ...]) generates ON CONFLICT (col1, col2, ...) — all listed columns must be present in each inserted row; otherwise an InvalidArgumentException will be thrown during validation.
  • conflictTargetByConstraint('constraint_name') generates ON CONFLICT ON CONSTRAINT "constraint_name" — column presence is not validated by the builder, but the constraint must exist in the DB.
• Identifier quoting:
  • Pass names without quotes — the library uses PostgreSQLIdentifierQuoter to quote identifiers with double quotes.
• Idempotency hints:
  • createInsertIgnore and createInsertOnConflictUpdate are typically idempotent — pass isIdempotent: true if your business logic agrees.
  • Plain createInsert is usually non-idempotent.
• Isolation level:
  • Choose appropriate TxOptions::isolationLevel. For UPSERT-heavy workloads, ReadCommitted is common, but Serializable can be paired with retries for stricter guarantees.
• Error classification & retries:
  • Use GenericErrorClassifier(new PostgreSQLErrorHeuristics()) with the core TransactionManager. It classifies transient issues (e.g., 40001 serialization failure, 40P01 deadlock, 55P03 lock not available) and connection losses for safe retries according to your RetryPolicy.
• Large batches:
  • Insert in batches (e.g., 100–1000 rows) to avoid driver limits and huge statements.
• JSON, arrays, custom types:
  • For complex types, rely on your DBAL mapping and pass explicit columnTypes when needed.
• DELETE with LIMIT (ctid) caveats:
  • The physical row order is not guaranteed. If you need deterministic ordering, perform chunking at the application level with stable predicates and small limits.

How it works

• SQL and parameters are built inside transaction objects (InsertTransaction, InsertIgnoreTransaction, InsertOnConflictUpdateTransaction, etc.).
• Identifiers (table and column names) are quoted safely for PostgreSQL.
• The result is an AEATech\TransactionManager\Query (from the core), which is executed via the provided connection adapter.

Running tests

1) Via Docker Compose (recommended for reproducibility)

Bring up services for your target PHP/PostgreSQL versions and run PHPUnit inside the PHP CLI containers.

Start services (PHP 8.2/8.3/8.4 with PostgreSQL 16, 17, 18 see docker/docker-compose.yml for details):

Install dependencies inside the PHP container (example for PHP 8.3):

Run tests for PHP 8.2 and PostgreSQL 16:

For PHP 8.3 and PostgreSQL 16:

For PHP 8.4 and PostgreSQL 16:

Run tests for PHP 8.2 and PostgreSQL 17:

For PHP 8.3 and PostgreSQL 17:

For PHP 8.4 and PostgreSQL 17:

Run tests for PHP 8.2 and PostgreSQL 18:

For PHP 8.3 and PostgreSQL 17:

For PHP 8.4 and PostgreSQL 17:

Run all configured variants:

Run phpstan

To stop and remove containers:

License

This project is licensed under the MIT License. See the LICENSE file for details.