In the transaction, how to pre-generate the sql query all at once?

[kamelzcs]

For example, in the relational table, Pet table has a owner property which references a Person table. If now I want to insert into the two tables such fact: Bob owns a cat called Kitty.

It is more easy to do it in two steps:

1. Insert Bob into the Person table and returns the owner id back.
2. Insert Kitty + owner id into the Pet table.

But now the transactions only accept pre-generate sql which means the return value could not be used. Does it mean, momoko prefers to write User defined functions, and put such kind of logic onto the server side or write complex functions on the client side which could do complex logic within one query?

[haizaar]

You have two options here:

1. Use CURRVAL in your SQL code. Something like

   INSERT INTO Person (name) VALUES ("Bob");
   INSERT INTO Pet (name, owner_id) VALUES ("Kitty",
                                           CURRVAL(pg_get_serial_sequence('Person','id'));

   More info here: http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

2. Use manual connection management - acquire connection from Momoko and run commands one by one, committing them in the end. More details: http://momoko.readthedocs.org/en/latest/tutorial.html#manual-connection-management

[kamelzcs]

1. Commands like

   momoko.Op(self.db.execute, INSERT INTO Person (name) VALUES ("Bob"))
   momoko.Op(self.db.execuet, INSERT INTO Pet (name, owner_id) VALUES ("Kitty",
                                         CURRVAL(pg_get_serial_sequence('Person','id')))

   would not work, as the manual connection management is in need.

2. It works. But I found it difficult to design API for transaction support, especially on the nested transaction case. As it is not always easy to flat all the generated sql command on one single layer, then use it in the transaction way like Momoko.

For example,

#in fileAB.py
def updateA_and_B(connection):  
     momoko.Op(connection.execute, 'BEGIN')
     updatetableA(connection);
     updatetableB(connection);
     momoko.Op(connection.execute, 'COMMIT') or momoko.Op(connection.execute, 'Rollback')

#in fileCD.py
def updateC_and_D(connection): 
     momoko.Op(connection.execute, 'BEGIN')
     updatetableC(connection)
     internal_calculation()
     updatetableD(connection)
     momoko.Op(connection.execute, 'COMMIT') or momoko.Op(connection.execute, 'Rollback')

Then, in the fileE I want to create a new transaction which would compose updateA_and_B and updateC_and_D. But I cant do it directly like:

#fileE
def updateA_B_C_D(connection): 
     momoko.Op(connection.execute, 'BEGIN')
     updateA_and_B(connection)
     internal_calculation()
     updateC_and_D(connection)
     momoko.Op(connection.execute, 'COMMIT') or momoko.Op(connection.execute, 'Rollback')

or flat all the update into quries = [updatetableA, updatetableB, updatetableC, updatetableD] then use momo.transaction(quries). As the updateA_and_B and update_C_and_D are supposed to execute(which may do some internal calculation between the single executions) which doe not only generate sql functionally.

As BEGIN transaction command would run several times. In order to avoid this, duplicate jugement like try, except,finally and re-raise exception is in need in each update_A_and_B() and update_C_and_D() functions.

For sync-ORMs, they have@commit_on_success such kind of decorators, but for coroutine tornado, it would not be possible to do it.

Is the flat query way more convenient or try, except,finally and re-raise exception more reasonable?

[haizaar]

You can easily write your own @commit_on_success decorator. Something like this:

from functools import wraps
def commit_on_success(func):
    @wraps(func)
    def wrapper(self, *args, *kwargs):
        connection = yield momoko.Op(self.db.getconn)
        with self.db.manage(connection):
            yield momoko.Op(connection.execute, "BEGIN")
            kwargs["connection"] = connection
            try:
                yield from func(*args, **kwargs)
            except:
                yield momoko.Op(connection.execute, "ROLLBACK")
            else:
                yield momoko.Op(connection.execute, "COMMIT")
    return wrapper

And then use it like follows:

@gen.coroutine
@commit_on_success
def get(self, connection=None):
    yield momoko.Op(connection.execute, "SELECT 'Hi'")
    yield from fileAB.updatetableA(connection)
    ...

yield from appeared only in python 3.3 so you will need to write code to pull your generators manually in earlier versions.

[kamelzcs]

I thought the putconn method wrongly to be an async function. As there is no way to do some coroutine thing in an exit function which is in a context manager.

Thanks for your patience. I think your way is just great.

[haizaar]

You are welcome.

May be putconn documentation can be improved to explicitly state this this is sync method. Care to submit push a request for it?

[kamelzcs]

My pleasure.

[kamelzcs]

Finally I made it like this:

def commit_on_success(func):
    @gen.coroutine
    @wraps(func)
    def wrapper(cls, *args, **kwargs):
        result = None
        if 'connection' in kwargs:
            connection = kwargs.get('connection')
            try:
                result = yield func(cls, *args, **kwargs)
            except psycopg2.Error as rollback_error:
                raise rollback_error
        else:
            pool = cls.get_pool()
            connection = yield momoko.Op(pool.getconn)
            with pool.manage(connection):
                yield momoko.Op(connection.execute, "BEGIN")
                kwargs['connection'] = connection
                try:
                    result = yield func(cls, *args, **kwargs)
                except psycopg2.Error as rollback_error:
                    logger.warn('rollback_error ' + str(rollback_error))
                    yield momoko.Op(connection.execute, "ROLLBACK")
                else:
                    yield momoko.Op(connection.execute, "COMMIT")
                raise gen.Return(result)
    return wrapper

And use it as follows:

    @classmethod
    @commit_on_success
    @gen.coroutine
    def test_decorator_correct(cls, connection=None):
        yield momoko.Op(connection.execute, "SELECT 'Hi'")
        result = yield fileAB.updatetableA(connection)
        raise gen.Return(result)

In this way, it is possible to get the return value from the callee.

[haizaar]

Congrats.

Notes:

• you do not need @gen.coroutine twice - just put it above @commit_on_success
• I suggest using yield from in decorator to allow nesting
• you can optimize it: check if connection was provided and if not just get connection, run BEGIN and continue with the same code path

[kamelzcs]

Thanks for your review.

I found the key part lies in return, which is the key to use the side effect to do successive query. I think maybe it is a good idea to use subquery as many as necessary. But sometimes do queries successively is in need, such as some data transform tasks.

• Your suggested way works as long as there is no return value in the commit_on_success(func). As I am using the Python 2.7, there is no way to return value back in a generator, so in order to get the return value back, I have to make the decorated function be a Future object.
• The connection parameter is the key to allow for nested transaction. If the commit_on_success is not provided with any connection param, then it would start a new transaction, otherwise, it means it is now in a transaction, then it only needs to raise exception to the outer-layer or rollback itself if savepoint is in need.