feat(postgres): read each side as of one instant · DBDiff/DBDiff@d8dc7c6 · GitHub
Skip to content

Commit d8dc7c6

Browse files
jasdeepkhalsaclaude
jasdeepkhalsa
and
claude
committed
feat(postgres): read each side as of one instant
A diff reads a database in many queries. A change made while it ran — a migration, a table someone added — was seen by some of them and not others, and the diff could describe a database that never existed: a foreign key to a table it did not read, a view on a column it read before the column was dropped. Each side now opens a REPEATABLE READ, READ ONLY transaction that exports its snapshot and stays open for the run, and the connection DBDiff reads with joins it, including the one made again for each schema of a multi-schema diff. Definitions are still rendered with temporary objects, inside a savepoint that is rolled back; a read that may fail runs inside one too, so the transaction stays usable. A server that cannot export a snapshot is read as before, and the log says so. As with pg_dump, what the reads lock stays locked until the run ends. --no-snapshot reads each query on its own. MySQL and SQLite are unchanged. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
1 parent 34c9be2 commit d8dc7c6

13 files changed

Lines changed: 334 additions & 49 deletions

‎README.md‎

Lines changed: 2 additions & 0 deletions

‎src/DB/DBManager.php‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
<?php namespace DBDiff\DB;
22

33
use DBDiff\DB\Support\PostgresSessionOptions;
4+
use DBDiff\DB\Support\PostgresSnapshot;
45
use DBDiff\DB\Support\ServerSettings;
56

67
use DBDiff\DB\Support\ComputedColumns;
@@ -22,6 +23,9 @@ class DBManager {
2223
/** The schema useSchema() last pointed the connections at. */
2324
protected ?string $schema = null;
2425

26+
/** @var array<string, string> each side's PostgreSQL snapshot, which its connections read as of */
27+
protected array $snapshots = [];
28+
2529
function __construct() {
2630
$this->capsule = new Capsule;
2731
$dispatcher = new Dispatcher();
@@ -64,6 +68,26 @@ public function connect($params): void {
6468
$this->capsule->addConnection($config, $key);
6569
// The URL's session settings, on a connection just created.
6670
PostgresSessionOptions::apply($this->capsule->getConnection($key));
71+
72+
if ($this->driver === 'pgsql' && ($params->snapshot ?? true)) {
73+
$this->holdSnapshot($key, $config);
74+
}
75+
}
76+
}
77+
78+
/**
79+
* Read `$side` as of one instant: a second connection exports its
80+
* snapshot and keeps it open, and the one DBDiff reads with joins it —
81+
* see PostgresSnapshot.
82+
*/
83+
private function holdSnapshot(string $side, array $config): void {
84+
$anchor = $side . '__snapshot';
85+
$this->capsule->addConnection($config, $anchor);
86+
$connection = $this->capsule->getConnection($anchor);
87+
PostgresSessionOptions::apply($connection);
88+
$id = PostgresSnapshot::export($connection, $side);
89+
if ($id !== null && PostgresSnapshot::join($this->getDB($side), $id, $side)) {
90+
$this->snapshots[$side] = $id;
6791
}
6892
}
6993

@@ -87,6 +111,11 @@ public function useSchema(string $schema): void {
87111
$this->capsule->getDatabaseManager()->purge($name);
88112
$this->capsule->addConnection($config, $name);
89113
PostgresSessionOptions::apply($this->capsule->getConnection($name));
114+
// The new connection reads as of the same instant as the last.
115+
if (isset($this->snapshots[$name])
116+
&& !PostgresSnapshot::join($this->getDB($name), $this->snapshots[$name], $name)) {
117+
unset($this->snapshots[$name]);
118+
}
90119
}
91120
}
92121

@@ -124,6 +153,11 @@ public function getAdapter(): DBAdapterInterface {
124153
return $this->adapter;
125154
}
126155

156+
/** Whether `$side` is read as of one snapshot. */
157+
public function readsSnapshot(string $side): bool {
158+
return isset($this->snapshots[$side]);
159+
}
160+
127161
public function getDriver(): string {
128162
return $this->driver;
129163
}

‎src/DB/Data/KeyOrder.php‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
<?php namespace DBDiff\DB\Data;
22

33
use DBDiff\Logger;
4+
use DBDiff\DB\Support\PostgresSnapshot;
45
use Illuminate\Database\Connection;
56

67
/**
@@ -68,7 +69,7 @@ private function kinds(string $table, string $qualified): array
6869
{
6970
try {
7071
return match ($this->driver) {
71-
'pgsql' => $this->pgsqlKinds($qualified),
72+
'pgsql' => PostgresSnapshot::attempt($this->source, fn() => $this->pgsqlKinds($qualified)),
7273
'mysql' => $this->mysqlKinds($table),
7374
default => $this->sqliteKinds($table),
7475
};

‎src/DB/Data/KeylessMergeDiff.php‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55
use DBDiff\Logger;
66
use Diff\DiffOp\DiffOpAdd;
77
use Diff\DiffOp\DiffOpRemove;
8+
use DBDiff\DB\Support\PostgresSnapshot;
89
use Illuminate\Database\Connection;
910

1011
/**
@@ -85,7 +86,7 @@ private function textMatchColumns(string $table, array $columns): array
8586
{
8687
try {
8788
if ($this->driver === 'pgsql') {
88-
$rows = $this->source->select(
89+
$rows = PostgresSnapshot::attempt($this->source, fn() => $this->source->select(
8990
"SELECT a.attname AS name
9091
FROM pg_attribute a
9192
JOIN pg_type d ON d.oid = a.atttypid
@@ -94,7 +95,7 @@ private function textMatchColumns(string $table, array $columns): array
9495
AND NOT EXISTS (SELECT 1 FROM pg_operator o
9596
WHERE o.oprname = '=' AND o.oprleft = t.oid AND o.oprright = t.oid)",
9697
[$this->tableName($table)]
97-
);
98+
));
9899
} elseif ($this->driver === 'mysql') {
99100
$rows = $this->source->select(
100101
"SELECT COLUMN_NAME AS name FROM information_schema.COLUMNS

‎src/DB/Data/RowEstimate.php‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,6 @@
11
<?php namespace DBDiff\DB\Data;
22

3+
use DBDiff\DB\Support\PostgresSnapshot;
34
use Illuminate\Database\Connection;
45

56
/**
@@ -40,12 +41,12 @@ public static function of(Connection $conn, string $table): ?float
4041
private static function stats(Connection $conn, string $table): ?array
4142
{
4243
try {
43-
$row = $conn->selectOne(
44+
$row = PostgresSnapshot::attempt($conn, fn() => $conn->selectOne(
4445
"SELECT c.relkind AS kind, c.reltuples::float8 AS tuples, c.relpages AS pages,
4546
pg_relation_size(c.oid) / current_setting('block_size')::int AS current_pages
4647
FROM pg_class c WHERE c.oid = to_regclass(?)",
4748
[$table]
48-
);
49+
));
4950
} catch (\Throwable) {
5051
return null;
5152
}

‎src/DB/Data/RowStream.php‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -60,7 +60,7 @@ public function __construct(PDO $pdo, string $driver, string $sql, string $name,
6060
$this->ownsTransaction = true;
6161
}
6262
$this->cursor = 'dbdiff_' . preg_replace('/\W/', '_', $name);
63-
$pdo->exec("DECLARE {$this->cursor} NO SCROLL CURSOR FOR {$sql}");
63+
$this->declare($pdo, "DECLARE {$this->cursor} NO SCROLL CURSOR FOR {$sql}");
6464
$this->fetch = $pdo->prepare("FETCH FORWARD " . self::BATCH . " FROM {$this->cursor}");
6565
return;
6666
}
@@ -73,6 +73,29 @@ public function __construct(PDO $pdo, string $driver, string $sql, string $name,
7373
$this->statement->execute();
7474
}
7575

76+
/**
77+
* Declare the cursor. In a transaction this stream did not open — the
78+
* snapshot a diff reads as of — a failure would end that transaction for
79+
* every read after it, so it is undone to a savepoint instead.
80+
*/
81+
private function declare(PDO $pdo, string $declare): void
82+
{
83+
if ($this->ownsTransaction) {
84+
$pdo->exec($declare);
85+
return;
86+
}
87+
$pdo->exec('SAVEPOINT dbdiff_declare');
88+
try {
89+
$pdo->exec($declare);
90+
} catch (\Throwable $e) {
91+
$this->cursor = null;
92+
$pdo->exec('ROLLBACK TO SAVEPOINT dbdiff_declare');
93+
$pdo->exec('RELEASE SAVEPOINT dbdiff_declare');
94+
throw $e;
95+
}
96+
$pdo->exec('RELEASE SAVEPOINT dbdiff_declare');
97+
}
98+
7699
/** The next row, or false when there are no more. */
77100
public function fetch(): array|false
78101
{

‎src/DB/Support/PostgresExpressionEquivalence.php‎

Lines changed: 29 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -135,21 +135,20 @@ private static function renderViewBodies(Connection $connection, array $bodies):
135135
. "EXCEPTION WHEN others THEN NULL; END;";
136136
}
137137
$out = array_fill_keys($bodies, null);
138-
$pdo->beginTransaction();
139-
try {
140-
$pdo->exec("CREATE TEMP TABLE dbdiff_canon_out (i int, def text);\n"
141-
. "DO \$dbdiff_canon\$ BEGIN\n" . implode("\n", $blocks) . "\nEND \$dbdiff_canon\$;");
142-
$list = array_values($bodies);
143-
foreach ($connection->select('SELECT i, def FROM pg_temp.dbdiff_canon_out') as $row) {
144-
$out[$list[(int) $row['i']]] = $row['def'];
138+
return PostgresSnapshot::scratch($pdo, function () use ($pdo, $connection, $blocks, $bodies, $out): array {
139+
try {
140+
$pdo->exec("CREATE TEMP TABLE dbdiff_canon_out (i int, def text);\n"
141+
. "DO \$dbdiff_canon\$ BEGIN\n" . implode("\n", $blocks) . "\nEND \$dbdiff_canon\$;");
142+
$list = array_values($bodies);
143+
foreach ($connection->select('SELECT i, def FROM pg_temp.dbdiff_canon_out') as $row) {
144+
$out[$list[(int) $row['i']]] = $row['def'];
145+
}
146+
} catch (\Throwable $e) {
147+
// Rendered one at a time instead, as before.
148+
return [];
145149
}
146-
} catch (\Throwable $e) {
147-
// Rendered one at a time instead, as before.
148-
return [];
149-
} finally {
150-
$pdo->rollBack();
151-
}
152-
return $out;
150+
return $out;
151+
});
153152
}
154153

155154
/**
@@ -240,24 +239,22 @@ public static function canonical(Connection $connection, string $kind, ?string $
240239
&& array_key_exists($parts[1], self::$rendered[spl_object_id($connection)] ?? [])) {
241240
return self::view($connection, $definition);
242241
}
243-
$pdo = $connection->getPdo();
244-
$pdo->beginTransaction();
245-
try {
246-
return match ($kind) {
247-
'check' => self::check($connection, $table, $definition),
248-
'index' => self::index($connection, $table, $definition),
249-
'policy' => self::policy($connection, $table, $definition),
250-
'trigger' => self::trigger($connection, $table, $definition),
251-
'view' => self::view($connection, $definition),
252-
'domain' => self::domain($connection, $table, $definition),
253-
'generated' => self::generated($connection, $table, $definition),
254-
default => null,
255-
};
256-
} catch (\Throwable $e) {
257-
return null;
258-
} finally {
259-
$pdo->rollBack();
260-
}
242+
return PostgresSnapshot::scratch($connection->getPdo(), function () use ($connection, $kind, $table, $definition): ?string {
243+
try {
244+
return match ($kind) {
245+
'check' => self::check($connection, $table, $definition),
246+
'index' => self::index($connection, $table, $definition),
247+
'policy' => self::policy($connection, $table, $definition),
248+
'trigger' => self::trigger($connection, $table, $definition),
249+
'view' => self::view($connection, $definition),
250+
'domain' => self::domain($connection, $table, $definition),
251+
'generated' => self::generated($connection, $table, $definition),
252+
default => null,
253+
};
254+
} catch (\Throwable $e) {
255+
return null;
256+
}
257+
});
261258
}
262259

263260
/** A temporary table with the same columns, to attach things to. */

‎src/DB/Support/PostgresRenderers.php‎

Lines changed: 8 additions & 9 deletions

0 commit comments

Comments
 (0)