psycodict.database

The connection object at the center of psycodict.

PostgresDatabase connects using a Configuration and exposes each table in the database as an attribute (db.nf_fields), a PostgresSearchTable. It also provides the database-wide operations: creating, dropping and renaming tables, reloading or cleaning up every table at once, schema-change notifications (listener()), refreshing table metadata without a restart (refresh_tables()), and the metadata format stamp and its migrations.

class psycodict.database.NumericLoader(oid: int, context: AdaptContext | None = None)

Bases: Loader

Loads Postgres numeric values through numeric_converter() (Sage Integer/RealLiteral when Sage is available, int/float otherwise).

load(data)

Convert the numeric’s text representation to a Python/Sage number.

psycodict.database.setup_connection(conn)

Prepare a fresh psycopg connection for psycodict: set the client encoding and register the loaders and dumpers that implement psycodict’s value conversion (numerics, json, arrays, and – when Sage is available – Sage integers and reals). Called for every connection the database opens.

class psycodict.database.PostgresDatabase(config=None, secretsfile=None, create=False, upgrade=False, **kwargs)

Bases: PostgresBase

The interface to the postgres database.

It creates and stores the global connection object, and collects the table interfaces.

A single psycopg connection is shared by this database object and every table interface registered on it (see register_object and reset_connection). The psycopg connection itself is thread-safe – it serializes concurrent cursor use with an internal lock – but sharing one connection means sharing one transaction and session state, and psycodict layers unsynchronized mutable bookkeeping on top (the commit-deferral stack behind DelayCommit, the server-side cursor counter, the per-object connection references reset together). So a PostgresDatabase instance is not thread-safe; use one instance per process or thread (this is how LMFDB deploys it).

INPUT:

• create – if True, create psycodict’s metadata tables (meta_tables, meta_indexes, meta_constraints and their _hist counterparts) when they are missing, allowing use of a fresh database

• upgrade – if True, migrate the metadata tables to the format this psycodict implements before connecting (see upgrade_metadata and MetadataFormats.md). Without it, a database using an older but compatible metadata format connects with a warning and operates at the older format.

• **kwargs – passed on to psycopg’s connect method

ATTRIBUTES:

The following public attributes are stored on the db object.

• server_side_counter – an integer tracking how many buffered connections have been created

• conn – the psycopg connection object

• tablenames – a list of tablenames in the database, as strings

• meta_format – the metadata format this connection operates at (see MetadataFormats.md)

Also, each tablename will be stored as an attribute, so that db.ec_curvedata works for example.

These table objects are snapshots: if another process later changes the schema (adding or dropping columns or tables), call refresh_tables to update them in place instead of restarting the process.

EXAMPLES:

>>> db
Interface to Postgres database
>>> db.conn
<psycopg.Connection [IDLE] (host=... database=...) at 0x...>
>>> 'test_fields' in db.tablenames
True
>>> db.test_fields
Interface to Postgres table test_fields

reset_connection()

Resets the connection

refresh_tables()

Update the table objects to match the current state of the database.

The set of tables, and each table’s columns, types, sort order and other metadata, are read from the database when this object is created. A long-running process (such as a website) therefore does not see schema changes made from other processes: after a column is dropped, for example, its queries still mention the column and fail, while a newly added column is silently invisible. Rather than restarting every such process, call this method to bring the process up to date – on a schedule, say, or upon catching an errors.UndefinedColumn.

Existing table objects are updated in place, so table references held by application code (nf = db.nf_fields) remain valid. Tables created since the last refresh become accessible as attributes and are added to tablenames; tables that have been dropped are removed. A reference held to a dropped table’s object will raise an error on its next query, as it must, since the underlying table no longer exists.

grant_select(table_name, users=['lmfdb', 'webserver'])

Grant users the ability to run SELECT statements on a given table

INPUT:

• table_name – a string, the name of the table

• users – a list of users to grant this permission

grant_insert(table_name, users=['webserver'])

Grant users the ability to run INSERT statements on a given table

INPUT:

• table_name – a string, the name of the table

• users – a list of users to grant this permission

grant_update(table_name, users=['webserver'])

Grant users the ability to run UPDATE statements on a given table

INPUT:

• table_name – a string, the name of the table

• users – a list of users to grant this permission

grant_delete(table_name, users=['webserver'])

Grant users the ability to run DELETE statements on a given table

INPUT:

• table_name – a string, the name of the table

• users – a list of users to grant this permission

table_sizes()

Returns a dictionary containing information on the sizes of the search tables.

OUTPUT:

A dictionary with a row for each search table (as well as a few others such as kwl_knowls), with entries

• nrows – an estimate for the number of rows in the table

• nstats – an estimate for the number of rows in the stats table

• ncounts – an estimate for the number of rows in the counts table

• total_bytes – the total number of bytes used by the main table, as well as stats, counts, indexes, ancillary storage….

• index_bytes – the number of bytes used for indexes on the main table

• toast_bytes – the number of bytes used for storage of variable length data types, such as strings and jsonb

• table_bytes – the number of bytes used for fixed length storage on the main table

• counts_bytes – the number of bytes used by the counts table

• stats_bytes – the number of bytes used by the stats table

property meta_format

The metadata format this connection operates at.

This is the format stamped in the database, capped at the format this psycodict implements (META_FORMAT). When it is lower than META_FORMAT, features introduced by newer formats are unavailable (their methods raise with instructions) until the database is migrated with upgrade_metadata(); see MetadataFormats.md.

upgrade_metadata()

Migrate this database’s metadata tables up to the format that this version of psycodict implements (META_FORMAT), applying each registered migration in order and stamping the format as it goes.

Connecting to an older-format database only warns (when the formats are compatible; see MetadataFormats.md), so migrating is a deliberate act – this method, or PostgresDatabase(config=..., upgrade=True), which bootstraps (when create=True), migrates, and then connects, all in one call.

Migrations only move forward. A database already at the current format is left untouched, so calling this is idempotent; a database whose format is newer than this psycodict is an error (upgrade psycodict instead), as is a meta_format table that exists but is empty (its format is unknown, so it cannot be migrated blindly).

create_table_like(new_name, table, tablespace=None, data=False, indexes=False)

Creates a new table with the same schema as an existing one, including each column’s storage and compression settings. By default neither data, indexes nor stats are copied.

INPUT:

• new_name – a string giving the desired table name.

• table – a string or PostgresSearchTable object giving an existing table.

• tablespace – the tablespace for the new table

• data – whether to copy over data from the source table

• indexes – whether to copy over indexes from the source table

create_table(name, search_columns, label_col, table_description=None, col_description=None, sort=None, id_ordered=None, tablespace=None, force_description=False, id_type='bigint', include_nones=True)

Add a new search table to the database. See also create_table_like().

INPUT:

• name – the name of the table, which must include an underscore. See existing names for consistency.

• search_columns – either a dictionary whose keys are valid postgres types and whose values

  are lists of column names (or just a string if only one column has the specified type); or a list of pairs (col, type). An id column of type id_type will be added as a primary key if not present.

• label_col – the column holding the LMFDB label. This will be used in the lookup method

  and in the display of results on the API. Use None if there is no appropriate column.

• table_description – a text description of this table

• col_description – a dictionary giving descriptions for the columns

• sort – If not None, provides a default sort order for the table, in formats accepted by

  the _sort_str method.

• id_ordered – boolean (default None). If set, the table will be sorted by id when

  pushed to production, speeding up some kinds of search queries. Defaults to True when sort is not None.

• tablespace – (optional) a postgres tablespace to use for the new table

• force_description – whether to require descriptions

• id_type – what postgres type to use for the id column

• include_nones – whether search results should include columns whose value is None (default True). Pass False to omit None values from result dictionaries, as was the default before psycodict 1.0. The value is stored explicitly in meta_tables, so the flipped default reaches databases created before it without any migration.

COMMON TYPES:

The postgres types most commonly used are:

• smallint – a 2-byte signed integer.

• integer – a 4-byte signed integer.

• bigint – an 8-byte signed integer.

• numeric – exact, high precision integer or decimal.

• real – a 4-byte float.

• double precision – an 8-byte float.

• text – string (see collation note above).

• boolean – true or false.

• jsonb – data iteratively built from numerics, strings, booleans, nulls, lists and dictionaries.

• timestamp – 8-byte date and time with no timezone.

drop_table(name, force=False)

Drop a table.

INPUT:

• name – the name of the table

• force – refrain from asking for confirmation

NOTE:

You cannot drop a table that has been marked important. You must first set it as not important if you want to drop it.

rename_table(old_name, new_name)

Rename a table.

INPUT:

• old_name – the current name of the table, as a string

• new_name – the new name of the table, as a string

copy_to(search_tables, data_folder, fail_on_error=True, **kwds)

Copy a set of search tables to a folder on the disk.

INPUT:

• search_tables – a list of strings giving names of tables to copy

• data_folder – a path to a folder to save the data. The folder must not currently exist.

• **kwds – other arguments are passed on to the copy_to method of each table.

copy_to_from_remote(search_tables, data_folder, remote_opts=None, fail_on_error=True, **kwds)

Copy data to a folder from a postgres instance on another server.

INPUT:

• search_tables – a list of strings giving names of tables to copy

• data_folder – a path to a folder to save the data. The folder must not currently exist.

• remote_opts – options for the remote connection (passed on to psycopg’s connect method)

• **kwds – other arguments are passed on to the copy_to method of each table.

reload_all(data_folder, halt_on_errors=True, resort=None, restat=None, adjust_schema=False, sequential_swap=False, **kwds)

Reloads all tables from files in a given folder. The filenames must match the names of the tables, with _counts and _stats appended as appropriate.

INPUT:

• data_folder – the folder that contains files to be reloaded

• halt_on_errors – whether to stop if a DatabaseError is encountered while trying to reload one of the tables

• sequential_swap – if True, then the whole transaction will not be wrapped in a DelayCommit, which can sometimes prevent deadlocks

• resort, restat, adjust_schema, and any extra keywords are passed on to the reload method of each PostgresTable

Note that this function currently does not reload data that is not in a search table, such as knowls or user data.

reload_all_revert(data_folder)

Reverts the most recent reload_all by swapping with the backup table for each search table modified.

INPUT:

• data_folder – the folder used in reload_all;

  determines which tables were modified.

cleanup_all()

Drops all _tmp and _old tables created by the reload() method.

show_queries()

Prints the queries currently running in this database (which may be holding the locks shown by show_locks; see show_blocked for statements that are stuck behind them).

show_blocked()

Prints the statements that are waiting on locks held by other sessions, together with the sessions holding them.

show_locks()

Prints information on all locks currently held on any table.

tablespaces()

Returns a dictionary giving giving the tablespace for all tables

compare(other, tables=None, row_counts=True, null_counts=False, exact=False)

Returns the differences between the search tables of this database and those of another one, such as a beta and a production server.

The comparison is read-only on both sides: only SELECT statements are issued, and (unlike the count method of a table, which may cache its results when count saving is enabled) nothing is recorded in the counts tables or meta_tables of either database, so other may safely be a production server.

INPUT:

• other – another PostgresDatabase instance; you connect to the second server yourself (see the examples below)

• tables – a table name or list of table names (default None, meaning every search table of either database); restricts every part of the comparison to those tables

• row_counts – boolean (default True). Whether to compare the number of rows in tables present in both databases

• null_counts – boolean (default False). Whether to compare the number of NULL entries in each column shared by both sides. This requires a full scan of each compared table in each database, which can take minutes for the largest LMFDB tables, so consider restricting tables

• exact – boolean (default False). By default row counts are read from the total cached in meta_tables, which psycodict’s write paths maintain and count() reports; that is a single cheap query per database, but it can be stale if a table was modified from outside psycodict or its statistics were never refreshed. Set to True to run SELECT COUNT(*) on each compared table instead, which is exact but slow on big tables (the counting scans run with whatever statement timeout each connection has)

OUTPUT:

A dictionary with keys

• only_in_self, only_in_other – sorted lists of the names of search tables present in only one of the databases

• schema – a dictionary indexed by the tables present in both databases whose schemas differ, with values dictionaries containing whichever of the following differences occur: only_in_self and only_in_other (lists of pairs (col, type) of columns present on one side only), type_changed (a list of triples (col, type_self, type_other), with types rendered in full so that e.g. numeric(10,2) vs numeric(20,4) is reported) and meta_changed (a list of triples (item, value_self, value_other) recording disagreements in the label_col, sort, id_ordered, include_nones or count_cutoff settings from meta_tables)

• row_counts (if requested) – {table: (rows_self, rows_other)}, only for the tables where the counts differ

• null_counts (if requested) – {table: {col: (nulls_self, nulls_other)}}, only for the columns where the counts differ

EXAMPLES:

Comparing with a server described by a second configuration file:

>>> from psycodict.config import Configuration
>>> from psycodict.database import PostgresDatabase
>>> prod_config = Configuration(defaults={"config_file": "prod-config.ini"}, readargs=False)
>>> prod = PostgresDatabase(config=prod_config)
>>> db.compare(prod)["only_in_self"]
['mf_newspaces_test']

Keyword arguments override the configuration, so a server that differs only in its host can reuse this database’s configuration:

>>> prod = PostgresDatabase(config=db.config, host="proddb.lmfdb.xyz")
>>> db.compare(prod, tables="mf_newspaces", null_counts=True)
{'only_in_self': [], 'only_in_other': [], 'schema': {}, 'row_counts': {}, 'null_counts': {}}

show_differences(other, tables=None, row_counts=True, null_counts=False, exact=False)

Prints a readable report of the differences between the search tables of this database and those of another one.

Sections without differences are omitted; if the databases agree, prints “No differences found”. See compare for the meaning of the arguments, the cost of the optional comparisons, and the guarantee that both databases are only read from.

show_slow_report(logfile, top=20, cutoff=None)

Prints an analysis of a slow-query log file: which query shapes take the most time, how much smaller the log would be with a higher slowcutoff, and which constrained columns lack a supporting index (checked against the indexes recorded in meta_indexes).

See psycodict.slowlog for the underlying functions, which can also be used without a database connection.

INPUT:

• logfile – the filename of a log written via the slowlogfile logging option

• top – the number of query shapes to show

• cutoff – only consider queries at least this slow, in seconds

notify(channel, payload='')

Send a PostgreSQL notification on channel with the given payload.

The notification is sent with pg_notify on the main connection, through _execute, so it is transactional: PostgreSQL delivers it when the surrounding transaction commits and drops it on rollback. Called on its own (outside a DelayCommit) it commits immediately and so is delivered at once; called inside a DelayCommit it rides that transaction and is delivered (or dropped) with it.

INPUT:

• channel – the channel name; must be a plain identifier (letters, digits and underscores, not starting with a digit)

• payload – a string payload (default ""); received verbatim by listeners

Subscribe with listener() (or the standalone NotificationListener).

listener(channels=None)

Return a NotificationListener.

The listener opens its own autocommit connection from this database’s configuration and LISTEN``s on ``channels (default: the schema channel "psycodict_schema" alone). It is pull-based: call poll(timeout) for a bounded batch, or iterate listen(); use it as a context manager to close the connection when done.

The intended follow-up use is a long-running website process that keeps a listener on "psycodict_schema" and, whenever a table name arrives, refreshes that table’s cached metadata so newly created columns and reloaded tables become visible without a restart. That refresh mechanism is proposed in a separate PR; psycodict ships the notification plumbing here without depending on it, so the two can land in either order.

Under a pre-forking web server each worker must build its own listener after the fork, and a server in recovery (a hot standby) refuses LISTEN outright; see the Forking and Hot standbys sections of psycodict.notifications.

INPUT:

• channels – a channel name or iterable of them; None (default) means the schema channel only