IF Commons GitHub

SQL dialects: PostgreSQL and DuckDB layouts

0010Active

0010-sql-dialects · owner if · domain commons

Requirements

FR-001checkpassing

On each of SQLite, PostgreSQL (PGlite and a real server) and DuckDB the store MUST pass every case of the suite its capabilities name and the merge suite of AnyFS.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/dialects.test.ts

Proved by: anyfs-sql/suite/dialects.test.ts

Tests: passed: suite/dialects.test.ts on duckdb the store passes every case of the suite its capabilities name, and the merge su; passed: suite/dialects.test.ts on postgres the store passes every case of the suite its capabilities name, and the merge ; passed: suite/dialects.test.ts on sqlite the store passes every case of the suite its capabilities name, and the merge su

FR-002checkpassing

Search MUST be offered where the engine has full-text search (SQLite FTS5, PostgreSQL tsvector with an inverted index), the caller MUST NOT be able to write an operator of the engine's query language, and an engine with no search (DuckDB) MUST say so and offer none.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/dialects.test.ts

Proved by: anyfs-sql/suite/dialects.test.ts

Tests: passed: suite/dialects.test.ts the search of PostgreSQL finds the words of a text and no operator can be written by the c

FR-003checkpassing

Replicas of SQLite, PostgreSQL and DuckDB that edit the same paths MUST exchange rows through a medium and merge pairwise, and all MUST end with the same files and the same siblings.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/dialects.test.ts

Proved by: anyfs-sql/suite/dialects.test.ts

Tests: passed: suite/dialects.test.ts replicas of SQLite, PostgreSQL and DuckDB that edit the same paths exchange rows through a

FR-004checkpassing

The dumps the library writes for PostgreSQL (INSERT ... ON CONFLICT DO NOTHING) and for DuckDB (INSERT OR IGNORE), replayed in any order, twice and overlapping, MUST give the union of the rows, the same files as the merge by deltas.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/dialects.test.ts

Proved by: anyfs-sql/suite/dialects.test.ts

Tests: passed: suite/dialects.test.ts the dumps the library writes for PostgreSQL and for DuckDB, replayed in any order, twice a

FR-005checkpassing

A dump MUST be written again for another engine from its text (redump), and the dump of the real sqlite3 written for PostgreSQL or DuckDB and the dump the library writes for each MUST give the same files in the other engine.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/dialects.test.ts

Proved by: anyfs-sql/suite/dialects.test.ts

Tests: passed: suite/dialects.test.ts the dump of the real sqlite3 written for PostgreSQL and for DuckDB, and the dump the libra

FR-006checkpassing

A DuckDB database has no trigger: the import of the library MUST hold what a trigger holds elsewhere, so that a replica the database does not trust refuses the whole replay and a row an epoch collected is not written; what a raw import into DuckDB cannot be made to hold MUST be said.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/dialects.test.ts

Proved by: anyfs-sql/suite/dialects.test.ts

Tests: passed: suite/dialects.test.ts a DuckDB database has no trigger, and the import of the library holds what a trigger holds

FR-007checkpassing

On a real PostgreSQL server (made for the run, or named by ANYFS_SQL_TEST_PG) the store MUST pass the suites too, writers on pooled connections MUST be serialised so that a check and its write are one step, and where no server can be made the test MUST be skipped with a sentence that says what is missing.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/dialects.test.ts

Proved by: anyfs-sql/suite/dialects.test.ts

Written in: anyfs-sql/tools/postgres-test.ts

Tests: passed: suite/dialects.test.ts on a REAL PostgreSQL server the store passes the laws of AnyFS and the merge suite (skippe

FR-008checkpassing

The dumps of the real pg_dump (--data-only --inserts --on-conflict-do-nothing) of several real databases, replayed by the real psql and by the library in any order, twice and overlapping, MUST be the union of the rows, and a dump of the real sqlite3 written for PostgreSQL and replayed by psql MUST give the same files.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/dialects.test.ts

Proved by: anyfs-sql/suite/dialects.test.ts

Tests: passed: suite/dialects.test.ts a dump of the real sqlite3 written for PostgreSQL and replayed by the real psql gives the ; passed: suite/dialects.test.ts the dumps of the real pg_dump (data only, inserts, on conflict do nothing) of several real

Acceptance scenarios

  1. Given three real PostgreSQL databases that each took different rows, When their pg_dump outputs are replayed by psql into a fourth in any order, Then it holds the union, per FR-008.
  2. Given replicas on SQLite, PostgreSQL and DuckDB with edits to one path, When they merge pairwise, Then all show the same winner and the same siblings, per FR-003.

Out of scope

  • Search on DuckDB, and the enforcement by the database itself of what a trigger holds on DuckDB.

Edge cases

  • A schema the connection does not start in: the functions of PostgreSQL name their schema, so that a dump replayed with an empty search path still finds its tables (FR-008).

Assumptions

  • PostgreSQL 16 client and server programs are on the computer where the real tests run; PGlite and DuckDB are libraries.

Open questions

  • None.

Key entities

  • Dialect - the way an engine states the layout: tables, views, triggers or their absence, bytes, search.
  • Redump - a dump read and written again for another engine.

Success criteria

SC-001success criterion

A fleet that keeps a database in each of three engines can be brought to the same files by dumps alone, and a real pg_dump is a valid input.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No implementation language beyond what the requirement needs
  • [x] Out of scope is stated

Titles, headings, exports, tables, columns and requirements. Esc closes.