Re: Psycopg2 and LIXA

On Sat, Feb 11, 2012 at 5:37 PM, Christian Ferrari <camauz(at)yahoo(dot)com> wrote:

Hi Christian,

> I am now interested in expanding the LIXA project scope to Python language and Python programmers.
> It seems to me Psycopg2 is a good start point because it wraps the PostgreSQL C API; LIXA too wraps libpq-fe.h
to make
Yes, psycopg is about the only libpq wrapper currently maintained.

> ...
> TX standards specifies the Resource Managers must be opened by the Transaction Manager using "tx_open()" by the Application Program. This is the key point: Psycopg2 supplies its own method to get a valid PostgreSQL session: "psycopg2.connect(...)", while LIXA wants to create it using "lixa.tx_open()": the Transaction Manager must open all the Resource Managers, not only PostgreSQL.
> Using C as the development language, LIXA supplies some convenience methods:
> PGconn *lixa_pq_get_conn_by_rmid(int rmid);
> PGconn *lixa_pq_get_conn_by_pos(int pos);
> PGconn *lixa_pq_get_conn(void);
>
> if there is only one PostgreSQL database, the third one is good enought.

[ a Python client should do:]

> tiian(at)mojan:~/src/swig$ python
> Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56)
> [GCC 4.4.3] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import lixa
>>>> lixa.tx_open()
> 0
>>>> conn = lixa.lixa_pq_get_conn()
>>>> print conn
> <Swig Object of type 'PGconn *' at 0xb77e9638>

...with which he won't do much of course, and it would be great to
have a psycopg connection here. Fine.

> Coming back to Psycopg2,
> psycopg2.connect(...)
> does not return a simple handler to a PostgreSQL connection, but a complex object with additional methods and properties.

Yes, but not much more complex: the Python connection object is a C
struct connectionObject, containing a pointer to the PGconn (see [1]).
Getting the PGconn from the connectionObject is straightforward in C,
but is not exposed to Python.

> Thinking about integration between LIXA and Psycopg2 I'm proposing three different paths:
> 1. patching Psycopg2 substituting "PQconnectdb(...)" with "lixa_pq_get_conn": I don't like this solution because the patch must be managed and tested every time Psycopg2 release something new; I don't think it would be well accepted by the users too

Yes, it wouldn't be a good solution.

> 2. using "psycopg2.extensions - Extensions to the DB API" I have found at this paragraph:
> http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.connection
> it could be viable path, but I'm not able to figure out it nowadays

This is basically equivalent to the above: extensions.connection is
the class whose instances are returned by the psycopg2.connect factory
function, which is required by the DBAPI spec

> 3. it could be very easy to overload the "psycopg2.connect()" method: if it accepted a "PGconn *" too, the integration would be straightforward like: psycopg2.connect([...], lixa.lixa_pq_get_conn() )

The big shortcoming I see in this method is that it takes a Python
object which is a wrapper to the PGconn, to be accessed from C. Swig
is not the only way to create such a wrapper, so I wouldn't like to
bind the psycopg C implementation specifically to swig (note that
psycopg should unpack manually the swig wrapper: it wouldn't be
automatic as psycopg is not swig-generated). A more portable method
would be to have connect() just receive an integer which would be a
pointer to the PGconn... but I wouldn't define it as a robust
interface! An entirely different wrapper for dynamic libraries is the
one provided by ctypes <http://docs.python.org/library/ctypes.html>,
which is part of the Python standard library and requires no code
generation beforehand (two reasons for which it could be considered a
somewhat blessed wrapper).

> What's your opinions and suggestions?

I guess it depends on how do you want your library to be used by the
Python code, what level or transparency you require from it, or
conversely how much explicit you want using lixa to be. One thing I
notice, I don't know how much do you know about it, is that all Python
database modules implement the same basic interface, called DBAPI
<http://www.python.org/dev/peps/pep-0249/>: this interface also
defines how to perform 2-phase commit, and it declares to follow the
same XA X/Open standard lixa implements... although it does with
different methods. I'm afraid I'm no 2PC expert (although I've
implemented the support for such methods in psycopg). So in first
place I wonder if there is a different level at which the libraries
may interoperate, using the DBAPI 2PC-related method. BTW, if lixa
could operate with the generic DBAPI 2-phase interface, it could work
for free not only with psycopg but with any driver implementing such
interface (well, to be honest I don't know how many of them exist
yet...).

Also note that if you force your database connection to have a
specific interface of yours, you would make harder for Python users to
use such connection in conjunction with already written code or third
party libraries: you'd have much more success if you could map the xa
methods to dbapi methods, which means no tx_close() and such (good
Python interfaces tend to differ from good C interface, that's why
swing-generated wrappers are usually poor ones by themselves and
require further wrapping to stop being painful).

Before suggesting any specific solution, I'd like to know what is
between the client function I understand the lixa user should invoke
(lixa_pq_get_conn()) and the function that actually creates a libpq
connection (lixa_pq_open()): I suspect a good solution could be for
the lixa code to create a psycopg connection (which in turn calls
PQconnectdb) and get the PGconn from there, then returning the python
object to the invoking python code. Also, because lixa seems modular,
couldn't you create a "psycopg" module, which would largely use the
same "postgresql" module implementation but would offers methods that
are meaningful for a Python user (i.e. return a connectionObject
instead of a PGconn)?

I believe it is possible for the two libraries to interoperate, but I
think the implementation is only a detail, easy to solve: I'd rather
try to understand the way lixa would be used from Python (creation,
usage, finalization of the connections, of the transactions and the
relation between the two) and derive the implementation from such use
case.

-- Daniele

[1] https://dndg.it/cgi-bin/gitweb.cgi?p=public/psycopg2.git;a=blob;f=psycopg/connection.h;hb=refs/tags/2_4_4#l77