Struct QueryOracle 

pub struct QueryOracle { /* private fields */ }

Implementations

impl QueryOracle

pub fn new(dsn: impl ToString) -> Self

pub async fn inspect_statement( &mut self, statement: &str, ) -> Result<InspectedStatement>

Inspect a statement and return an InspectedStatement, containing a summary of the side effects of the statement.

It is non-trivial to understand exactly what impacts a given SQL statement has on a Postgres database. This is because the actual effects of a statement depend almost entirely on the current state of the database (including the version), which is not present in the statement itself. The statement is completely devoid of any context, and the context is the only thing that matters.

For example, an ALTER COLUMN statement on a large table will either:

• Require a lengthy rewrite of the entire table, during which no other operations can be performed on the table at all, resulting in potential service disruption
• Be an instantaneous metadata-only operation that does not block any other operations on the table and requires no data to be rewritten.

Which of these two outcomes occurs depends on the current type of the column and the type we are altering:

• if the column is “binary compatible” with the new type then the operation is metadata-only
• otherwise it requires a full rewrite.

Which types are binary compatible is often an implementation specific detail of the Postgres version in use, or in the case of custom data types the implementation of the type itself.

In order to understand the impact of a statement, we need to execute it in a controlled environment and observe the side effects. This is what this method does: it provides a “query oracle” that executes the statements and observes the side effects. This relies entirely on Postgres’ introspection capabilities as well as it’s ability to execute DDL statements (such as ALTER TABLE) in a transaction, meaning it is generic across all supported Postgres versions, data types and extensions.

Detecting locks

Postgres provides no built-in way to detect which tables a statement is trying to access, or what locks are required. In order to work around this, we force a controlled lock conflict using multiple transactions in a way that allows us to view the blocking locks. This gives us information on the specific tables and lock types that the query requires.

Specifically, we use the following algorithm:

1. A new “locker” connection is created, and a transaction is begun
2. The locker connection locks all tables present in the database with an ACCESS EXCLUSIVE lock. This prevents all other transactions from performing any operations on the tables.
3. A new “executor” connection is created, and a transaction is begun
4. The executor executes the statement we are inspecting. This will cause the transaction to be blocked by the “locker” connection.
5. When the executor detects that the statement is blocked it yields control back to the oracle, whilst keeping the blocked transaction open in it’s blocked state.
6. The “locker” connection then lists all locks that are blocking the executor’s transaction, which reveals the tables that the statement is trying to access. We now have an initial set of tables that the statement is accessing.
7. We close the “executor” connection without committing the transaction, and close the “locker” connection after. This releases the locks on the tables.
8. We repeat steps 1 to 7, but this time only locking the tables that have not been locked in the previous iterations. This allows us to detect new locks that the statement is trying to acquire.
9. We repeat this process until the statement is no longer blocked by any locks and executes successfully.

Once this process is completed, we have observed the complete set of locks that the statement requires in order to execute.

One caveat of this approach is that it is only really suitable to be run in a controlled, isolated instance of Postgres (i.e. not a production environment), and so it requires the schema to completely match the production schema in order to give accurate results.

Detecting other effects

In addition to detecting locks, we also need to detect the other side effects of the statement. These include the specific objects that are added, removed, or modified, as well as any tables that are rewritten.

The implementation of this is much simpler: we simply introspect relevant database objects before and after the statement is executed, and compare the two sets of objects.