psycodict.dbdiff

Compare the search tables of two psycodict databases.

The LMFDB runs the same schema in several places (beta, production, private development copies), and the copies drift: a table is created in one place and not the other, a column is added or retyped on beta only, data is pushed to one server and not the other. The functions here detect that drift; see LMFDB/lmfdb#5900 for the motivating incident, where mf_newspaces ended up with different schemas on beta and production.

The entry points are the compare and show_differences methods of PostgresDatabase, which delegate to compare_databases() and format_differences() here. Everything runs read-only on both sides: only SELECT statements are issued, and unlike table.count() (which may cache the counts it computes when count saving is enabled), nothing is ever recorded in the counts tables or in meta_tables of either database, so it is safe to point at a production server.

Comparisons are based on fresh queries rather than on the schema cached on the PostgresDatabase objects at connection time, so long-lived connections do not report stale differences.

psycodict.dbdiff.compare_databases(self_db, other_db, tables=None, row_counts=True, null_counts=False, exact=False)[source]

Compute the differences between the search tables of two databases.

This is the implementation of PostgresDatabase.compare; see the docstring there for the interface, options and cost caveats.

OUTPUT:

A dictionary with keys

  • only_in_self, only_in_other – sorted lists of the names of search tables (rows of meta_tables) present in only one database

  • schema – a dictionary indexed by the names of tables present in both databases whose schemas differ. Each value is a dictionary containing whichever of the following are nonempty:

    • only_in_self, only_in_other – sorted lists of pairs (col, type) for columns present on one side only

    • type_changed – a sorted list of triples (col, type_self, type_other) for columns whose postgres types disagree. Types are rendered in full, including any modifiers, so numeric(10,2) vs numeric(20,4) is a difference

    • meta_changed – a list of triples (item, value_self, value_other) for the meta_tables settings in _meta_compared (label_col, sort, id_ordered, include_nones, count_cutoff) that disagree

  • row_counts (if requested) – a dictionary {table: (rows_self, rows_other)}, restricted to the tables where the counts differ

  • null_counts (if requested) – a dictionary {table: {col: (nulls_self, nulls_other)}} over the columns shared by both sides (id excluded), restricted to the columns where the counts differ

psycodict.dbdiff.format_differences(diff, self_db=None, other_db=None)[source]

Render the output of compare_databases() as a readable report.

INPUT:

  • diff – a dictionary as returned by compare_databases()

  • self_db, other_db – optionally, the compared PostgresDatabase instances, used to label the two sides with their connection details rather than just “self” and “other”

OUTPUT:

A string; "No differences found" when every section is empty. Sections without differences are omitted.