Forcing commit to get triggered data updated

[Juan Antonio Ibáñez]

Hello,

   I have one model object for an account table. I have one DB trigger which updates acc balance each time I insert one movement into the acc. I am trying to get updated balance after insert it inside the same controller method but I get always old balance. Is there any whay to force the commit and to get the updated balance?

Regards

[Carlos Daniel Ruvalcaba Valenzuela]

TurboGears uses a transaction manager whose default behaviour is to
commit if the function (view) is successful or to rollback if the
function fails (if it throws an exception or returns an http error
code), you can also tap the manager and commit/abort transactions
explicitely by importing the module transaction:

impor transaction

class MyController(BaseController):
def index(self):
# Your code here
transaction.commit()

For more info (albeit a bit incomplete) check the docs:

http://www.turbogears.org/2.2/docs/main/Config/SQLAlchemy.html

Regards,
Carlos Daniel Ruvalcaba Valenzuela

[Craig Small]

On Tue, Sep 18, 2012 at 02:49:30AM -0700, Juan Antonio Ib��ez wrote:
> I have one model object for an account table. I have one DB trigger
> which updates acc balance each time I insert one movement into the acc. I

I needed to do something like this to obtain the new id for an item
I created. I used DBSession.flush() and DBSession was imported

from foo.model import DBSession

(add some stuff into the database)
DBSession.add(newthing)
DBSession.flush()

This was done on the command line though.

--
Craig Small VK2XLZ http://enc.com.au/ csmall at : enc.com.au
Debian GNU/Linux http://www.debian.org/ csmall at : debian.org
GPG fingerprint: 5D2F B320 B825 D939 04D2 0519 3938 F96B DF50 FEA5

[Juan Antonio Ibáñez]

Commiting the transaction manually disables the possibility to get data after that getting one error as:

Instance <User at 0xa9d782c> is not bound to a Session; attribute refresh operation cannot proceed

Regards

[Michael Pedersen]

On Wed, Sep 19, 2012 at 7:12 AM, Juan Antonio Ibáñez
<juani...@gmail.com> wrote:
> Commiting the transaction manually disables the possibility to get data
> after that getting one error as:
>
> Instance <User at 0xa9d782c> is not bound to a Session; attribute refresh
> operation cannot proceed

This is why I, personally, go with Craig Small's approach:
DBSession.flush(). It does not close out the transaction, the session
continues to work, and I can get at the data that I need.

--
Michael J. Pedersen
My Online Resume: http://www.icelus.org/ -- Google+ http://plus.ly/pedersen
Google Talk: m.ped...@icelus.org -- Twitter: pedersentg

[Alessandro Molina]

Every object you retrieved before committing the session must be
linked again to a session as the session gets removed when committing.
So you must call DBSession.merge(object, load=False) on all the
objects you had before committing the transaction.

Usually you can avoid that by using DBSession.flush instead of
transaction.commit as other said.

> --
> You received this message because you are subscribed to the Google Groups
> "TurboGears" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/turbogears/-/ge5rQaZ8bqQJ.
>
> To post to this group, send email to turbo...@googlegroups.com.
> To unsubscribe from this group, send email to
> turbogears+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/turbogears?hl=en.

[Juan Antonio Ibáñez]

But it seems the trigger doesn't gets executed until the transaction is commited so I get always old balance instead new one

[Michael Pedersen]

On Wed, Sep 19, 2012 at 1:02 PM, Juan Antonio Ibáñez
<juani...@gmail.com> wrote:
> But it seems the trigger doesn't gets executed until the transaction is commited so I get always old balance instead new one

Ah, yes, this will be a problem regardless of your database, and it's
not one that I know of a solid way to solve. The real problem is that
your database trigger will take an unknown time to run. In testing, it
could (and probably will) be under a second. In production, depending
on the amount of data and the load on the server, it could take
minutes or even hours. Web browsers will give up after a limited
amount of time, reporting that the remote web server is not
responsive.

I've only got one option, and I really don't like it.

Set up some sort of notification from the server to your app, so that
the database trigger will notify when the calculation is done. The app
can then hold the information in memory, waiting for the web browser
to request it. In the meantime, the browser periodically queries your
app to see if there is an updated balance, displaying "updating" until
it gets a result.

[Juan Antonio Ibáñez]

Changing the trigger from AFTER trigger to BEFORE trigger seems to work ok

[Michael Pedersen]

On Thu, Sep 20, 2012 at 1:51 AM, Juan Antonio Ibáñez
<juani...@gmail.com> wrote:
> Changing the trigger from AFTER trigger to BEFORE trigger seems to work ok

I don't know that I would have thought of that. Much simpler. Congrats :)