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:
- engine
sqlalchemy.engine.Engine
Engine to use for this connection.
- origin
int
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.
- namespace
str
, optional The namespace (schema) this database is associated with. If
None
, the default schema for the connection is used (which may beNone
).- writeable
bool
, optional If
True
, allow write operations on the database, includingCREATE TABLE
.
- engine
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 thebtree_gist
PostgreSQL server extension to be installed an enabled on the database being connected to; this is checked at connection time.Attributes Summary
Whether this database supports the
ANY_VALUE
aggregate function or something equivalent.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 existingsqlalchemy.engine.Engine
.Return a
type
that encapsulates the wayTimespan
objects are stored in this database.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:
- column
sqlalchemy.ColumnElement
Original column to wrap.
- column
- Returns:
- wrapped
sqlalchemy.ColumnElement
A column element of the same SQL type that can appear in the
SELECT
clause even when this column does not appear in theGROUP BY
clause.
- wrapped
Notes
This method’s behavior is unspecified when
has_any_aggregate
isFalse
; the caller is responsible for checking that property first.
- clone() PostgresqlDatabase ¶
Make an independent copy of this
Database
object.- Returns:
- db
Database
A new
Database
instance with the same configuration as this instance.
- db
- 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:
- Returns:
- from_clause
sqlalchemy.sql.FromClause
SQLAlchemy object representing the given rows. This is guaranteed to be something that can be directly joined into a
SELECT
query’sFROM
clause, and will not involve a temporary table that needs to be cleaned up later.
- from_clause
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 tosuper
.
- 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:
- table
sqlalchemy.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_only
bool
, 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.
- table
- Returns:
- count
int
The number of rows actually inserted.
- count
- Raises:
- ReadOnlyDatabaseError
Raised if
isWriteable
returnsFalse
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.
- classmethod fromEngine(engine: Engine, *, origin: int, namespace: str | None = None, writeable: bool = True) Database ¶
Create a new
Database
from an existingsqlalchemy.engine.Engine
.- Parameters:
- engine
sqlalchemy.engine.Engine
The engine for the database. May be shared between
Database
instances.- origin
int
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.
- namespace
str
, 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.- writeable
bool
, optional If
True
, allow write operations on the database, includingCREATE TABLE
.
- engine
- Returns:
- db
Database
A new
Database
instance.
- db
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 wayTimespan
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:
- TimespanReprClass
type
(TimespanDatabaseRepresention
subclass) A type that encapsulates the way
Timespan
objects should be stored in this database.
- TimespanReprClass
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 aDatabase
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.
- classmethod makeEngine(uri: str | URL, *, writeable: bool = True) Engine ¶
Create a
sqlalchemy.engine.Engine
from a SQLAlchemy URI.- Parameters:
- Returns:
- engine
sqlalchemy.engine.Engine
A database engine.
- 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:
- table
sqlalchemy.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.
- table
- Raises:
- ReadOnlyDatabaseError
Raised if
isWriteable
returnsFalse
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.
- temporary_table(spec: TableSpec, name: str | None = None) Iterator[Table] ¶
Return a context manager that creates and then drops a temporary table.
- Parameters:
- spec
ddl.TableSpec
Specification for the columns. Unique and foreign key constraints may be ignored.
- name
str
, optional If provided, the name of the SQL construct. If not provided, an opaque but unique identifier is generated.
- spec
- Returns:
- context
AbstractContextManager
[sqlalchemy.schema.Table
] A context manager that returns a SQLAlchemy representation of the temporary table when entered.
- context
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.