Commit 73358da

py-postgresql gotchas

=====================

It is recognized that decisions were made that may not always be ideal for a

given user. In order to highlight those potential issues and hopefully bring

some sense into a confusing situation, this document was drawn.

Cursors are not usable after transaction commits or aborts

----------------------------------------------------------

`postgresql.driver` uses protocol level cursors. When a cursor is bound, there

is no way for the driver to state that the cursor should be held via the

protocol. This causes two potentially unfortunate things:

- In auto-commit mode, the entire cursor is read.

- When a transaction closes, all cursors created become unusable.

Prepared statements, in contrast, are not automatically closed.

client_encoding and TimeZone GUCs should not be altered

-------------------------------------------------------

`postgresql.driver`'s cursor implementation reads a fixed set of rows when it

queries the server for more. In order to optimized some situations, the driver

will send a request for more data, but makes no attempt to wait and process the

data as it is not yet needed. When the user comes back to read more data from

the cursor, it will then look at this new data. The problem being, if the

`client_encoding` or the `TimeZone` was switched, it may use the wrong codec and

tzinfo to transform the wire data into higher level Python objects.

To avoid this problem from ever happening, set the `TimeZone` or

`client_encoding` early. Furthermore, it is probably best to never change the

`client_encoding` as the driver automatically makes the necessary tranformation

to Python strings.

The user and password is correct, but does it not work when using `postgresql.driver`

-------------------------------------------------------------------------------------

This issue likely comes from the possibility that the information sent to the

server early in the negotiation phase may not be in an encoding that is

consistent with the server's encoding.

One problem is that PostgreSQL does not provide the client with the server

encoding early enough in the negotiation phase, and, therefore, is unable to

process the password data in a way that is consistent with the server's

expectations.

Another problem is that PostgreSQL takes much of the data in the startup message

as-is, so a decision about the best way to encode parameters is difficult.

The easy way to avoid *most* issues with this problem is to initialize the

database in the `utf-8` encoding. The driver defaults the expected server

encoding to `utf-8`. However, this can be overridden by creating the `Connector`

with a `server_encoding` parameter. Setting `server_encoding` to the proper

value of the target server will allow the driver to properly encode *some* of

the parameters. Also, any GUC parameters passed via the `settings` parameter

should use typed objects when possible to hint that the server encoding should

not be used on that parameter.