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 tousername
.password (
str
) – The password to authenticate with.application_name (
Optional
[str
]) – The application name to send. Defaults topg-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.
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:
- Returns:
Either a
PostgresMessage
, or the specialNEED_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:
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 setErrorOrNoticeResponse
, 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:
- 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:
This will yield several messages, usually in this order:
A
RowDescription
, if this query returns data, or aErrorOrNoticeResponse
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:
This will yield several messages, usually in this order:
A
ParseComplete
, or aErrorOrNoticeResponse
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 byinfo
, 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.
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.