Low-Level API

The low-level API is a sans-IO, in-memory PostgreSQL client. It implements creation and parsing of PostgreSQL network protocol messages, and maintains internal state relating to the client connection.

Warning

Despite this class primarily being laid out similar to the PostgreSQL protocol itself, this is not a protocol state machine, but a client with its own semantics (e.g. you can never send a Parse message by itself, it must always be accompanied with a Describe).

Creation

The primary class for the low-level client is SansIOClient.

class pg_purepy.SansIOClient(username, database=None, password=None, application_name='pg-purepy', logger_name=None, ignore_unknown_types=True)

Bases: object

Sans-I/O state machine for the PostgreSQL C<->S protocol. This operates as an in-memory buffer that takes in Python-side structures and turns them into bytes to be sent to the server, and receives bytes from the server and turns them into Python-side structures.

__init__(username, database=None, password=None, application_name='pg-purepy', logger_name=None, ignore_unknown_types=True)

Parameters:

username (str) – The username to authenticate as. Mandatory.
database (str) – The database to connect to. Defaults to username.
password (str) – The password to authenticate with.
application_name (Optional[str]) – The application name to send. Defaults to pg-purepy.
logger_name (str) – The name of the logger to use. Defaults to a counter.
ignore_unknown_types (bool) – If True, unknown types are returned as strings. Otherwise, raises an exception.

property encoding: str: The client encoding.

property timezone: tzinfo: The server timezone.

property dead: bool: If the protocol is “dead”, i.e. unusable via an error or terminated.

Doing… Stuff

The client by itself doesn’t really do anything. Instead, there’s a process in order to make it do something.

Call the do_* method for the thing you want to do, e.g. send a query. This updates internal states and returns the bytes for the command.
Send the bytes returned by the do_* function to the server.
Enter the Ready Loop.

The Ready Loop

The ready loop is the key construct for working with the low-level API. It consists of a simple loop that does several steps.

First, you need to check if the protocol is already ready. If you don’t do this, you’ll likely get a deadlock because the server will have nothing to send and you’ll wait forever on reading.

SansIOClient.ready: If the server is ready for another query.

Second, you need to check if there are any incoming messages from the server. This is done by calling SansIOClient.next_event() in an inner while True loop until it returns the special NO_DATA constant sentinel value. You should also do error handling wrt to ErrorOrNoticeResponse messages, if so desired. If this inner loop returns ReadyForQuery, then you have finished the whole loop and should break out of both the inner and outer loops.

while True:
    event = protocol.next_event()
    if event == NEED_DATA:
        break

    # optional, you can just yield it or whatever else
    if isinstance(event, ErrorResponse): ...

    do_something_with_event(event)
    if isinstance(event, ReadyForQuery):
        return

pg_purepy.NO_DATA: A dummy sentinel value used to signal the client needs more data to proceed.

SansIOClient.next_event()

Reads the next event from the message queue.

Return type:: Union[PostgresMessage, object]
Returns:: Either a PostgresMessage, or the special NEED_DATA constant.

Next, you need to get any synchronisation data that the client needs to send to the server. This may occur during authentication for example.

to_send = protocol.get_needed_synchronisation()
send_data(to_send)

SansIOClient.get_needed_synchronisation()

Gets the bytes to send to the server if we need to do synchronisation. This is used e.g. during a complex query message where a Sync packet is needed, or authentication where multiple messages may need to be sent.

This may change the state of the protocol machine.

Return type:: bytes

Finally, you need to read incoming data from the server, and feed it into the protocol machine for later processing. This data won’t actually be processed until you call next_event() in the next iteration of the loop.

received = read_data()
protocol.receive_bytes(received)

SansIOClient.receive_bytes(data)

Receives incoming bytes from the server. This merely appends to an internal buffer; you need to call next_event() to do anything.

Parameters:: data (bytes) – The incoming data from PostgreSQL.

The ready loop in the mid-level API looks like this, for reference:

while not self._protocol.ready:
    while True:
        next_event = self._protocol.next_event()
        if next_event is NEED_DATA:
            break

        if isinstance(next_event, ErrorOrNoticeResponse) and next_event.notice:
            if next_event.severity == "WARNING":
                err = wrap_error(next_event)
                warnings.warn(str(err))

        yield next_event

        if isinstance(next_event, ReadyForQuery):
            await anyio.sleep(0)  # checkpoint()
            return

    to_send = self._protocol.get_needed_synchronisation()
    if to_send:
        await self._stream.send(to_send)

    received = await self._stream.receive(65536)
    self._protocol.receive_bytes(received)

Note

The order of this may look odd, but it helps prevent deadlocks. Draining the events first means that any events that may have been sent AFTER the ReadyForQuery, for example a second query that was queued by the client before the server sent the ReadyForQuery message.

The ready loop should be performed after every single action to completion, in order to drain data from the server that is no longer relevant. If that’s not possible, then methods should call the ready loop before performing any other actions, for the same reason.

Sending Commands

Each do_* command performs a specific action or set of actions on the server. Each command will cause the server to issue a specific set of responses in return, which are constructed into their own message classes.

Additionally, the server may issue certain responses at any point in the connection. These will be captured by the client and returned from next_event() automatically. These are:

ParameterStatus, when a runtime parameter is set
ErrorOrNoticeResponse, for server-sent errors or notices

Warning

ErrorOrNoticeResponse instances will only be captured if they are either notices, or recoverable. Non-recoverable errors will be turned into protocol errors.

Once the the server has sent all messages, a ReadyForQuery event will be returned.

Startup

The first message you must send is the startup message. This contains the username and database information.

SansIOClient.do_startup()

Gets the startup packet, and sets the state machine up for starting up.

Return type:: bytes
Returns:: A startup message, that should be sent to PostgreSQL.

Any additional messages may be sent during the synchronisation stage.

This will yield several messages, usually in this order:

The AuthenticationRequest describing how the client should authenticate, if needed.
Several ParameterStatus message, setting runtime parameters.
A BackendKeyData message, used for potential later cancellation.
A AuthenticationCompleted message, signifying that the client can now send commands.

Simple Queries

Simple queries are static queries with no parameters that combine parsing and execution into one step. These queries should be used by a higher-level client when no parameters are needed, for network performance reasons.

To perform a simple query, you should call do_simple_query().

SansIOClient.do_simple_query(query_text)

Performs a simple (static) query. This query cannot have any dynamic parameters.

Return type:: bytes

This will yield several messages, usually in this order:

A RowDescription, if this query returns data, or a ErrorOrNoticeResponse if the query was invalid in some way.
Zero to N DataRow instances.
One CommandComplete instance.

Extended Queries

Extended queries are queries using prepared statements and SQL injection invulnerable parameter injection.

To perform an extended query, you need to create a prepared statement. This will issue a Parse+Describe combo of messages to the server.

SansIOClient.do_create_prepared_statement(name, query_text)

Creates a prepared statement. If name is not specified, this will create the unnamed prepared statement.

Return type:: bytes

This will yield several messages, usually in this order:

A ParseComplete, or a ErrorOrNoticeResponse if the query was invalid in some way.
A ParameterDescription, if the query has parameters to be filled.
A PreparedStatementInfo wrapping the relevant info about the prepared query.

Warning

Internally, the server actually returns either a RowDescription or a NoData message, but both are wrapped into the PreparedStatementInfo.

Once you have a prepared statement, you can then run the query.

SansIOClient.do_bind_execute(info, params, row_count=None)

Binds the specified params to the prepared statement specified by info, then executes it.

If row_count is not None, then only that many rows will be returned at maximum. Otherwise, an unlimited amount of rows will be returned.

This will yield a similar sequence of messages as the simple query:

(Optional) A ErrorOrNoticeResponse if the parameters were invalid in some way.
A BindComplete.
0 to N DataRow instances for the actual data of the query.

Termination

When you are done with a connection, you should send a Terminate message. This closes the client and no further actions will work.

SansIOClient.do_terminate()

Gets the Terminate message. This closes the protocol!

Return type:: bytes

Internal State

The client object exposes the current state of the protocol state machine using SansIOClient.state.

SansIOClient.state: The current state of this connection.

class pg_purepy.protocol.ProtocolState(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)

Bases: Enum

Enumeration of possible states for the protocol machine.

STARTUP = 0: The initial state. We are waiting to send our startup message.

SENT_STARTUP = 1: We’ve sent our startup message. We’re waiting for the server to respond to us.

AUTHENTICATED_WAITING_FOR_COMPLETION = 2: We’ve authenticated, receiving an Authentication Request Success, but we’re waiting for a Ready For Query message.

SASL_STARTUP = 10: During startup, we’ve been told to authenticate using SASL.

SASL_FIRST_SENT = 11: During authentication, we’ve sent our first SASL message, and we’re waiting for a response from the server.

SASL_FIRST_RECEIVED = 12: During authentication, we’ve received the server’s first SASL message, and we’re waiting to send the final message.

SASL_FINAL_SENT = 13: During authentication, we’ve sent our final SASL message, and we’re waiting for a response from the server.

MD5_STARTUP = 20: During startup, we’ve been told to authenticate using MD5.

MD5_SENT = 21: During authentication, we’ve sent our MD5-hashed password.

CLEARTEXT_STARTUP = 30: During startup, we’ve been told to authenticate using a cleartext password.

CLEARTEXT_SENT = 31: During authentication, we’ve sent our cleartext password.

SIMPLE_QUERY_SENT_QUERY = 100: During a simple query, we’ve sent the query to the server.

SIMPLE_QUERY_RECEIVED_ROW_DESCRIPTION = 101: During a simple query, we’ve received the row description message, and we’re waiting for either data rows, or command completion.

SIMPLE_QUERY_RECEIVED_COMMAND_COMPLETE = 102: During a simple query, we’ve received the command completion message, and we’re waiting for the Ready for Query message.

MULTI_QUERY_SENT_PARSE_DESCRIBE = 300

MULTI_QUERY_RECEIVED_PARSE_COMPLETE = 301: We’ve received a ParseComplete, and are waiting for the ParameterDescription+RowDescription messages.

MULTI_QUERY_RECEIVED_PARAMETER_DESCRIPTION = 302: We’ve received a ParameterDescription, and are waiting for a RowDescription. This state may be skipped.

MULTI_QUERY_DESCRIBE_SYNC = 303: We’ve received our RowDescription, and are waiting for the ReadyForQuery message.

MULTI_QUERY_SENT_BIND = 310: We’ve sent a Bind message, and are waiting for a BindComplete.

MULTI_QUERY_READING_DATA_ROWS = 311: We’ve received a BindComplete or have unsuspended the portal, and are waiting for the data row messages.

MULTI_QUERY_RECEIVED_PORTAL_SUSPENDED = 312: We’ve received a PortalSuspended message, and need to send another Execute.

MULTI_QUERY_RECEIVED_COMMAND_COMPLETE = 313: We’ve received a CommandComplete messagee, and we are waiting for the ReadyForQuery message.

READY_FOR_QUERY = 999: We’re ready to send queries to the server. This is the state inbetween everything happening.

UNRECOVERABLE_ERROR = 1000: An unrecoverable error has happened, and the protocol will no longer work.

RECOVERABLE_ERROR = 1001: A recoverable error has happened, and the protocol is waiting for a ReadyForQuery message.

TERMINATED = 9999: The connection has been terminated.