11.18 PostgreSQL

Two drivers: psycopg 3 and psycopg2

django/db/backends/postgresql/base.py imports psycopg under the name Database where it can and psycopg2 where it cannot, raising ImproperlyConfigured with neither, and then refuses, with ImproperlyConfigured again, a psycopg2 older than 2.9.9 or a psycopg 3 older than 3.1.12, as psycopg_version reads them. Psycopg 3 is chosen whenever it imports, and one too old is refused, not passed over for a psycopg2 installed beside it.

django/db/backends/postgresql/psycopg_any.py makes the same choice and exports one set of names under either driver, IsolationLevel and mogrify among them, which the backend’s modules, django.contrib.postgres and the PostGIS backend import (Content types, static files and the other contrib apps).

Some of the backend exists only under psycopg 3: a pool of connections, refused under psycopg2 with ImproperlyConfigured; server-side binding, ignored under psycopg2; the map of adapters and the threshold for preparing statements that each connection is handed; Django’s own subclasses of the driver’s cursor; and the values of integer fields, other than auto fields and keys to them, handed to the driver typed for their column, as psycopg’s Int2, Int4 or Int8.

From the settings to the driver’s arguments: get_connection_params

DatabaseWrapper.get_connection_params makes the keyword arguments of the driver’s connect from the alias’s "OPTIONS", passed on whole but for four keys it always takes out, so that a key the method does not handle, such as "sslmode", reaches the driver as one of PostgreSQL’s own connection parameters. Asked, with no server run, of an alias whose options are those four, "server_side_binding" among them as True, and "sslmode"; in this recording, a line at the left margin is a line of Python as it ran, and its value follows ->:

tuned.get_connection_params()  ->  {'dbname': 'harbour', 'sslmode': 'require', 'client_encoding': 'UTF8', 'cursor_factory': ServerBindingCursor, 'user': 'purser', 'password': 'tide', 'host': 'db.harbour.example', 'port': '5432', 'context': an AdaptersMap, 'prepare_threshold': None}

Of the four, "isolation_level" and "assume_role" are read as the connection opens, "server_side_binding" chooses the cursor class, here ServerBindingCursor, unless the options name a "cursor_factory", and "pool" makes a pool for the alias. Under psycopg 3 the method adds "context", a map of adapters made once for each value of USE_TZ and time zone (Values in and out: adapters, converters and time zones), and "prepare_threshold", None unless the options give one, which turns off psycopg’s preparing of statements on the server, the comment says to keep connection poolers working.

"NAME" may be empty only where the options name a "service", an entry of PostgreSQL’s connection service file, which then supplies the database; with neither, the method refuses with ImproperlyConfigured, as it does a name longer than DatabaseOperations.max_name_length, 63. None, which is how BaseDatabaseWrapper._nodb_cursor asks for no database, becomes "postgres", the server’s default database, after "service" has been taken out of the alias’s own options. Asked, with no server run, of an alias whose "NAME" is None:

serviced.settings_dict['OPTIONS']  ->  {'service': 'harbour', 'sslmode': 'require'}
serviced.get_connection_params()  ->  {'dbname': 'postgres', 'sslmode': 'require', 'client_encoding': 'UTF8', 'cursor_factory': Cursor, 'context': an AdaptersMap, 'prepare_threshold': None}
serviced.settings_dict['OPTIONS']  ->  {'sslmode': 'require'}

Opening a connection: get_new_connection, init_connection_state and pg_version

DatabaseWrapper.get_new_connection settles the isolation level first. With none in the options the connection records IsolationLevel.READ_COMMITTED and says nothing to the driver, Django’s documentation saying that it defaults to READ COMMITTED like PostgreSQL itself; a level named there must be a value of IsolationLevel, or the method refuses with ImproperlyConfigured, and is set on the driver’s connection once that is open. The driver’s connection comes from the alias’s pool where "pool" is set (Opening and closing a connection) and from the driver’s connect otherwise. BaseDatabaseWrapper.connect then sets autocommit as the alias’s "AUTOCOMMIT" says, through DatabaseWrapper._set_autocommit, which sets the driver connection’s own autocommit; the backend sends no "BEGIN" of its own, the driver beginning a transaction by itself while autocommit is off (Transactions: autocommit, atomic and savepoints).

DatabaseWrapper.init_connection_state runs the base class’s version check and then DatabaseWrapper._configure_connection, except on a connection from a pool, which the pool configures itself, having been made with that method as its callback. _configure_connection sets the session’s time zone through DatabaseWrapper._configure_timezone, where the session’s differs from the connection’s, BaseDatabaseWrapper.timezone_name (Values in and out: adapters, converters and time zones), and its role through DatabaseWrapper._configure_role, with "SET ROLE", wherever the options name "assume_role"; the comment says the role is for a login whose credentials are not those of the role that owns the database’s objects. It returns whether it sent anything, and init_connection_state commits where it did and autocommit is off. _configure_role writes the role into its statement before sending it, with DatabaseOperations.compose_sql, which has mogrify merge the parameters through the driver.

DatabaseWrapper.pg_version is the server’s version as the driver’s connection reports it, a number such as 170000 for 17.0, read through BaseDatabaseWrapper.temporary_connection and kept as a cached property. DatabaseWrapper.get_database_version divides it by ten thousand into a pair, and BaseDatabaseWrapper.check_database_version_supported refuses with NotSupportedError a server older than DatabaseFeatures.minimum_database_version, the first time each alias opens in a process.

Cursors: where a parameter is bound, and cursors named on the server

Django’s placeholders, "%s" and "%(name)s", are psycopg’s own under both drivers, so PostgreSQL’s backend hands the driver the statement as the compiler wrote it, with nothing like SQLite’s SQLiteCursorWrapper.convert_query (Cursors: executing a statement, the query log and execute wrappers). What it chooses is where the values meet the statement. Under psycopg 3 they are bound either on the client, by psycopg’s client-side cursor, or on the server, by its standard cursor. The backend’s cursor classes, each CursorMixin on a class of psycopg 3’s, make the choice:

• Cursor, on psycopg’s client-side cursor, is the connection’s cursor class unless "server_side_binding" is True or the options name a "cursor_factory"; under psycopg2 the name is psycopg2’s own cursor class.
• ServerBindingCursor, on psycopg’s standard cursor, is the connection’s cursor class when "server_side_binding" is True and the options name none.
• ServerSideCursor, on psycopg’s class of named cursors with its client-side mixin put before it, is the named cursor of DatabaseWrapper.chunked_cursor unless "server_side_binding" is True.

Server-side binding is off unless the alias asks for it. Under it, parameters passed to expressions in the select list and the grouping are not recognised as the same values, and the features expect Django’s test of such a query to fail (ticket 34255); DatabaseOperations.last_executed_query composes the statement for the log with compose_sql; and DatabaseFeatures.max_query_params becomes 65,535, where it is otherwise none, so that a bulk insert is cut into batches (Inserts: returned keys, conflicts and batch sizes).

DatabaseWrapper.chunked_cursor, the only override of the base class’s among the four backends, asks BaseDatabaseWrapper._cursor for a cursor named "_django_curs_" and then the thread’s identifier, "sync" and a count the connection keeps in DatabaseWrapper._named_cursor_idx, so that no two cursors of a connection, or of two threads, share a name. DatabaseWrapper.create_cursor, given a name, makes a cursor declared on the server, whose rows stay there until each fetch of cursor_iter takes the next chunk (The compiler: as_sql, execute_sql and results_iter). Under psycopg 3 with client-side binding it is a ServerSideCursor, whose docstring says psycopg 3 forces server-side binding on named cursors while the ORM does not yet systematically write SQL that can be prepared (ticket 20516), and the mixin keeps the binding on the client; otherwise it is the driver’s own named cursor. Either is made not scrollable, and holdable, outliving the transaction that declared it, where the connection is in autocommit: the comment says the cursor will then be used outside a transaction. QuerySet.iterator asks for such a read unless the alias sets "DISABLE_SERVER_SIDE_CURSORS" (Evaluation and the result cache), which Django’s documentation says a connection pooler in transaction pooling mode needs, since a cursor held open past its transaction may be asked for its next rows on another connection, where it does not exist.

DatabaseWrapper.make_debug_cursor makes the backend’s own CursorDebugWrapper, which puts the driver’s "COPY" in the query log: copy under psycopg 3, copy_expert and copy_to under psycopg2.

Without a database: the fallback of _nodb_cursor

BaseDatabaseWrapper._nodb_cursor gives a cursor for the statements that create and drop test databases on a connection with "NAME" set to None and the alias NO_DB_ALIAS, which on PostgreSQL is a connection to "postgres" (Opening and closing a connection). PostgreSQL’s DatabaseWrapper._nodb_cursor catches a database error raised before it has given out a cursor, a failure to connect and not of the caller’s statement, and warns with a RuntimeWarning that Django, which uses "postgres" to avoid running initialization queries against the production database, will use the first PostgreSQL database instead. That is the first alias in connections whose backend is PostgreSQL and whose "NAME" is not "postgres": the method connects with its own settings and that name, gives out the cursor and closes the connection afterwards, and with no such alias raises the error again.

Constraints checked at the commit: check_constraints, and is_usable

Django’s schema editor ends a foreign key on PostgreSQL with "DEFERRABLE INITIALLY DEFERRED", DatabaseOperations.deferrable_sql, which the base class leaves empty, so the database checks the key at the commit. The backend does not override BaseDatabaseWrapper.disable_constraint_checking, so BaseDatabaseWrapper.constraint_checks_disabled does nothing here, the keys being deferred already (The connection object: BaseDatabaseWrapper and what a backend provides). What its callers run after their writes, DatabaseWrapper.check_constraints, sends "SET CONSTRAINTS ALL IMMEDIATE", which checks whatever is pending and raises IntegrityError for a row that breaks a constraint, and then "SET CONSTRAINTS ALL DEFERRED", whatever tables it was given. TestCase calls it too, before rolling back each test, where DatabaseFeatures.can_defer_constraint_checks is true and the connection is usable and not marked for rollback, so that a test that left a broken key fails although nothing it wrote is committed.

DatabaseWrapper.is_usable runs "SELECT 1" on a cursor of the driver’s connection, past Django’s cursor, and answers false for any error of the driver’s, as the base class’s docstring asks so that an unusable connection is closed rather than raised over. Django asks it when BaseDatabaseWrapper.close_if_unusable_or_obsolete runs after an error other than a DataError or IntegrityError that no commit or rollback has followed, and under "CONN_HEALTH_CHECKS" at the first cursor, or the first change of autocommit, of each request, except with a pool, which the comment says returns only healthy connections (Opening and closing a connection).

What PostgreSQL’s features say

PostgreSQL’s DatabaseFeatures sets most flags as class attributes, and those that depend on the server’s version or the alias’s options as properties, most of them cached: DatabaseFeatures.is_postgresql_18, read from pg_version, decides supports_uuid7_function, supports_virtual_generated_columns and whether UUID4.as_postgresql writes "GEN_RANDOM_UUID", and DatabaseFeatures.uses_server_side_binding decides max_query_params. Of the four backends, only PostgreSQL’s features set these true: DatabaseFeatures.has_select_for_no_key_update, which the compiler asks for select_for_update(no_key=True); supports_covering_indexes and supports_nulls_distinct_unique_constraints; supports_combined_alters, by which BaseDatabaseSchemaEditor._alter_field joins a column’s changes into one "ALTER TABLE"; and requires_casted_case_in_updates, by which QuerySet.bulk_update casts what it writes for each field (What a database can do: BaseDatabaseFeatures).

The operations object

PostgreSQL’s DatabaseOperations names its own compiler module, whose insert compiler sends the rows of a bulk insert of more than one row as arrays through "UNNEST", where its fields and values allow (Inserts: returned keys, conflicts and batch sizes), and departs from the base class in the dialect of what it writes, as the queryset of voyages that Database backends compiles for the four backends shows.

In PostgreSQL’s statement there, the match on the ship’s name without regard to case ("icontains") is written in three pieces. DatabaseOperations.lookup_cast casts the column to text for "iexact", the pattern lookups and the regular expressions, so that, its comment says, a text lookup can be given a number, reads an address’s column through "HOST" instead, and wraps either in "UPPER" for "iexact", "icontains", "istartswith" and "iendswith", which the comment says is faster; DatabaseWrapper.operators gives "LIKE UPPER(%s)" for the right side; and the lookup escapes the value and adds its wildcards (Lookups: Lookup, BuiltinLookup and the standard comparisons). The part of the date, MONTH, that the filter on "sailed__month" asks for, is written into the statement, since, the comment says, the format of "EXTRACT" cannot be passed as a parameter; the 3 it is compared with stands among the parameters as psycopg’s Int4, made by DatabaseOperations.adapt_integerfield_value.

Three more of its methods, asked beside SQLite’s with no server run:

postgresql.ops.distinct_sql(['"fleet_ship"."name"'], [()])  ->  (['DISTINCT ON ("fleet_ship"."name")'], [])
...
full(postgresql.ops.sql_flush(no_style(), ['office_licence', 'office_berth'], reset_sequences=True))  ->  ['TRUNCATE "office_licence", "office_berth" RESTART IDENTITY;']
...
postgresql.ops.prep_for_iexact_query('50%_off')  ->  '50%_off'
sqlite.ops.prep_for_iexact_query('50%_off')  ->  '50\\%\\_off'

DatabaseOperations.distinct_sql writes "DISTINCT ON" for the fields QuerySet.distinct names, which the base class refuses with NotSupportedError; DatabaseOperations.sql_flush empties the tables with one "TRUNCATE" (Introspection: reading the schema back); and DatabaseOperations.prep_for_iexact_query leaves the value of "iexact", an equality, "= UPPER(%s)", as it is, where the base class’s, which SQLite’s backend keeps, escapes it as for "LIKE", under a comment saying that "iexact" need not be written with "LIKE".

The schema editor

PostgreSQL’s DatabaseSchemaEditor departs from the base class in how it executes a statement, in the indexes it adds for text columns and in how it changes an auto field; with DatabaseFeatures.can_rollback_ddl true, its changes are rolled back with the transaction they are made in (The schema editor: BaseDatabaseSchemaEditor). DatabaseSchemaEditor.execute merges a statement’s parameters into it with compose_sql and hands the base class’s execute the result with None for parameters, its comment saying PostgreSQL won’t merge them on the server; DatabaseSchemaEditor.quote_value, which the base class uses to write a value into a statement’s text, quotes through the driver. Some of its templates set deferred constraints immediate, which runs the checks still pending, so that, their comments say, data can be changed, or a key dropped, in the same transaction: DatabaseSchemaEditor.sql_update_with_default, DatabaseSchemaEditor.sql_create_column_inline_fk, which the base class leaves None, and DatabaseSchemaEditor.sql_delete_fk.

A column of type "varchar" or "text" with an index, from db_index or from unique, gets a second index with an operator class for matching patterns, "varchar_pattern_ops" or "text_pattern_ops", named, like an index Django makes for the field, from the table, the column and a digest of them, with "_like" as its suffix. The comment gives the reason, citing ticket 12234: the second index is needed for correct "LIKE" queries outside the C locale. None is made for an array, nor where the column’s collation, or that of the column a key points at, is not deterministic, which the editor asks the database; the comment says such collations do not support those operator classes. The statements the editor writes for a model with a character field and a text field, each with db_index, and a foreign key, collected with no server run:

full(editor.collected_sql)  ->  ['CREATE TABLE "moorings_chart" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "title" varchar(60) NOT NULL, "notes" text NOT NULL, "port_id" bigint NOT NULL);', 'ALTER TABLE "moorings_chart" ADD CONSTRAINT "moorings_chart_port_id_72d8a6c5_fk_fleet_port_id" FOREIGN KEY ("port_id") REFERENCES "fleet_port" ("id") DEFERRABLE INITIALLY DEFERRED;', 'CREATE INDEX "moorings_chart_title_a40da3ba" ON "moorings_chart" ("title");', 'CREATE INDEX "moorings_chart_title_a40da3ba_like" ON "moorings_chart" ("title" varchar_pattern_ops);', 'CREATE INDEX "moorings_chart_notes_3fea9c52" ON "moorings_chart" ("notes");', 'CREATE INDEX "moorings_chart_notes_3fea9c52_like" ON "moorings_chart" ("notes" text_pattern_ops);', 'CREATE INDEX "moorings_chart_port_id_72d8a6c5" ON "moorings_chart" ("port_id");']

DatabaseSchemaEditor._field_indexes_sql adds the index to what the base class makes for a field; DatabaseSchemaEditor._alter_field adds it when a field gains an index or uniqueness or an indexed text column changes type, and drops it when a unique field becomes neither unique nor indexed. Every name the editor makes for an index or constraint is shortened, where it is too long, to fit within the operations object’s max_name_length, 63 (The schema editor: BaseDatabaseSchemaEditor).

An auto field’s column is an identity column, DatabaseWrapper.data_types_suffix putting "GENERATED BY DEFAULT AS IDENTITY" after its type, as in the "CREATE TABLE" above (Column types: from a field to a column). The base class writes that suffix only where it creates a column, and a change of type writes the type alone, so DatabaseSchemaEditor._alter_column_type_sql gives a column that becomes an auto field its identity with DatabaseSchemaEditor.sql_add_identity, and takes it from one that stops being one with DatabaseSchemaEditor.sql_drop_indentity, so spelt, dropping too the sequence that a "serial" column made by a Django older than 4.1 has. Where the column’s data type changes, and the field is not generated, the clause that changes its type ends with "USING" and a cast of the column to the new type (Altering a column, and SQLite’s table remake).

Introspection and test databases

PostgreSQL’s DatabaseIntrospection reads the server’s own catalogues (Introspection: reading the schema back). Its DatabaseCreation creates a test database with the encoding and the template that the "TEST" setting names, and a copy for each parallel worker with the test database as its template (Test databases: BaseDatabaseCreation).

The client: psql

PostgreSQL’s DatabaseClient runs "psql". The user, the host and the port become "-U", "-h" and "-p", the parameters given to dbshell follow, and the database’s name comes last, "postgres" where the settings give neither a name nor a service. The password is not put on the command line: it goes into the environment as "PGPASSWORD", and the options’ "service", "passfile" and keys of SSL go there as "PGSERVICE", "PGPASSFILE", "PGSSLMODE" and their kin, which BaseDatabaseClient.runshell adds to the process’s own environment. Asked of one set of settings, with no client run:

shore  ->  {'NAME': 'harbour', 'USER': 'purser', 'PASSWORD': 'tide', 'HOST': 'db.harbour.example', 'PORT': '5432', 'OPTIONS': {}}
...
PostgreSQLClient = load_backend('django.db.backends.postgresql').DatabaseWrapper.client_class
full(PostgreSQLClient.settings_to_cmd_args_env(shore, []))  ->  (['psql', '-U', 'purser', '-h', 'db.harbour.example', '-p', '5432', 'harbour'], {'PGPASSWORD': 'tide'})

DatabaseClient.runshell ignores the interrupt signal while psql runs and puts its handler back afterwards, so that, the comment says, an interrupt passes to psql to abort a query.