# PostgreSQL driver

`Bootgly\ADI\Databases\SQL\Drivers\PostgreSQL` implements PostgreSQL Protocol 3.0
natively — startup, authentication, TLS, simple and extended query protocols, pipelining
and cancellation — with zero dependencies. It is the default Bootgly driver.

## Connecting

```php
use Bootgly\ADI\Databases\SQL;

$Database = new SQL([
   'driver' => 'pgsql',
   'host' => '127.0.0.1',
   'port' => 5432,
   'database' => 'app',
   'username' => 'postgres',
   'password' => 'secret',
]);

$Operation = $Database->await($Database->query('SELECT version() AS version'));
$Operation->Result->cell;
```

Operations are asynchronous: `query()` returns a pending `Operation` driven by `await()`
or by the Fiber scheduler under the HTTP server.

## Authentication and TLS

Supported authentication methods: **cleartext**, **MD5** and **SCRAM-SHA-256** (channel
binding is not negotiated). TLS is negotiated through `SSLRequest` and controlled by
`secure.mode`: `disable`, `prefer` (fall back to plaintext when refused), `require`,
`verify-ca` and `verify-full` — with `peer` and `cafile` for certificate pinning.

## Prepared statements

Parameterized queries use the extended protocol (`Parse`/`Bind`/`Describe`/`Execute`/
`Sync`) with a per-connection LRU statement cache (`statements` config key, default
`256`). Statements are named `bootgly_{sha1(sql)}` and evicted with `Close`:

```php
$Row = $Database->await($Database->query(
   'SELECT id, name FROM users WHERE mail = $1 AND active = $2',
   ['ada@bootgly.com', true]
));
```

The PostgreSQL dialect uses `$1..$n` placeholders. Results hydrate by type OID: booleans,
integers, floats, `bytea` and temporal types become native PHP values
(`DateTimeImmutable` for dates/timestamps); `numeric` stays a string.

## Generated keys

The dialect supports `RETURNING` — the ORM appends it to mutations automatically and
hydrates saved entities from the returned rows:

```php
$Saved = $Repository->hydrate(
   $Database->await($Repository->save($User))
)->entity;
```

## Pipelining

Co-located operations pipeline on one connection: multiple in-flight commands share the
socket and resolve in FIFO order as backend messages arrive. The Pool co-locates
operations onto busy connections automatically when the pool is at capacity.

On a transport failure (socket write/read error, peer close, framing corruption) the
driver fails every pipelined operation, resets its session state and disconnects, so the
Pool drops the dead connection instead of keeping it busy.

## Cancellation

```php
$Running = $Database->query('SELECT pg_sleep(30)');
$Database->advance($Running);

$Database->cancel($Running);
```

Sends a `CancelRequest` through a separate connection using the backend key data —
advisory: the operation still resolves, fails or expires on the main socket.

Only an operation that is actually in flight produces one. A cancel request names a backend,
not a statement, so cancelling an operation that has already finished — or one composed by
`query()` but never advanced — sends nothing at all and withdraws it locally instead.

## Reference

```php
query (string $sql, array $parameters = []): Operation
```

Creates one pending operation. Without parameters it uses the simple query protocol;
with parameters, the extended protocol with the statement cache.

```php
prepare (Operation $Operation): Operation
```

Builds the wire messages for the operation — `Query`, or
`Parse + Describe + Bind + Execute + Sync` with cache-aware reuse.

```php
advance (Operation $Operation): Operation
```

Drives the connection state machine: connect, SSL negotiation, startup, authentication,
command write and backend message read.

```php
cancel (Operation $Operation): Operation
```

Sends the advisory `CancelRequest` side-channel packet. Requires `BackendKeyData` from the
connection startup; marks the operation `cancelled`.

```php
abandon (Operation $Operation): void
```

Reconciles the wire when the Pool finishes an operation from the outside — an elapsed
deadline — while the backend is still answering its batch. The pipeline slot is handed to
a detached stand-in that absorbs the remaining messages up to its `ReadyForQuery`, so the
driver keeps every session effect it owes itself (statement caching, evictions) while the
operation the Pool took back is never read, written or resolved again. When no sibling is
left to pump the answer, the session is dropped instead, which is what gives the
connection slot back.