psycodict.table

The write and schema half of a table.

PostgresTable manages a single search table: row-level writes (insert_many, update, upsert, delete), the bulk file-based import and export that psycodict is built around (copy_from, copy_to, reload and its staged _tmp-table machinery), and the table’s schema – columns, indexes, constraints and the corresponding meta_* bookkeeping with its versioned history. The read interface lives in the subclass PostgresSearchTable, which is what db.<tablename> actually returns.

class psycodict.table.PostgresTable(db, search_table, label_col, sort=None, count_cutoff=1000, id_ordered=False, out_of_order=False, stats_valid=True, total=None, include_nones=True, data_types=None)

Bases: PostgresBase

This class is used to abstract a table in the LMFDB database on which searches are performed.

INPUT:

• db – an instance of PostgresDatabase

• search_table – a string, the name of the table in postgres.

• label_col – the column holding the LMFDB label, or None if no such column exists.

• sort – a list giving the default sort order on the table, or None. If None, sorts that can return more than one result must explicitly specify a sort order. Note that the id column is sometimes used for sorting; see the search method for more details.

• count_cutoff – an integer parameter (default 1000) which determines the threshold at which searches will no longer report the exact number of results.

• id_ordered – a boolean, whether the ids of the rows are in sort order.

  Used for improving search performance

• out_of_order – if the rows are supposed to be ordered by ID, this boolean value records

  that they are currently out of order due to insertions or updates.

• stats_valid – whether the statistics tables are currently up to date

• total – the total number of rows in the table; cached as a performance optimization

• data_types – a dictionary holding the data types of the columns; see the _column_types method for more details

ATTRIBUTES:

The following public attributes are available on instances of this class

• search_table – a string, the name of the associated postgres search table

• search_cols – a list of column names in the search table. Does not include the id column.

• col_type – a dictionary with keys the column names and values the postgres type of that column.

• stats – the attached PostgresStatsTable instance

The following private attributes are sometimes also useful

• _label_col – the column used by default in the lookup method

• _sort_org – either None or a list of columns or pairs (col, direction)

• _sort_keys – a set of column names included in the sort order

• _primary_sort – either None, a column name or a pair (col, direction), the most significant column when sorting

• _sort – the psycopg.sql.Composable object containing the default sort clause

analyze(query, projection=1, limit=1000, offset=0, sort=None, explain_only=False, join=None)

Prints an analysis of how a given query is being executed, for use in optimizing searches.

INPUT:

• query – a query dictionary

• projection – outputs, as in the search method

• limit – a maximum on the number of rows to return

• offset – an offset starting point for results

• sort – a string or list specifying a sort order

• explain_only – whether to execute the query (if True then will only use Postgres’ query planner rather than actually carrying out the query)

• join – a list of tuples describing other search tables to join to this one, as for search; the query, projection and sort may then use qualified columns. Slow joined searches log a replication command that includes this argument.

EXAMPLES:

>>> nf = db.test_fields
>>> nf.analyze({'degree': 2}, limit=4)
SELECT "class_group", "class_number", "degree", "disc_abs", "disc_sign", "label", "r2", "ramps" FROM "test_fields" WHERE "degree" = 2 ORDER BY "degree", "disc_abs", "label" LIMIT 4
Limit  (cost=... rows=4... loops=1)
...
Execution Time: ... ms

list_indexes(verbose=False)

Lists the indexes on the search table present in meta_indexes

INPUT:

• verbose – if True, prints the indexes; if False, returns a dictionary

OUTPUT:

• If not verbose, returns a dictionary with keys the index names and values a dictionary containing the type, columns and modifiers.

NOTE:

• not necessarily all built

• not necessarily a superset of all the built indexes

For the current built indexes on the search table, see _list_built_indexes

create_index(columns, type='btree', modifiers=None, name=None, storage_params=None, where=None)

Create an index.

This function will also add the indexing data to the meta_indexes table so that indexes can be dropped and recreated when uploading data.

INPUT:

• columns – a list of column names

• type – one of the postgres index types: btree, gin, gist, brin, hash, spgist.

• modifiers – a list of lists of strings. The overall length should be

  the same as the length of columns, and each internal list can only contain the following whitelisted column modifiers: - a non-default operator class - ASC - DESC - NULLS FIRST - NULLS LAST This interface doesn’t currently support creating indexes with nonstandard collations.

• where – if given, a string with the predicate of a partial index, emitted

  verbatim as the WHERE clause of CREATE INDEX (e.g. "disc > 0"). Unlike a search query, this is raw SQL: it is not parsed, escaped or translated, and is inlined into the DDL like storage_params. It is administrative input (you are already trusted to run DDL), never website input, and must be trusted accordingly. The predicate is recorded in meta_indexes so the partial index survives a drop/restore or reload. A partial index needs a name distinct from any plain index on the same columns: pass an explicit name, or rely on the automatic numeric suffix appended below when the generated name collides with an existing relation.

drop_index(name, suffix='', permanent=True)

Drop a specified index.

INPUT:

• name – the name of the index

• suffix – a string such as “_tmp” or “_old1” to be appended to the names in the DROP INDEX statement.

• permanent – whether to remove the index from the meta_indexes table

restore_index(name, suffix='')

Restore a specified index using the meta_indexes table.

INPUT:

• name – the name of the index

• suffix – a string such as “_tmp” or “_old1” to be appended to the names in the CREATE INDEX statement.

drop_indexes(columns=[], suffix='', permanent=True)

Drop all indexes and constraints.

If columns provided, will instead only drop indexes and constraints that refer to any of those columns.

INPUT:

• columns – a list of column names. If any are included,

  then only indexes referencing those columns will be included.

• suffix – a string such as “_tmp” or “_old1” to be appended

  to the names in the drop statements.

restore_indexes(columns=[], suffix='')

Restore all indexes and constraints using the meta_indexes and meta_constraints tables.

If columns provided, will instead only restore indexes and constraints that refer to any of those columns.

INPUT:

• columns – a list of column names. If any are included,

  then only indexes/constraints referencing those columns will be included.

• suffix – a string such as “_tmp” or “_old1” to be appended

  to the names in the creation statements.

drop_pkeys(suffix='')

Drop the primary key on the id columns.

INPUT:

• suffix – a string such as “_tmp” or “_old1” to be appended to the names in the ALTER TABLE statements.

restore_pkeys(suffix='')

Restore the primary key on the id columns.

INPUT:

• suffix – a string such as “_tmp” or “_old1” to be appended to the names in the ALTER TABLE statements.

list_constraints(verbose=False)

Lists the constraints on the search table present in meta_constraints

INPUT:

• verbose – if True, prints the constraints; if False, returns a dictionary

OUTPUT:

• If not verbose, returns a dictionary with keys the index names and values a dictionary containing the type, columns and the check_func

NOTE:

• not necessarily all built

• not necessarily a superset of all the built constraints

For the current built constraints on the search table, see _list_built_constraints

create_constraint(columns, type, name=None, check_func=None)

Create a constraint.

This function will also add the constraint data to the meta_constraints table so that constraints can be dropped and recreated when uploading data.

INPUT:

• columns – a list of column names

• type – we currently support “unique”, “check”, “not null”

• name – the name of the constraint; generated if not provided

• check_func– a string, giving the name of a function

  that can take the columns as input and return a boolean output. It must be in the _valid_check_functions list above, in order to prevent SQL injection attacks

drop_constraint(name, suffix='', permanent=False)

Drop a specified constraint.

INPUT:

• name – the name of the constraint

• suffix – a string such as “_tmp” or “_old1” to be appended to the names in the statement.

• permanent – whether to remove the index from the meta_constraint table

restore_constraint(name, suffix='')

Restore a specified constraint using the meta_constraints table.

INPUT:

• name – the name of the constraint

• suffix – a string such as “_tmp” or “_old1” to be appended to the names in the ALTER TABLE statement.

copy_to_meta(filename, sep='|')

Export this table’s row of meta_tables to a file, in the format accepted by reload_meta().

copy_to_indexes(filename, sep='|')

Export this table’s index definitions (its rows of meta_indexes) to a file, in the format accepted by reload_indexes().

copy_to_constraints(filename, sep='|')

Export this table’s constraint definitions (its rows of meta_constraints) to a file, in the format accepted by reload_constraints().

reload_indexes(filename, sep='|')

Replace this table’s index definitions in meta_indexes with the contents of the file (as written by copy_to_indexes()). The definitions being replaced are archived in meta_indexes_hist, so revert_indexes() can undo this.

reload_meta(filename, sep='|')

Replace this table’s row of meta_tables with the contents of the file (as written by copy_to_meta()). The row being replaced is archived in meta_tables_hist, so revert_meta() can undo this.

reload_constraints(filename, sep='|')

Replace this table’s constraint definitions in meta_constraints with the contents of the file (as written by copy_to_constraints()). The definitions being replaced are archived in meta_constraints_hist, so revert_constraints() can undo this.

revert_indexes(version=None)

Restore an earlier version of this table’s index definitions from meta_indexes_hist.

INPUT:

• version – the version to restore (default: the one before the current version)

revert_constraints(version=None)

Restore an earlier version of this table’s constraint definitions from meta_constraints_hist.

INPUT:

• version – the version to restore (default: the one before the current version)

revert_meta(version=None)

Restore an earlier version of this table’s meta_tables row from meta_tables_hist.

INPUT:

• version – the version to restore (default: the one before the current version)

finalize_changes()

Intended to finish off a batch of data changes by updating the cached total, refreshing statistics targets, and re-sorting by id. Currently a placeholder that does nothing.

rewrite(func, query={}, *, resort=True, restat=True, tostr_func=None, datafile=None, progress_count=10000, **kwds)

This function can be used to edit some or all records in the table.

Note that if you want to add new columns, you must explicitly call add_column() first.

The modified records are written to a file and loaded into a brand-new table, which is then swapped in for the current one, so that the change can be undone with reload_revert and no locks are taken on a table that is being actively used. Since the replacement table is built from scratch, all of its indexes are always recreated; there is thus no reindex option, and asking for reindex=False raises an error (unless inplace=True is passed through to update_from_file, which edits rows on the live table instead). All arguments other than func and query must be passed by keyword.

INPUT:

• func – a function that takes a record (dictionary) as input and returns the modified record

• query – a query dictionary; only rows satisfying this query will be changed

• resort – whether to resort the table after running the rewrite

• restat – whether to recompute statistics after running the rewrite

• tostr_func – a function to be used when writing data to the temp file

  defaults to copy_dumps from encoding

• datafile – a filename to use for the temp file holding the data

• progress_count – (default 10000) how frequently to print out status reports as the rewrite proceeds

• **kwds – any other keyword arguments (such as inplace or sep) are passed on to the update_from_file method

EXAMPLES:

For example, to add a new column to test_fields holding the signed discriminant, you would do the following:

>>> nf = db.test_fields
>>> nf.add_column('disc', 'integer')
>>> def add_disc(rec):
...     rec['disc'] = rec['disc_sign'] * rec['disc_abs']
...     return rec
>>> nf.rewrite(add_disc)

update_from_file(datafile, label_col=None, *, inplace=False, resort=None, reindex=None, restat=True, logging={'operation': 'file_update'}, **kwds)

Updates this table from data stored in a file.

By default the updated rows are merged into a brand-new table, which is then swapped in for the current one, so that the change can be undone with reload_revert and no locks are taken on a table that is being actively used. Since the replacement table is built from scratch, all of its indexes are always recreated, whatever reindex says; with inplace=True the rows are instead edited on the live table and reindex controls how the indexes are handled. Arguments after label_col must be passed by keyword.

INPUT:

• datafile – a file with header lines (unlike reload, does not need to include all columns) and rows containing data to be updated.

• label_col – a column specifying which row(s) of the table should be updated corresponding to each row of the input file. This will usually be the label for the table, in which case it can be omitted.

• inplace – whether to do the update in place. If set, the operation cannot be undone with reload_revert.

• resort – whether this table should be resorted after updating (default is to resort when the sort columns intersect the updated columns)

• reindex – only meaningful when inplace is set: whether to drop the indexes touching the updated columns before the update and recreate them afterward, which is faster when many rows change (by default this is done when more than 1000 rows are updated). Without inplace, all indexes are necessarily recreated on the replacement table, so reindex=True is redundant and reindex=False raises an error.

• restat – whether to recompute stats for the table

• logging – a dictionary of keyword arguments for _log_db_change

• kwds – passed on to the COPY command. Cannot include “columns”.

delete(query, restat=True)

Delete all rows matching the query.

INPUT:

• query – a query dictionary; rows matching the query will be deleted

• restat – whether to recreate statistics afterward

update(query, changes, resort=False, restat=True)

Update a table using Postgres’ update command

INPUT:

• query – a query dictionary. Only rows matching the query will be updated

• changes – a dictionary. The keys should be column names, the values should be constants.

• resort – whether to resort the table afterward

• restat – whether to recompute statistics afterward

upsert(query, data)

Update the unique row satisfying the given query, or insert a new row if no such row exists. If more than one row exists, raises an error.

Upserting will often break the order constraint if the table is id_ordered, so you will probably want to call resort after all upserts are complete.

INPUT:

• query – a dictionary with key/value pairs specifying at most one row of the table. The most common case is that there is one key, which is either an id or a label.

• data – a dictionary containing key/value pairs to be set on this row.

The keys of both inputs must be columns of the table.

OUTPUT:

• new_row – whether a new row was inserted

• row_id – the id of the found/new row

insert_many(data, resort=False, reindex=None, restat=True)

Insert multiple rows.

This function will be faster than repeated upsert calls, but slower than copy_from

INPUT:

• data – a list of dictionaries, whose keys are columns and values the values to be set. All dictionaries must have the same set of keys.

• resort – whether to sort the ids after copying in the data. Only relevant for tables that are id_ordered.

• reindex – boolean (default True iff data has more than 1000 entries). Whether to drop the indexes before insertion and restore afterward. Note that if there is an exception during insertion the indexes will need to be restored manually using restore_indexes.

• restat – whether to refresh statistics after insertion

If the search table has an id, the dictionaries will be updated with the ids of the inserted records, though note that those ids will change if the ids are resorted.

resort(suffix='', sort=None)

Restores the sort order on the id column. The id sequence might have gaps after resorting. See: https://www.postgresql.org/docs/current/functions-sequence.html

INPUT:

• suffix – a string such as “_tmp” or “_old1” to be appended to the names in the command.

• sort – – a list, either of strings (which are interpreted as column names

  in the ascending direction) or of pairs (column name, 1 or -1). If None, will use self._sort_orig.

reload(searchfile, countsfile=None, statsfile=None, indexesfile=None, constraintsfile=None, metafile=None, resort=None, restat=None, final_swap=True, silence_meta=False, adjust_schema=False, **kwds)

Safely and efficiently replaces this table with the contents of one or more files.

The data is loaded into a brand-new table, which is then swapped in for the current one, so that the change can be undone with reload_revert and no locks are taken on a table that is being actively used. The primary key, indexes and constraints are always recreated on the new table, after the data is loaded (building them afterward is faster than maintaining them during the load). There is deliberately no reindex option: the replacement table starts without indexes, so they can only be rebuilt, never preserved.

INPUT:

• searchfile – a string, the file with data for the search table

• countsfile – a string (optional), giving a file containing counts

  information for the table.

• statsfile – a string (optional), giving a file containing stats

  information for the table.

• indexesfile – a string (optional), giving a file containing index

  information for the table.

• constraintsfile – a string (optional), giving a file containing constraint

  information for the table.

• metafile – a string (optional), giving a file containing the meta

  information for the table.

• resort – whether to sort the ids after copying in the data.

  Only relevant for tables that are id_ordered. Defaults to sorting when the searchfile does not contain ids.

• restat – whether to refresh statistics afterward. Default behavior

  is to refresh stats if either countsfile or statsfile is missing.

• final_swap – whether to perform the final swap exchanging the

  temporary table with the live one.

• silence_meta – suppress the warning message when using a metafile

• adjust_schema – If True, it will create the new tables using the

  header columns, otherwise expects the schema specified by the files to match the current one

• kwds – passed on to the COPY command. Cannot include “columns”.

reload_final_swap(tables=None, metafile=None, ordered=False, sep='|')

Renames the _tmp versions of tables to the live versions, and updates the corresponding meta_tables row if metafile is provided.

INPUT:

• tables – list of strings (optional), of the tables to be renamed. If None is provided, renames all the tables ending in _tmp

• metafile – a string (optional), giving a file containing the meta information for the table.

• sep – a character (default |) to separate columns

drop_tmp()

Drop the temporary tables used in reloading.

See the method cleanup_from_reload if you also want to drop the old backup tables.

reload_revert(backup_number=None)

Use this method to revert to an older version of a table.

Note that calling this method twice with the same input should return you to the original state.

INPUT:

• backup_number – the backup version to restore,

  or None for the most recent.

cleanup_from_reload(keep_old=0)

Drop the _tmp and _old* tables that are created during reload.

Note that doing so will prevent reload_revert from working.

INPUT:

• keep_old – the number of old tables to keep (they will be renamed so that they start at 1)

staged()

Returns a context manager for editing this table without ever modifying the live table in place.

On entering the context, the search table is copied (data, ids and primary key, but no other indexes) to a table with a _tmp suffix, and empty copies of the counts and stats tables are created alongside it. The object yielded is a genuine table object pointed at the copy, so the usual write methods (insert_many, update, upsert, delete, rewrite, …) and the search methods work on it, while reads on the live table proceed as if nothing had happened.

On exiting normally, the indexes and constraints recorded in meta_indexes and meta_constraints are built on the copy and it is swapped into place using the same renaming choreography as reload: the previous version is kept with an _old<n> suffix, so reload_revert undoes the swap and cleanup_from_reload drops the backups. The statistics are invalidated rather than recomputed: the swapped-in counts and stats tables are empty and stats_valid is set to false in meta_tables.

On exiting with an exception, the copies are dropped and the live table is left exactly as it was.

As with reload, a successful swap replaces the table object held by the database, so get a fresh reference afterward (db[name]) rather than continuing to use an old one; the staged handle is also dead once the context exits. Only one staged context (or reload) at a time can be active on a table; a second one raises on entry. Schema changes through the staged handle are disabled. If the swap itself fails, the staged tables are left in place so that no work is lost; drop_tmp discards them and staged_force_swap adopts them, discarding whatever concurrent changes made the swap refuse.

While the context is open, writes to the live table through psycodict’s API (insert_many, update, delete, …, from this or any other connection) raise LockError, since the swap would silently discard them. Raw SQL bypasses that guard; as a backstop, the commit refuses to swap if the live table’s row count or maximum id changed while the context was open. In-place updates change neither number and escape the backstop, so raw-SQL writers must simply stay away from a table while it is being staged.

EXAMPLES:

Stage a batch of changes – here inserting a (real) totally real quartic field, touching it up, and then deleting it again, so the table ends up back where it started:

>>> nf = db.test_fields
>>> row = {'label': '4.4.725.1', 'degree': 4, 'r2': 0,
...        'disc_abs': 725, 'disc_sign': 1, 'ramps': [5, 29],
...        'class_number': 1, 'class_group': []}
>>> with nf.staged() as staged:
...     staged.insert_many([row])
...     staged.update({'label': '4.4.725.1'}, {'class_number': 1})
...     staged.delete({'degree': 4})
Built primary key on test_fields in ... secs
Staged copy of test_fields created in ... secs
Inserted 1 records into test_fields_tmp in ... secs
...
Swapped temporary tables for test_fields into place in ... secs
New backup at test_fields_old1
>>> nf.count()
22

staged_force_swap()

Adopt a staged copy whose commit never happened: build its indexes and swap it into place, finalized exactly as a clean staged exit would have.

This is the recovery named in the error raised when a staged commit refuses to swap because the live table changed during staging; it also adopts staged tables left behind by a session that died before its commit ran. Unlike the commit it performs no drift check: the staged copy wins, and whatever the live table holds is backed up under _old<n> (so reload_revert still undoes this).

The staged handle that knew whether the staged writes preserved the id ordering is gone, so the table is conservatively marked out of order – always safe, it merely disables a sort optimization; run resort afterward to restore id order. As with a normal staged commit the statistics are invalidated, and the table object held by the database is replaced, so get a fresh reference (db[name]) afterward.

max_id(table=None)

The largest id occurring in the given table. Used in the random method.

min_id(table=None)

The smallest id occurring in the given table. Used in the random method.

copy_from(searchfile, resort=False, reindex=None, restat=True, **kwds)

Efficiently copy data from files into this table.

INPUT:

• searchfile – a string, the file with data for the search table

• resort – whether to sort the ids after copying in the data. Only relevant for tables that are id_ordered.

• reindex – whether to drop the indexes before importing data and rebuild them afterward.

  If the number of rows is a substantial fraction of the size of the table, this will be faster. Defaults to true when the number of rows added is more than 1000

• restat – whether to recreate statistics after reloading.

• kwds – passed on to the COPY command. Cannot include “columns”.

copy_to(searchfile, countsfile=None, statsfile=None, indexesfile=None, constraintsfile=None, metafile=None, columns=None, query=None, include_id=True, **kwds)

Efficiently copy data from the database to a file.

The result will have one line per row of the table, separated by | characters and in order given by self.search_cols.

INPUT:

• searchfile – a string, the filename to write data into for the search table

• countsfile – a string (optional), the filename to write the data into for the counts table.

• statsfile – a string (optional), the filename to write the data into for the stats table.

• indexesfile – a string (optional), the filename to write the data into for the corresponding rows of the meta_indexes table.

• constraintsfile – a string (optional), the filename to write the data into for the corresponding rows of the meta_constraints table.

• metafile – a string (optional), the filename to write the data into for the corresponding row of the meta_tables table.

• columns – a list of column names to export

• query – a query dictionary

• include_id – whether to include the id column in the output file

• kwds – may contain sep and null options for the COPY.

  Cannot include “columns”.

set_sort(sort, id_ordered=True, resort=True)

Change the default sort order for this table

INPUT:

• sort – a list of columns or pairs (col, direction) where direction is 1 or -1.

• id_ordered – the value id_ordered to set when changing the sort to a non None value. If sort is None, then id_ordered will be set to False.

• resort – whether to resort the table ids when changing the sort to a non None value and if id_ordered=True

set_label(label_col=None)

Sets (or clears) the label column for this table.

INPUT:

• label_col – a search column of this table, or None. If None, the current label column will be cleared without a replacement.

get_label()

Returns the current label column as a string.

description(table_description=None)

This stub defines the API for getting and setting the table description. In the LMFDB, this is implemented using the knowl table, but we do nothing by default.

INPUT:

• table_description – if provided, set the description to this value. If not, return the current description.

column_description(col=None, description=None, drop=False)

This stub defines the API for getting, setting and deleting column descriptions. In the LMFDB, this is implemented using the knowl table, but we do nothing by default.

INPUT:

• col – the name of the column. If None, description should be a dictionary with keys equal to the column names.

• description – if provided, set the column description to this value. If not, return the current description.

• drop – if True, delete the column from the description dictionary in preparation for dropping the column.

add_column(name, datatype, description=None, label=False, force_description=False)

Adds a column to this table.

INPUT:

• name – a string giving the column name. Must not be a current column name.

• datatype – a valid Postgres data type (e.g. ‘numeric’ or ‘text’)

• description – a string giving the description of the column

• label – whether this column should be set as the label column for this table (used in the lookup method for example).

drop_column(name, force=False)

Drop a column and any data stored in it.

INPUT:

• name – the name of the column

• force – if False, will ask for confirmation

set_importance(importance)

Production tables are marked as important so that they can’t be accidentally dropped.

Use this method to mark a table important or not important.

class psycodict.table.StagedWriteContext(table)

Bases: object

Context manager for staging writes to a copy of a search table, swapping the copy into place on a clean exit and dropping it on an exception.

Returned by the staged method on PostgresTable; see its documentation for details.