Thread: Integration of Psycopg with XTA

Hi Christian,

first of all I don't like SWIG at all (having used it myself to create 
Python bindings to the Ogre3D library). It makes too easy to depend on 
the internal state of the C library instead of abstracting it. A Python 
function that expects a C pointer (PQconn*) as its argument is... 
ridiculous?

Anyway, I think that if Python has a way to encapsulate a C a pointer 
and pass it unmodified from psycopg2 to XTA we'd have no problems 
exposing it on the connection. Something like connection._raw_pgconn, 
with a name horrible enough to make sure nobody tries to use it without 
knowing what she is doing. I don't think that exposing it just as an 
integer is enough, but I can be wrong here.

federico


On 09/25/2018 10:57 PM, Christian Ferrari wrote:
> Dear All,
> XTA (XA Transaction API, http://www.tiian.org/lixa/XTA.html) is a new 
> API that has been developed inside the LIXA project to support two phase 
> commit transactions in the context of FaaS (Function as a Service) and 
> microservice oriented, polyglot applications.
> 
> The API already supports C and C++ languages; it aims to support many 
> more, at the bare minimum Python, PHP and Java.
> I'm currently working on supporting Python with PostgreSQL and MySQL, 
> this mail thread is related to Python/PostgreSQL.
> 
> XTA is implemented in C language and XTA for Python is generated using 
> SWIG: I would like to repeat the approach for all the languages that 
> provides drivers derived from libpq-fe.h
> 
> Now the request for help: XTA needs to enlist all the resource managers 
> (here PostgreSQL) to manage them using 2 phase commit, basically it 
> requires a pointer (PGconn *) that must be passed to 
> PostgresqlXaResource constructor 
> (http://www.tiian.org/lixa/manuals/xta/CPP/classxta_1_1PostgresqlXaResource.html) 
> to create an XTA object associated to an already opened PostgreSQL 
> connection.
> 
> Here are the basic steps of a Python example program 
> (https://github.com/tiian/lixa/blob/master/doc/examples/xta/python/example_xta_sa21.py):
> 
> # initialize XTA environment
> Xta_Init()
> 
> # create a new PostgreSQL connection
> # Note: using PostgreSQL Psycopg2 functions
> rm1 = psycopg2.connect("dbname=testdb")
> 
> # create a new MySQL connection
> # Note: using MySQLdb functions
> rm2 = MySQLdb.connect("localhost", "lixa", "", "lixa")
> 
> # create a new XTA Transaction Manager object
> tm = TransactionManager()
> 
> # create an XA resource for PostgreSQL
> # second parameter "PostgreSQL" is descriptive
> # third parameter "dbname=testdb" identifies the specific database
> #
> # how to retrieve PGconn * from rm1?!
> xar1 = PostgresqlXaResource(rm1.conn, "PostgreSQL", "dbname=testdb")
> 
> Looking at the last statement, the stack is:
> - XTA native C library expects "PGconn *" to register the connection handler
> - XTA C++ wrapper expects "PGconn *" as the first parameter to construct 
> the object
> - XTA Python (SWIG generated) wrapper expects a "SWIG generated" PGconn 
> * pointer
> - psycopg2.connect does not provide me something equivalent to PGconn *, 
> at least it seems so to me.
> 
> Stated that replacing "PGconn *" with "void *" in the API stack would 
> not be an issue, the question is: what's the best way to retrieve 
> something thatcan be transformed in a C pointer that could be passed to 
> XTA constructor?
> 
>  From my point of view, this new API layout should solve most of the 
> issues presented by the old style TX API that I explored some years ago: 
> https://grokbase.com/t/postgresql/psycopg/122b1re71w/psycopg2-and-lixa
> 
> Thanks in advance for your help.
> Regards,
> Ch.F.
> 
> 


-- 
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
    I filosofi son come i sociologi: il mondo non lo capiscono. -- A.R.M.

On Tue, Sep 25, 2018 at 9:57 PM Christian Ferrari <camauz@yahoo.com> wrote:

> Stated that replacing "PGconn *" with "void *" in the API stack would not be an issue, the question is: what's the
bestway to retrieve something that can be transformed in a C pointer that could be passed to XTA constructor?
 

This is a hack I passed some time ago to an ukulele friend to get the
PGconn pointer out of the connection object:
<https://gist.github.com/dvarrazzo/b7c8f050bbd39dd2c104>. You may need
trasposing it for guitar though.

-- Daniele

On 09/26/2018 10:36 PM, Christian Ferrari wrote:
[snip]
> I'm not a Python developer at all, but I see usage of htonl() 
> function... if it has the same meaning of the C function it could be 
> influenced by 32/64 architecture differences.
> 
> Do you have any suggestions?
> 
> What do you think about Federico's proposal to expose the pqconn 
> pointer? Could ctypes "c_void_p" a viable option?

Would a PyCapsule work? https://docs.python.org/2/c-api/capsule.html

If yes, I already have some code in place to expose the PGconn* as a 
capsule. I can just push it on my repo.

federico

-- 
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
           The reverse side also has a reverse side.  -- Japanese proverb

On Wed, Sep 26, 2018 at 9:36 PM Christian Ferrari <camauz@yahoo.com> wrote:
>
> Dear Daniele,
> thank you for providing the hack: it's a starting point to check integration feasibility.
> Unfortunately it does not work in my environment:
> Ubuntu LTS 14.04 64 bit
> python 2.7.6

>   File "pqconn_hack.py", line 24, in getpqconn
>     pqconn = htonl(int(hex[ver_off + 8:ver_off + 16], 16))
> TypeError: 'builtin_function_or_method' object has no attribute '__getitem__'

This is an error: it should have been 'mem' instead of 'hex':

    pqconn = htonl(int(mem[ver_off + 8:ver_off + 16], 16))

-- Daniele

On 09/27/2018 09:57 PM, Christian Ferrari wrote:
> Thanks, for the fix, now the function works, but unfortunately it's a 
> dead end in the SWIG realm...Here's the statement: 
> http://www.swig.org/Doc2.0/SWIGDocumentation.html#Python_nn18
> [...]
> *However, the inverse operation is not possible, i.e., you can't build a 
> SWIG pointer object from a raw integer value.*
> [...]
> 
> PyCapsule https://docs.python.org/2/c-api/capsule.html, suggested by 
> Federico seems a more viable approach:
> https://bfroehle.com/2011/07/18/python-capsules/
> 
> If Psycopg was able to retrieve a PyCapsule, XTA should be able to use 
> it and the Python interpreter would be a gateway between two C/C++ 
> distinct modules (Psycopg and XTA).
> I'm going to perform some experiments in the XTA wrapper before coming 
> back to this mail thread.

If you want the capsule, just pull from my report:

https://github.com/fogzot/psycopg2/tree/feature-expose-pgconn

and use the feature-expose-pgconn branch.

federico

-- 
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
  Everything will be OK at the end. If it's not OK, it's not the end.
                                                               -- Unknown

On 10/02/2018 10:07 PM, Christian Ferrari wrote:
> Hi Federico,
> I have been able to test _raw_pgconn using SWIG, as documented here 
> https://sourceforge.net/p/swig/mailman/message/27919299/ , the trick to 
> use PyCapsule and SWIG together is to specifically map the type with a 
> directive like this one:
> 
> %typemap(in) PGconn * {
>    $1 = (PGconn *) PyCapsule_GetPointer($input, 
> "psycopg2.connection._raw_pgconn");
> }
> 
> This is interesting because PyCapsule is for sure a "clean" way to pass 
> the pointer from Psycopg to XTA.
> 
> 
> A couple of questions to understand how to implement it as a re-usable 
> deliverable.
> 
> In current branch, _raw_pgconn is a property, it's not a method, I have 
> to retrieve it as below: 
> 
> pgconn = rm1._raw_pgconn
> xar1 = PostgresqlXaResource(pgconn, "PostgreSQL", "dbname=testdb")
> 
> what do you think about a "getter method":
> 
> pgconn = rm1._raw_pgconn()
> 
> the name of the method does not really matter to me.
> 
> _raw_pgconn() create a Capsule object that should be destroyed.

Right now the getter creates a new capsule at every invocation so 
transforming it into a method should be straightforward. I'll do it 
later today so that you can test.

 > What do you think about a factory to create and destroy a PyCapsule that
 > contains the PGconn pointer?

Mm. I don't understand what you need here? Can you show an example of usage?

federico

-- 
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
  Credo fermamente che da qualche parte, in una scatola ci sia un gatto
   che non è vivo ne morto. Credo anche che se i fisici non si sbrigano
   a dargli una scatoletta, ben presto sarà solo morto.
                               -- adattato da una frase di Sam Black Crow

On Wed, Oct 3, 2018 at 1:28 PM Federico Di Gregorio <fog@dndg.it> wrote:

> > This is interesting because PyCapsule is for sure a "clean" way to pass
> > the pointer from Psycopg to XTA.

Fog, maybe it's worth adding the capsules for PGconn and PGresult to
the objects interface and document them? Can do that for psycopg 2.8.

-- Daniele

On 10/03/2018 02:37 PM, Daniele Varrazzo wrote:
> On Wed, Oct 3, 2018 at 1:28 PM Federico Di Gregorio<fog@dndg.it>  wrote:
> 
>>> This is interesting because PyCapsule is for sure a "clean" way to pass
>>> the pointer from Psycopg to XTA.
> Fog, maybe it's worth adding the capsules for PGconn and PGresult to
> the objects interface and document them? Can do that for psycopg 2.8.

If it works I'd add it (and document it as an "internal" extension point 
used by other C-level modules) for 2.8, yes.

By PGresult you mean adding it on the cursor, or the connection?

federico

-- 
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
  Io non sono romantica. La candelina sul tavolo mi vede e si spegne.
                                                       -- sisterconfusion

On Wed, Oct 3, 2018 at 1:44 PM Federico Di Gregorio <fog@dndg.it> wrote:
>
> On 10/03/2018 02:37 PM, Daniele Varrazzo wrote:
> > On Wed, Oct 3, 2018 at 1:28 PM Federico Di Gregorio<fog@dndg.it>  wrote:
> >
> >>> This is interesting because PyCapsule is for sure a "clean" way to pass
> >>> the pointer from Psycopg to XTA.
> > Fog, maybe it's worth adding the capsules for PGconn and PGresult to
> > the objects interface and document them? Can do that for psycopg 2.8.
>
> If it works I'd add it (and document it as an "internal" extension point
> used by other C-level modules) for 2.8, yes.
>
> By PGresult you mean adding it on the cursor, or the connection?

Yes, i'd say a _pgconn attribute or _get_pgconn() method on the
connection, a _pgresult thing on the cursor.

By the way a read-only attribute as per your implementation seems
appropriate to me. Christian, why do you think a function would be
better?


-- Daniele

On 10/03/2018 02:52 PM, Daniele Varrazzo wrote:
> On Wed, Oct 3, 2018 at 1:44 PM Federico Di Gregorio<fog@dndg.it>  wrote:
>> On 10/03/2018 02:37 PM, Daniele Varrazzo wrote:
>>> On Wed, Oct 3, 2018 at 1:28 PM Federico Di Gregorio<fog@dndg.it>   wrote:
>>>
>>>>> This is interesting because PyCapsule is for sure a "clean" way to pass
>>>>> the pointer from Psycopg to XTA.
>>> Fog, maybe it's worth adding the capsules for PGconn and PGresult to
>>> the objects interface and document them? Can do that for psycopg 2.8.
>> If it works I'd add it (and document it as an "internal" extension point
>> used by other C-level modules) for 2.8, yes.
>>
>> By PGresult you mean adding it on the cursor, or the connection?
> Yes, i'd say a _pgconn attribute or _get_pgconn() method on the
> connection, a _pgresult thing on the cursor.
> 
> By the way a read-only attribute as per your implementation seems
> appropriate to me. Christian, why do you think a function would be
> better?

I guess the capsule is meant to be consumed by the other API and using a 
method makes is explicit that a new object is instantiated every time. I 
really don't care about this, let's do whatever is more Pythonic.

federico

-- 
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
  I did appreciate the irony that I was whining about encoding issues on
   a mailing list that was unable to show those chars, too.
                                  -- Antti S. Lankila to mono-devel-list@

On 10/03/2018 10:50 PM, Christian Ferrari wrote:
[snip]
>  > I guess the capsule is meant to be consumed by the other API and using a
>  > method makes is explicit that a new object is instantiated every time. I
>  > really don't care about this, let's do whatever is more Pythonic.
> 
> Dear Daniele & Federico,
> I'm not a "pythonist" at all, I can't provide suggestions about "Python 
> style".
> In oo context, typically I expect to call a method when I need to 
> retrieve something, that's why I proposed a method instead of a property.
> Anyway, my obscure question was related to garbage collection: who is in 
> charge of destroying the capsule and how it must be destroyed?
> Looking at this example:
> 
> pgconn = xx._get_pgconn()
> do_something(pgconn)
> pgconn = yy._get_pgconn()
> 
> will the first capsule be automatically destroyed by the Python runtime 
> when pgconn is assigned to another one (yy....)? Or some explicit 
> statement must be executed?
> If everything is automatically managed by the Python runtime, I will be 
> very happy. If "do_something()" must release the capsule to avoid memory 
> leaks, I have to implement it.

The capsule is a standard reference-counted Python object. If the API 
just use the pointer inside it and does not keep a reference to the 
Python object itself, it will be free'd by the gc when the reference 
count goes to 0. In your example, the first capsule is garbage collected 
when you assign the second one to the same variable "pgconn".

@Daniele, I see that a capsule has an implicit state, "invalid" if the 
pointer inside it is null. Does it makes sense to reuse the same capsule 
and keep a reference to it to be able to invalidate it when the 
connection is closed?

federico

-- 
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
  The only thing I see is if you are pumping so much data into the
   database all the time when do you expect to look at it?
                                                         -- Charlie Clark

On Thu, Oct 4, 2018 at 10:33 AM Federico Di Gregorio <fog@dndg.it> wrote:
> @Daniele, I see that a capsule has an implicit state, "invalid" if the
> pointer inside it is null. Does it makes sense to reuse the same capsule
> and keep a reference to it to be able to invalidate it when the
> connection is closed?

I can take a look at it but I suspect it's a lot of work for a feature
very rarely used.

-- Daniele

On 10/04/2018 12:13 PM, Daniele Varrazzo wrote:
> On Thu, Oct 4, 2018 at 10:33 AM Federico Di Gregorio<fog@dndg.it>  wrote:
>> @Daniele, I see that a capsule has an implicit state, "invalid" if the
>> pointer inside it is null. Does it makes sense to reuse the same capsule
>> and keep a reference to it to be able to invalidate it when the
>> connection is closed?
> I can take a look at it but I suspect it's a lot of work for a feature
> very rarely used.

Nah, I can do it. I was just asking for your opinion. :D

federico

-- 
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
                              Best friends are often failed lovers. -- Me

On Thu, Oct 4, 2018 at 11:24 AM Federico Di Gregorio <fog@dndg.it> wrote:
>
> On 10/04/2018 12:13 PM, Daniele Varrazzo wrote:
> > On Thu, Oct 4, 2018 at 10:33 AM Federico Di Gregorio<fog@dndg.it>  wrote:
> >> @Daniele, I see that a capsule has an implicit state, "invalid" if the
> >> pointer inside it is null. Does it makes sense to reuse the same capsule
> >> and keep a reference to it to be able to invalidate it when the
> >> connection is closed?
> > I can take a look at it but I suspect it's a lot of work for a feature
> > very rarely used.
>
> Nah, I can do it. I was just asking for your opinion. :D

I don't know much about the capsule object, its life cycle etc. Yes,
not a huge deal saving it in the connection state and returning always
the same. I just wonder if it's worth the effort.

-- Daniele