PostgresqlDatabase¶

class lsst.daf.butler.registry.databases.postgresql.PostgresqlDatabase(*, engine: Engine, origin: int, namespace: str | None = None, writeable: bool = True)¶

Bases: Database

An implementation of the Database interface for PostgreSQL.

Parameters:

enginesqlalchemy.engine.Engine: Engine to use for this connection.
originint: An integer ID that should be used as the default for any datasets, quanta, or other entities that use a (autoincrement, origin) compound primary key.
namespacestr, optional: The namespace (schema) this database is associated with. If None, the default schema for the connection is used (which may be None).
writeablebool, optional: If True, allow write operations on the database, including CREATE TABLE.

Notes

This currently requires the psycopg2 driver to be used as the backend for SQLAlchemy. Running the tests for this class requires the testing.postgresql be installed, which we assume indicates that a PostgreSQL server is installed and can be run locally in userspace.

Some functionality provided by this class (and used by Registry) requires the btree_gist PostgreSQL server extension to be installed an enabled on the database being connected to; this is checked at connection time.

Attributes Summary

`has_any_aggregate`	Whether this database supports the `ANY_VALUE` aggregate function or something equivalent.
`has_distinct_on`	Whether this database supports the `DISTINCT ON` SQL construct.

Methods Summary

`apply_any_aggregate`(column)	Wrap the given SQLAlchemy column in the `ANY_VALUE` aggregate function or its equivalent.
`clone`()	Make an independent copy of this `Database` object.
`constant_rows`(fields, *rows[, name])	Return a SQLAlchemy object that represents a small number of constant-valued rows.
`ensure`(table, *rows[, primary_key_only])	Insert one or more rows into a table, skipping any rows for which insertion would violate a unique constraint.
`expandDatabaseEntityName`(shrunk)	Retrieve the original name for a database entity that was too long to fit within the database engine's limits.
`fromEngine`(engine, *, origin[, namespace, ...])	Create a new `Database` from an existing `sqlalchemy.engine.Engine`.
`getTimespanRepresentation`()	Return a `type` that encapsulates the way `Timespan` objects are stored in this database.
`isWriteable`()	Return `True` if this database can be modified by this client.
`makeEngine`(uri, *[, writeable])	Create a `sqlalchemy.engine.Engine` from a SQLAlchemy URI.
`replace`(table, *rows)	Insert one or more rows into a table, replacing any existing rows for which insertion of a new row would violate the primary key constraint.
`shrinkDatabaseEntityName`(original)	Return a version of the given name that fits within this database engine's length limits for table, constraint, indexes, and sequence names.
`temporary_table`(spec[, name])	Return a context manager that creates and then drops a temporary table.

Attributes Documentation

has_any_aggregate¶

has_distinct_on¶

Methods Documentation

apply_any_aggregate(column: ColumnElement[Any]) → ColumnElement[Any]¶

Wrap the given SQLAlchemy column in the ANY_VALUE aggregate function or its equivalent.

Parameters:

columnsqlalchemy.ColumnElement: Original column to wrap.

Returns:

wrappedsqlalchemy.ColumnElement: A column element of the same SQL type that can appear in the SELECT clause even when this column does not appear in the GROUP BY clause.

Notes

This method’s behavior is unspecified when has_any_aggregate is False; the caller is responsible for checking that property first.

clone() → PostgresqlDatabase¶

Make an independent copy of this Database object.

Returns:

dbDatabase: A new Database instance with the same configuration as this instance.

constant_rows(fields: NamedValueAbstractSet[FieldSpec], *rows: dict, name: str | None = None) → FromClause¶

Return a SQLAlchemy object that represents a small number of constant-valued rows.

Parameters:

fieldsNamedValueAbstractSet [ ddl.FieldSpec ]: The columns of the rows. Unique and foreign key constraints are ignored.
*rowsdict: Values for the rows.
namestr, optional: If provided, the name of the SQL construct. If not provided, an opaque but unique identifier is generated.

Returns:

from_clausesqlalchemy.sql.FromClause: SQLAlchemy object representing the given rows. This is guaranteed to be something that can be directly joined into a SELECT query’s FROM clause, and will not involve a temporary table that needs to be cleaned up later.

Notes

The default implementation uses the SQL-standard VALUES construct, but support for that construct is varied enough across popular RDBMSs that the method is still marked abstract to force explicit opt-in via delegation to super.

ensure(table: Table, *rows: dict, primary_key_only: bool = False) → int¶

Insert one or more rows into a table, skipping any rows for which insertion would violate a unique constraint.

Parameters:

tablesqlalchemy.schema.Table: Table rows should be inserted into.
*rows: Positional arguments are the rows to be inserted, as dictionaries mapping column name to value. The keys in all dictionaries must be the same.
primary_key_onlybool, optional: If True (False is default), only skip rows that violate the primary key constraint, and raise an exception (and rollback transactions) for other constraint violations.

Returns:

countint: The number of rows actually inserted.

Raises:

ReadOnlyDatabaseError: Raised if isWriteable returns False when this method is called. This is raised even if the operation would do nothing even on a writeable database.

Notes

May be used inside transaction contexts, so implementations may not perform operations that interrupt transactions.

Implementations are not required to support ensure on tables with autoincrement keys.

expandDatabaseEntityName(shrunk: str) → str¶

Retrieve the original name for a database entity that was too long to fit within the database engine’s limits.

Parameters:

shrunkstr: The original name.

Returns:

shrunkstr: The new, possibly shortened name.

classmethod fromEngine(engine: Engine, *, origin: int, namespace: str | None = None, writeable: bool = True) → Database¶

Create a new Database from an existing sqlalchemy.engine.Engine.

Parameters:

enginesqlalchemy.engine.Engine: The engine for the database. May be shared between Database instances.
originint: An integer ID that should be used as the default for any datasets, quanta, or other entities that use a (autoincrement, origin) compound primary key.
namespacestr, optional: A different database namespace (i.e. schema) the new instance should be associated with. If None (default), the namespace (if any) is inferred from the connection.
writeablebool, optional: If True, allow write operations on the database, including CREATE TABLE.

Returns:

dbDatabase: A new Database instance.

Notes

This method allows different Database instances to share the same engine, which is desirable when they represent different namespaces can be queried together.

classmethod getTimespanRepresentation() → type[lsst.daf.butler.timespan_database_representation.TimespanDatabaseRepresentation]¶

Return a type that encapsulates the way Timespan objects are stored in this database.

Database does not automatically use the return type of this method anywhere else; calling code is responsible for making sure that DDL and queries are consistent with it.

Returns:

TimespanReprClasstype (TimespanDatabaseRepresention subclass): A type that encapsulates the way Timespan objects should be stored in this database.

Notes

There are two big reasons we’ve decided to keep timespan-mangling logic outside the Database implementations, even though the choice of representation is ultimately up to a Database implementation:

Timespans appear in relatively few tables and queries in our typical usage, and the code that operates on them is already aware that it is working with timespans. In contrast, a timespan-representation-aware implementation of, say, insert, would need to have extra logic to identify when timespan-mangling needed to occur, which would usually be useless overhead.

SQLAlchemy’s rich SELECT query expression system has no way to wrap multiple columns in a single expression object (the ORM does, but we are not using the ORM). So we would have to wrap _much_ more of that code in our own interfaces to encapsulate timespan representations there.

isWriteable() → bool¶: Return True if this database can be modified by this client.

classmethod makeEngine(uri: str | URL, *, writeable: bool = True) → Engine¶

Create a sqlalchemy.engine.Engine from a SQLAlchemy URI.

Parameters:

uristr or sqlalchemy.engine.URL: A SQLAlchemy URI connection string.
writeablebool, optional: If True, allow write operations on the database, including CREATE TABLE.

Returns:

enginesqlalchemy.engine.Engine: A database engine.

Notes

Subclasses that support other ways to connect to a database are encouraged to add optional arguments to their implementation of this method, as long as they maintain compatibility with the base class call signature.

replace(table: Table, *rows: dict) → None¶

Insert one or more rows into a table, replacing any existing rows for which insertion of a new row would violate the primary key constraint.

Parameters:

tablesqlalchemy.schema.Table: Table rows should be inserted into.
*rows: Positional arguments are the rows to be inserted, as dictionaries mapping column name to value. The keys in all dictionaries must be the same.

Raises:

ReadOnlyDatabaseError: Raised if isWriteable returns False when this method is called.

Notes

May be used inside transaction contexts, so implementations may not perform operations that interrupt transactions.

Implementations should raise a sqlalchemy.exc.IntegrityError exception when a constraint other than the primary key would be violated.

Implementations are not required to support replace on tables with autoincrement keys.

shrinkDatabaseEntityName(original: str) → str¶

Return a version of the given name that fits within this database engine’s length limits for table, constraint, indexes, and sequence names.

Implementations should not assume that simple truncation is safe, because multiple long names often begin with the same prefix.

The default implementation simply returns the given name.

Parameters:

originalstr: The original name.

Returns:

shrunkstr: The new, possibly shortened name.

temporary_table(spec: TableSpec, name: str | None = None) → Iterator[Table]¶

Return a context manager that creates and then drops a temporary table.

Parameters:

specddl.TableSpec: Specification for the columns. Unique and foreign key constraints may be ignored.
namestr, optional: If provided, the name of the SQL construct. If not provided, an opaque but unique identifier is generated.

Returns:

contextAbstractContextManager [ sqlalchemy.schema.Table ]: A context manager that returns a SQLAlchemy representation of the temporary table when entered.

Notes

Temporary tables may be created, dropped, and written to even in read-only databases - at least according to the Python-level protections in the Database classes. Server permissions may say otherwise, but in that case they probably need to be modified to support the full range of expected read-only butler behavior.

Navigation

PostgresqlDatabase¶