Psycopg2 and LIXA

Dear All,
I'm the founder of LIXA project ( http://sourceforge.net/projects/lixa/ http://lixa.sourceforge.net/manuals/ ) and I'm interested in integrating LIXA with Psycopg2.
LIXA is LIbre XA, a free and open source XA compliant transaction manager: it implements XA interface to deal with the Resouce Managers and implements TX interface to deal with the Application Programs.
LIXA supports PostgreSQL as a Resource Manager and allows to create distributed transaction (2 phase commit) with PostgreSQL, MySQL, Oracle and DB2.
XA and TX are interfaces designed for C (and C++) programs.
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

It's time to describe my issue to ask some help to you.
First of all, I'm not a Python expert.
I've generated a LIXA wrapper using SWIG and it works fine, the usage is something like this (my development environment is based on Ubuntu 10.04):

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
>>> lixa.tx_begin()
0
>>> lixa.tx_commit()
0
>>> lixa.tx_close()
0
>>>

tx_open(), tx_begin(), tx_commit(), tx_close() are the standard functions described in "TX (Transaction Demarcation) Specification" (CAE X/Open standard).
The operations related to the Resource Managers must be put between "tx_begin()" and "tx_commit()".

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. Using the convenience method, the C program becomes:

[...]

    /* open the resource manager(s) */
    if (TX_OK != (txrc = tx_open())) {
        fprintf(stderr, "tx_open error: %d\n", txrc);
        exit(txrc);
    }
    /* retrieve PostgreSQL connection */
    conn = lixa_pq_get_conn();
    /* start a new transaction */
    if (TX_OK != (txrc = tx_begin())) {
        fprintf(stderr, "tx_begin error: %d\n", txrc);
        exit(txrc);
    }

[...]

the Application Program does not use "PQconectdb(...)": the Transaction Manager calls it inside "tx_open()" and the Application Program retrieves the connection using a convenience method (lixa_pq_get_conn).
The same when the connection must be closed using "tx_close()".

Translated to Python, it becomes:

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>
 
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.

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
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
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() )

What's your opinions and suggestions?

Thanks in advance for your help.
Regards,
Ch.F.

-------------------------------------------------------------------
Decent workarounds outperform poor solutions

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

Hi Daniele,
I've greatly appreciated your answer. There is a lot of stuff to work on.
At first glance it seems some of the standard followed by LIXA could hurt some Python de-facto standard, but I think a solution path can be found.

>> Thinking about integration between LIXA and Psycopg2 I'm proposing three different paths:
[...]

>> 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).

At this time I'm looking to SWIG just because it could help me in wrapping LIXA with Python, PHP, Perl and Ruby; without a tool like SWIG, the mileage would probably be too long.
I don't know CTYPE (unfortunately I'm not a Python expert at all) and in the meantime I'm going to discover the power of CTYPE.
I agree with you: if Psycopg2 is not based on SWIG, it will be a bad choice to bind it with SWIG for the sake of this integration.
The idea behind 

psycopg2.connect([...], lixa.lixa_pq_get_conn() )

is: if Psycopg2 could accept the PostgreSQL (libpq-fe) connection from third party supplying an overloaded method, I could integrate LIXA with Psycopg2 without strange tricks and hacks inside LIXA. The type of object an overloaded psycopg2.connect method accepted, should be the best choice for Psycopg2, practical for LIXA.
We could discuss this point in a successive reply because I'm more interested in the following issues.

>> 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.

I think this is the right time to explain some design choices of LIXA.
When I started developing LIXA (3 years ago), I was principally interested in the XA protocol. I studied the official document published by X/Open and discovered XA is a *system* interface: it was designed as a standard API (and protocol) between one Transaction Manager and many Resource Managers. It was not designed to be used by the developers of an Application Program (I'm using the same terminology used by X/Open documentation).
I discovered there was a standard for the interface exposed by a Transaction Manager and invoked by an Application Program: it's name is "TX (Transaction Demarcation) Specification".
In my honest opinion TX is not a marvel, but incidentally it was supported by Encina (now IBM TXSeries) and by Tuxedo (once BEA, now Oracle).
I chose to avoid reinventing the wheel and sticked to TX standard.
The TX API is not complete and does not solve some issues, but it has two interesting features: it's easy to understand (and implement) and it doesn't specify too many restrictions (some issues can be bypassed). Speaking about TX, I would say "it just works".
The TX API was designed for C and COBOL languages: I suppose no one could imagine a crazy guy would try to extend it to Python, PHP, Perl and Ruby that time.

> 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 briefly examined DBAPI, but unfortunately there is a major drawback: DBAPI supplies an API that is equivalent to XA and can be used to implement a Transaction Manager, but it should not be used by an Application Program. If an Application Program had to deal with "prepare" and "recover" verbs, it would implement a Transaction Manager itself.

> 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...).

LIXA already implements all the Transaction Manager logic, and all the
code is C code: extending that logic to deal with Python API is a
complex task with many risks.

> 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).

I don't think this is a real issue: LIXA supports Distributed Transaction Processing using TX API. There's no way to pick-up an Application Program designed for one phase commit and convert it to an Application Program for two phase commit without some changes. The scenario I imagine is the following one:
1. there is an Application Program designed for only one Resource Manager, for example PostgreSQL
2. the same Application Program must be re-engineered to deal with two Resource Managers (PostgreSQL and MySQL) and some transactions must change data inside both Resource Managers (INSERT INTO PostgreSQL, UPDATE MySQL).
If the data was critical, the developer would use a Transaction Manager with 2 phase commit support: LIXA might be a choice.

> 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()):

Using TX API (supplied by LIXA), there are four steps:
1. tx_open()
2. tx_begin()
3. connection handler retrieval
4. business logic (using connection handler, PGconn * for PostgreSQL)

(2. and 3. can be swapped if necessary)
This is one of the key aspects of TX: the connection must be opened by the Transaction Manager, the configuration necessary to open the connection must be managed by the system engineers in charge of the Transaction Manager.
When you use TX, you don't have to know how the Resource Managers will be reached. TX standard does not specify how the Transaction Manager implements the behavior, it only specifies it's a task of the Transaction Manager.
LIXA implementation uses a flexible approach: a configuration file contains some profiles, every profile references a set of Resource Managers and theirs configurations. If the Application Program does not specify the LIXA_PROFILE environment variable, it will use the default (first) available profile; if the Application Program specifies the LIXA_PROFILE environment variable, it will use the desired profile. Some commercial Transaction Managers does not allow such flexibility: they behave like LIXA with a single configured profile.
The X/Open (TX) standard specifies the connection must be opened by the Transaction Manager for many reasons; there are at least two really important reasons:
1. the Resource Manager can not be used independently by the Transaction Manager (an Application Program could create a session, perform some work and then pass it to the Transaction Manager creating a potential inconsistent state)
2. the Transaction Manager inspects the Resource Managers at tx_open() time to perform automatic recovery of previous prepared/recovery pending transactions

The method "lixa_pq_get_conn()" is a work-around necessary for PostgreSQL and MySQL (lixa_my_get_conn()). Oracle and DB2 do not need such a work-around: they supply specific API; PostgreSQL and MySQL *do* *not* *implement* *standard* *XA*, but only some proprietary extensions that can be used to arrange an XA like interface (LIXA provides that stubs too).

> 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)?

Wrapping "lixa_pq_get_conn()" (that's C code) with a method retrieving a different type could be done at C level and Python level as well. Where could I found the exact "connectionObject" specification? Could you point me to the right direction at first step?

> 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

There's probably another interesting detail: LIXA implementation of TX API is thread safe and "thread related".
Two distinct threads must invoke two distinct "tx_open()/tx_close()" functions.
The transactional state must not be shared between distinct threads because the state is indexed using the thread id (TX functions do not pass a reference to the state, so it must be implicitly managed by the API).
This is an example:
Thread1                  Thread2
tx_open()                tx_open()
tx_begin()               tx_begin()
some stuff               some stuff
tx_commit()              tx_rollback()
tx_begin()               tx_close()
some stuff
tx_rollback()
tx_close()
the connection handler must not be passed by Thread1 to Thread2 and vice versa.

Thanks in advance.
Ch.

Hi Christian,

On 12/02/12 23:02, Christian Ferrari wrote:
[snip]
> (2. and 3. can be swapped if necessary)
> This is one of the key aspects of TX: the connection must be opened by the Transaction Manager, the configuration necessary to open the connection must be managed by the system engineers in charge of the Transaction Manager.
> When you use TX, you don't have to know how the Resource Managers will be reached. TX standard does not specify how the Transaction Manager implements the behavior, it only specifies it's a task of the Transaction Manager.
> LIXA implementation uses a flexible approach: a configuration file contains some profiles, every profile references a set of Resource Managers and theirs configurations. If the Application Program does not specify the LIXA_PROFILE environment variable, it will use the default (first) available profile; if the Application Program specifies the LIXA_PROFILE environment variable, it will use the desired profile. Some commercial Transaction Managers does not allow such flexibility: they behave like LIXA with a single configured profile.
> The X/Open (TX) standard specifies the connection must be opened by the Transaction Manager for many reasons; there are at least two really important reasons:
> 1. the Resource Manager can not be used independently by the Transaction Manager (an Application Program could create a session, perform some work and then pass it to the Transaction Manager creating a potential inconsistent state)
> 2. the Transaction Manager inspects the Resource Managers at tx_open() time to perform automatic recovery of previous prepared/recovery pending transactions
>
> The method "lixa_pq_get_conn()" is a work-around necessary for PostgreSQL and MySQL (lixa_my_get_conn()). Oracle and DB2 do not need such a work-around: they supply specific API; PostgreSQL and MySQL *do* *not* *implement* *standard* *XA*, but only some proprietary extensions that can be used to arrange an XA like interface (LIXA provides that stubs too).

To put it bluntly, I don't like this kind of design at all and I can
anticipate that just like me other Python programmers wouldn't like it
too. If you need a work-around (lika_pq_get_conn()) I don't see why not
provide an hook to register a connection; i.e., lixa_pg_set_conn() or
something like that. LIXA is conceptually above database adapters
(should know about their semantics) so it does make sense for it to know
about psycopg, not the reverse.

Anyway what we can do is to exporse (from C? from Python?) a connection
function that takes a void* and converts it to a PGconn*. It isn't solid
and I still don't like it but it is something that should work.

The ideal solution would be for the LIXA Python adapetr to know about
psycopg (and other adapters) and accept a native Python object to be
added to the current resource list.

federico

On Sun, Feb 12, 2012 at 10:02 PM, Christian Ferrari <camauz(at)yahoo(dot)com> wrote:

> I briefly examined DBAPI, but unfortunately there is a major drawback: DBAPI supplies an API that is equivalent to XA and can be used to implement a Transaction Manager, but it should not be used by an Application Program. If an Application Program had to deal with "prepare" and "recover" verbs, it would implement a Transaction Manager itself.

Isn't LIXA a Transaction Manager, and as such supposed to use the
tpc_*() methods, while an application would use the other connection
methods?

>> 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)?
>
> Wrapping "lixa_pq_get_conn()" (that's C code) with a method retrieving a different type could be done at C level and Python level as well. Where could I found the exact "connectionObject" specification? Could you point me to the right direction at first step?

An example of creation of a connection is the psyco_connect() function
in psycopgmodule.c: the most accessible way in C would be

connectionObject *conn
conn = (connectionObject *)PyObject_CallFunction((PyObject
*)&connectionType, "s", dsn);

where dsn is the char[] containing the libpq connection string.

I assume the function should be called with a Python interpreter well
initialized, but if lixa is imported by a Python process and the above
code is called by a function wrapped in a Python call I assume all the
conditions required are met. Of course everything is pretty
experimental: psycopg doesn't offer (yet) a C api, nor the import
file are designed to be used outside the library: if the approach
works we can provide a Python C API properly done.

-- Daniele

On 13/02/12 10:10, Daniele Varrazzo wrote:
> I assume the function should be called with a Python interpreter well
> initialized, but if lixa is imported by a Python process and the above
> code is called by a function wrapped in a Python call I assume all the
> conditions required are met. Of course everything is pretty
> experimental: psycopg doesn't offer (yet) a C api, nor the import
> file are designed to be used outside the library: if the approach
> works we can provide a Python C API properly done.

That's what I was thinking too but then psycopg users will lose custom
connection (and cursor) objects. :/ IMHO the best approach would be for
LIXA to extend its concept of "profile" to include connection factories
and let Python code to register custom profiles and connection
factories. I understand that is probably alien to what LIXA does now but
would increase acceptance by Pythonistas.

federico

Daniele & Federico,
in the past few days we have discussed about a possible integration between LIXA and Psycopg2.

>> I assume the function should be called with a Python interpreter well
>> initialized, but if lixa is imported by a Python process and the above
>> code is called by a function wrapped in a Python call I assume all the
>> conditions required are met. Of course everything is pretty
>> experimental: psycopg doesn't offer (yet) a C api, nor the import
>> file are designed to be used outside the library: if the approach
>> works we can provide a Python C API properly done.
>
> That's what I was thinking too but then psycopg users will lose custom
> connection (and cursor) objects. :/ IMHO the best approach would be for LIXA to
> extend its concept of "profile" to include connection factories and
> let Python code to register custom profiles and connection factories. I
> understand that is probably alien to what LIXA does now but would increase
> acceptance by Pythonistas.

After some replies, two possible solutions emerged:

1. the LIXA Transaction Manager open the connection(s) to the database(s) and pass it/them to Python libraries with a dedicated method. LIXA could open a DB API connection type (Python) and map it to C for its own usage; casting a native C connection type (PGconn *) seems the worst case for Psycopg2 integration.

PROS:
a. the role of the Transaction Manager is not subverted: the T.M. manages the connections to the Resource Managers

b. LIXA logic requires minor technical updates, necessary to deal with a cross-language environment (C/Python), but no functional change is involved
c. Python developers could use familiar connectionObject objects

CONS:
c. Python developers must change a little of their logic when using the LIXA Transaction Manager (it happens to C/C++ programmers for Encina/TXSeries/Tuxedo and Java programmers for Jboss, WebSphere, WebLogic, any JEE application server)

2. the Application Program opens the connection(s) to the database(s) and pass it/them to LIXA Transaction Manager. LIXA should register the connections instead of directly manage them; the connection would be "connectionObject".
PROS:
a. highest acceptance level from Python community
CONS:
b. changing the role of the Transaction Manager and the Application Program with respect of well consolidated DTP environments like C and Java could be incautious: some consistency and/or robustness issues might emerge in the future because it's basically a not explored battlefield (the "happy path" seems to work, but guessing what happens when a crashed system restarts is a completely different matter)
c. LIXA logic requires a lot of rework: a concept of "dynamic profile" is probably necessary; the logic related to consistency and isolation must be re-designed to avoid undesired automatic recoveries

d. some XA compliant Resource Managers could complain: when you use Oracle and DB2 for XA Distributed Transaction Processing, you will not open a connection using the standard function, you will use a specific XA function (xa_open) available inside the "XA switch file" (a dynamically loaded module) supplied by the vendor. It may be the functions are interchangeable, it may not: only experiments will give the right answer. Unfortunately, dealing with proprietary software outside documented use cases (xa_open usage) could be a nightmare.

 I do think the discussion was really interesting and a lot of important information was retrieved.

LIXA can not live alone, nor it can live supporting only PostgreSQL: by design LIXA must integrate with as many Resource Managers as possible. LIXA must support as many programming languages as possible, too.

I think it's time to suspend this discussion and transfer it to the teams are developing equivalent modules for other Resource Managers.
In the C/C++ environment, LIXA works with PostgreSQL, MySQL, Oracle and DB2; I will poll MySQL, Oracle and DB2 Python guys to pick-up their feedbacks.

Stay tuned, I will come back in the next few days/weeks.

Ch.F.

On Mon, Feb 13, 2012 at 9:50 PM, Christian Ferrari <camauz(at)yahoo(dot)com> wrote:

> I think it's time to suspend this discussion and transfer it to the teams are developing equivalent modules for other Resource Managers.
> In the C/C++ environment, LIXA works with PostgreSQL, MySQL, Oracle and DB2; I will poll MySQL, Oracle and DB2 Python guys to pick-up their feedbacks.

The DB-SIG could be a good party to get feedback from: they could also
decide to revise the 2PC specs if you show them that a different
interface would do better in a realistic use case (I've got the
feeling that 2PC is rarely used in Python systems, judging on how many
interfaces implement it).

-- Daniele