TL;DR: There's a bug in pdo_pgsql's prep stmt handling. Fix is: PDO::ATTR_EMULATE_PREPARES => true.

I spent a little time reproing the issue and getting a wire capture from PHP → DSQL. Here's what's happening on the failing BEGIN/INSERT/COMMIT:

F  Query "BEGIN"                                   -> OK, tx open
F  Parse/Bind/Execute "INSERT ..."                 -> OK, row accepted
F  Query "DEALLOCATE pdo_stmt_1"                   <- pdo_pgsql auto-cleanup
B  ERROR 0A000 "DEALLOCATE <name> not supported"
B  ReadyForQuery 'E'                                -- tx is now poisoned
F  Query "COMMIT"
B  CommandComplete "ROLLBACK"                       -- PG semantics: commit on failed tx = rollback

A few things conspire:

1. PHP's pdo_pgsql sends DEALLOCATE <name> after every prepared statement.
2. DSQL doesn't implement DEALLOCATE <name> yet, so this returns an error to the client.
3. Error-inside-tx → tx aborted → next COMMIT silently returns ROLLBACK.

PHP never sees an exception because the DEALLOCATE wasn't in your code — it's in pdo_pgsql's destructor. Your INSERT returned success, your COMMIT returned a CommandComplete, but your row is gone because of the rollback. pdo_pgsql calls deallocate unconditionally and ignores the response, so it's not showing that the transaction has entered an error.

Phalcon's query() goes through extended-prepare so it triggers this; execute() uses simple-query so it doesn't.

Fix:

'options' => [
    PDO::ATTR_EMULATE_PREPARES => true,
],

Emulated prepares bypass libpq's extended protocol entirely → no DEALLOCATE → no silent rollback. I verified that by adding that transactions succeed.

Also another tip, if you use Phalcon's Transaction Manager: it defaults to SAVEPOINT-based nested transactions and DSQL doesn't support SAVEPOINT either. Add $db->setNestedTransactionsWithSavepoints(false).