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.
glob_expression(expression, pattern)	Produce boolean expression for expression match against glob-like pattern.
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.

glob_expression(expression: ColumnElement[Any], pattern: str) → ColumnElement[bool]

Produce boolean expression for expression match against glob-like pattern.

Parameters:

expressionsqlalchemy.ColumnElement

Column expression that evaluates to string.

patternstr

GLob pattern string.

Returns:

globsqlalchemy.ColumnElement

Boolean expression that matches expression against pattern.

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.