Re: Psycopg2 and LIXA

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.