python-postgres / fe Goto Github PK

Reported by http://twitter.com/rintavarustus

Unix Connector references SocketCreator instead of SocketFactory.
No documentation for unix keyword(may not work with open).
Needs tests and must patch v0.9 as well.

Per-request from sqlalchemy, use a custom exception hierarchy for the .driver.dbapi20 module.

datetime.* doesn't support it, so a really high date will need to be used.

Should some text compensation be provided for input? if x == 'infinity': x=DateTimeInfinity?

Extend postgresql.string to include a means to make COPY rows from a sequence of Python strings or None's. Where None represents NULL. For strings, this would mean properly escaping field content.

Also, include a parser to split and normalize the fields of a given serialized COPY row.

This will only support the traditional tab-separated values

Notably, enum ARRAYs, and composites containing enums.

Make it less stupid.

Looks like they're adding this in 9.0. Issue opened to note that.

Currently, type I/O is undocumented and in need of some API refactoring.

Also, type I/O on statements and cursors need to be adjustable.

Difficulties involve cases where the I/O routine may need to depend on backend specific information like integer_datetimes.

Add a section to the driver documentation about working with threads. Most drivers offer thread safety, but driver.pq3 does not.

Discuss: one connection per thread, synchronizing using a queue, and explicit locking.

When doing tuple processing, it's always against a sequence of rows with the same number of columns. So, let's dynamically generate the code to process the tuples. By doing so, we should be able to get a performance gain in pure-Python cases.

The number of tuple processors will be consistent with the number of statements created; 2-per statement object.

Subsequently, process_chunk becomes a map() operation.

The .pgpassfile is far too simple; csv is a bit heavy of a requirement.

extract_command() should probably just be self.data.strip(" \t\n0123456789")

Currently: "ALTER TABLE" will come back as "ALTER". Only the first "word" is returned by extract_command().

Refine the recommendation of PG-API, talk about the implemented DB-API extensions, link to the PEP.

I've seen some people complain about projects using file, and that's exactly what py-postgresql uses to get lib*.sql files.

Make a function that will try to hit pkgutil first, and fallback to file based resolution.

postgresql.cluster has made a serious effort in order to automate the management of PostgreSQL clusters using Python.

The next step is to expose these interfaces so that users may manage their PG data directories using a web interface: Web APIs(JSON and XML-RPC?) and a JavaScript GUI application.

The Web API will need the following actions:

• Identify an Installation
• Forget an Installation
• Initialize a data directory
• Remove a data directory
• List data directories
• Start a cluster
• Stop a cluster
• Restart a cluster
• Reload a cluster
• Permanently set a setting in a cluster
• Get a setting in a cluster
• Get all settings in a cluster
• Write the HBA file
• Read the HBA file
• Create a Database
• Drop a Database
  ... (database management? )=
• Create Role
• Drop Role
  ... (role management? )=
• WAL directory relocation (other directories?)
• Logging Configuration?

Other ideas: cluster configuration templates, configuration restoration(last known working config mgmt).

The GUI part may need to come later, but it would be nice to have something in the first round.

Use a Mutable bytes array to manage the out-going data.

This won't change element3.Message.serialize(), but it should offer some benefits wrt to avoiding reallocating the out-buffer. Additionally, it would allow a more appropriate out-going message consumption loop.

buffer_messages(buffer, messages) writes as many messages as possible to the buffer, returning the number written in full. The buffer size will be automatically increased in cases where the first message is too large for the buffer.

PostgreSQL's advisory locks offers users a handy tool.

Add a lock interface that is consistent with threading's Lock():

l = db.lock()
Creates a lock object with a generated identifier

il = db.lock('explicit_id')
Creates a lock object with an explicit identifier.
Will need to derive an numeric ID from the string.

From there, l and il must support the methods provided by Lock():

l.acquire([blocking = 1])
l.release()

Additionaly, CM interfaces:

with l:
...

Where l.enter is consistent with l.acquire(blocking = True).

Two options:

1. Use pg_ctl.
2. Implement/steal pg_kill (what pg_ctl uses for supporting windows.)

Part of the ultimate purpose of Cluster() is to provide a postgres daemon manager. Using pg_ctl tends to get in the
way of that: no access to the process result code, no access to any early failure messages on startup(?), and indirect
access to the PostgreSQL "ping" tool for waiting for startup.

Practical problems implementing pg_kill: What happens when core changes it?
Problems using pg_ctl: Difficulty providing quality interfaces(bad past experiences).

The way I see it:

pg_ctl = DBA tool.
postgresql.cluster.Cluster = Python Programmer's tool.

This distinction leads me to think that re-implementing pg_kill will be the right solution.

There appears to be many more fatal than non-fatal socket exceptions, so use a list of non-fatal socket exceptions to identify the fatal ones. =(

Per the sqlalchemy test failures pointed out by Kevin:

'SELECT flds.intcol % %(intcol_1)s AS anon_1 \nFROM flds ORDER BY flds.idcol' {'intcol_1': 3}

The paramstyle code expects %% to be used to represent %.

In my own code, I've come across a pattern of ILF use:

project/lib:

• libfoo.sql
• foo.py

Where the foo module creates a class, Foo, that references libfoo.sql. Often, some higher-level functionality is needed in addition to the mere collection of queries.

I'm currently thinking of implementing this using a metaclass that resolves the ILF location relative to the .py file of the class' module. Subsequently, we can automatically bind the library to a common location, '.lib', when the Foo class is instantiated against a connection.

Seems like it will be a great way to avoid all the memcpy's going on throughout .protocol.

Some problems: port.optimized needs to be taught about them, and some .parse() methods are written expecting bytes().

Move the core option information in clientparameters into a resource/data module to be referenced by clientparameters.

Currently option information is built and stored using make_option. With python-dev's apparent direction of moving to argparse, this no longer appears to be an appropriate long term format.

(Might be wise to consider multi-lingual support for the descriptions..)

In order to properly calibrate fetch sizes and recvsize, some information about what's happening on the wire would be useful. For instance, if we find that a cursor's rows are relatively small on average, we should increase fetchsize in order to reduce the number of syscalls being issued.

The rather low recvsize has proven to be an impediment to performance.

title says it all.

New file:

postgresql/types/io/pg_hstore.py

Look at 8.5/9.0 send and recv functions to get started.

hstore is in contrib, so tests need to be conditional.
hstore should map to/from a Python dictionary.

Implement a symbol definition extension that allows the binding to identify that the symbol's statement is a reference
to the statement that should be executed.

If the symbol's statement takes no parameters, the reference should be resolved at bind time; if the statement does
take parameters, the symbol's execution must be a two step process: resolve the reference using the given parameters,
and apply the remaining parameters to the dereferenced statement.

This will establish a dynamic query policy: SQL should be used to generate dynamic statements.

While it may be easier in Python with the appropriate abstractions, it's implementation would require more semantic
knowledge of the statement's structure(execute this python code in a special context here, here and there). By deferring
to SQL, it will likely encourage more portable SQL generation.

Name: statement references | symbol pointers | ?

Format: [name&]

Use the '&' to lend toward C's reference operator.
I would like to prepend the character, but I don't want interfere with grep'ing.

Pretty easy change to support DO statements:

def do(self, code, language = None):
self.execute("DO " + quote_literal(code) + (" LANGUAGE " + language) if language is not None else "")

v1.0 removed documentation, and subsequent bugfixes will include deprecation warnings.

Queue this up on v1.1's list.

Use ctypes/libffi to implement libpq's usage of PGGSSLIB.

This is a significant barrier for entry for applications that work with many users.

Chances are that this will be implemented as a separate project and then incorporated into the package tree.

Returns an iterator to the values in the first column of the result.

The trick is to alter the processing pipeline to quickly target the first column so that the least amount of time is spent recreating tuples.

Often, just using the keyword hack, regular functions perform as well as the C compose(), and are more portable than the C compose().

Of course, performance checks should be made..

Looks like rolling back to a savepoint doesn't "release" it.

This would only be, potentially, problematic in cases where xact() is repeatedly entered and exited with exception. Nested xact() are fairly safe because rolling back to an earlier SP will implicitly release the latter SP's.

In some recent profiling, it became evident that using element3.Tuple() was rather costly for little functional gain. The only gain it provides is the ability to query the 'type' attribute.

So by making some special cases response message processing, we can just use tuple() objects to identify tuple data coming from PG.

Currently the support for Notify messages is pretty well undocumented. It looks like payload support is coming in 8.5, so it will be important for this to be more visible.

There are a few parts to implementing an appropriate set of listening interfaces:

• Subscribe/Unsubscribe interface (basically, emit N-LISTEN $id statements)?
• Notification hook management (punting here; single attribute, let user decide the implementation)
• wait() method for simple event loops

The simple wait()[db.listen()?] method will perform pg_sleep() function calls in order to keep an active protocol transaction. This would allow for interrupts to take place, tho, not reliably(small windows between sleep() calls)..

The .pgpassfile is far too simple; csv is a bit heavy of a requirement.

Nice thing about this is with it being relative to the user so we can use it without forcing the user to set PGSYSCONFIG environment variable.

Symbols don't provide any metadata. While statements can provide much of this information, procedure references require
some specialization wrt the exposed description.

A full description is needed:

parameter types
parameter names
optional parameters (due to procedure defaults[8.4 feature])
result types
result column names (if applicable; single column results may not have names)
result transformation/finalized type (if any; column/mapping)

How should COPY be described? o_O

I'm thinking along the lines of:

Bs.pg_description, Bs.sql_description

Where each attribute can express type identifiers in a native form...

Statements and procedures are a bit more simple than symbols, so I'm not optimistic about being able to directly reflect
their metadata attributes.

Primarily, support lowerbounds.

Evaluate some of the algorithms in the methods. I think a few need refactoring.

Much of the state machine is over complicated to compensate for 'Q' and 'F' commands. By isolating those in different classes and restricting transactions to a single sync, much of the complexity can be simply removed.

Currently, the identifiers of notice message parts are being translated into descriptive identifiers--this is covers all the parts currently emitted by PG, but will not cover future parts.

PQ Notice messages need to use the single character part identifier to key the dictionary. Then, at a higher level, it can then be translated into something more useful.

postgresql.api.Message instances will also need to carry the original Notice/Error message..

Currently, when the typio modules are loaded, decimal, xml.etree, and datetime are loaded immediately. These modules and their dependencies are substantial in size so it would be beneficial to only load them when the I/O routines are requested.

Refactor .protocol.client3.Connection to manage Transactions using a Queue.

Currently, Connections can only handle one Protocol Transaction at a given time. This requires that each Protocol Transaction consumes at least one round trip. With a Queue based implementation, the Connection should keep track of which Protocol Transactions have had their Protocol Messages sent to the server, and which ones have not. This would allow multiple Protocol Transactions to be started without waiting for the prior to finish.

Prior, or currently, xact3.Transaction took steps to allow multiple synchronize messages to be sent within a given Protocol Transaction. At one point, the intention was to allow the transaction to be composed so that if grouping were desired, it would be done inside a single Protocol Transaction. However, the difficulty of distinguishing whose response messages belongs to what Protocol Transaction Controller stopped this from being practical. Not to mention, this would cause more significant refactoring of the Controllers.

Per-Hannu's talk,

Add functionality for automatic "remoting" of local Python code.

This feature will depend on a pg-python installation on the remote end.

It covers the properties, but some examples would help.

Not sure how. =(

8.3 added the ability to lookup connection parameters using LDAP with libpq.

http://www.postgresql.org/docs/8.3/static/libpq-ldap.html
http://python-ldap.sourceforge.net/

A connection-to-connection COPY management object which provides efficient transmission of COPY data.

This object will use a mutable buffer in conjunction with a function that will identify the current COPY state. Multiple target connections will need to be supported. Exceptions thrown by this object's main method will need to be generalized into a exception identifying the target that caused the failure. This will be one component in order to allow the user to craft fault tolerance capabilities. The next component is the ability to dynamically remove a target from the copy manager. This will allow the COPY to continue after the fault was identified as an acceptable risk.

Would be nice to be able to one-shot the pg-python initialization code.

postgresql.backend.pl ?

The gid argument enabled a 2pc transaction. When given a gid, the transaction object required that the prepare() method be used before committing on exit. This was intended to provide an abstraction, but use in any distributed transaction manager is likely to lead to more pain than it's worth. For v1.0, the gid documentation will simply be removed, and in 1.1, we'll probably throw a deprecation warning if it's a provided. Subsequent versions will just throw the TypeError as the feature will not exist.

Currently it's not checking things like xsize for wrapping. If a user decides cat some gig size messages, it could be problematic.