db.commit documentation

[Anthony]

In the documentation on commit and rollback, it says:

No create, drop, insert, truncate, delete, or update operation is actually committed until web2py issues the commit command.

However, at least for SQLite, this does not appear to be true for create and drop operations -- those are executed immediately, even in the shell. If I'm not mistaken, at least some other backends also do create and drop operations without requiring a commit. Can someone confirm?

The documentation also says:

In models, views and controllers, web2py does this for you, but in modules you are required to do the commit.

I'm not sure the above is quite the right way to put it. The distinction is not between models/views/controllers vs. modules, but between operations that happen during an HTTP request (whether or not in a module) and those that don't (whether or not in a model/view/controller). In other words, if code in a module does an insert during an HTTP request, the insert will be committed automatically. By contrast, if a shell is started or a script is run via the command line, inserts in model/controller code will not be committed automatically because gluon.main.wsgibase is never called (therefore, no transaction and no commit). Am I missing something? Should we change the documentation to reflect this?

Anthony

[Michele Comitini]

AFAIK only a few databases support transactions with DDL.  PostgreSQL does; MySQL, ORACLE, SQLite don't.

--
-- mail from:GoogleGroups "web2py-developers" mailing list
make speech: web2py-d...@googlegroups.com
unsubscribe: web2py-develop...@googlegroups.com
details : http://groups.google.com/group/web2py-developers
the project: http://code.google.com/p/web2py/
official : http://www.web2py.com/
---
You received this message because you are subscribed to the Google Groups "web2py-developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py-develop...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Niphlod]

@anthony: we should probably leave out ddl statements. The fact is that we just need to make sure that pydal does (or does not) commit explicitely any DDL. Once done that, we'd need to merge that fact with the other one which is that only in postgresql

begin tran

create table foo ....

insert into foo values

....here there's an exception

rollback

and no "foo" table at the end is there.

EVERY other backend leaves "foo" behind. 

BTW @mcm: weren't you the one that pointed out that web2py (even on postgresql) comitts explicitely also DDLs ?

[Michele Comitini]

@niphlod: was me or someonelse ... whatever ... the issue is there.
Not only being able to rollback DDL, something which one can cope with... the problem appears when intermixing DDL  with inserts or updates or in any situation when DDLs are not the first thing. The unit of work of web2py lasts longer than the transaction on the undelying DB.

Is there a way to circumvent the issue?

[Anthony]

On Wednesday, November 18, 2015 at 2:32:07 AM UTC-5, Niphlod wrote:

@anthony: we should probably leave out ddl statements. The fact is that we just need to make sure that pydal does (or does not) commit explicitely any DDL. Once done that, we'd need to merge that fact with the other one which is that only in postgresql

Here we have:

            if not fake_migrate:
                self.create_sequence_and_triggers(query,table)
                table._db.commit()

So there is a commit, though it sounds like it isn't really necessary except in the case of Postgres, right?

There is also a commit after drop, though again, maybe not needed in most cases:

    def drop(self, table, mode=''):
        db = table._db
        queries = self._drop(table, mode)
        for query in queries:
            if table._dbt:
                self.log(query + '\n', table)
            self.execute(query)
        db.commit()
        self._drop_cleanup(table)
        return

But there is no commit after truncate:

    def truncate(self, table, mode= ' '):
        # Prepare functions "write_to_logfile" and "close_logfile"
        try:
            queries = table._db._adapter._truncate(table, mode)
            for query in queries:
                self.log(query + '\n', table)
                self.execute(query)
            self.log('success!\n', table)
        finally:
            pass

Anthony

[Anthony]

Also, do you agree with my second point about the documentation -- whether a commit happens automatically depends on whether the code is executed as part of an HTTP request, not where the code resides?

Anthony

On Wednesday, November 18, 2015 at 2:32:07 AM UTC-5, Niphlod wrote:

[Niphlod]

well, truncate is "mixed territory"; for some backend it's closer to a DDL, for other it's a glorified delete.

create and drop (and alter) ARE ddl.

The funny thing has many (possibly valid) explanations:

- some backends can't cope with multiple ddl statements in a single transaction

- I "think" the commit() was also put there to isolate each statement in an "atomic" fashion (create table, commit, everything works, save .table file, in case of exception stop there)

- some backends just ignore the transaction and automatically commit

On Wednesday, November 18, 2015 at 5:09:28 PM UTC+1, Anthony wrote:

Also, do you agree with my second point about the documentation -- whether a commit happens automatically depends on whether the code is executed as part of an HTTP request, not where the code resides?

Anthony

a commit for any NON-DDL statement happens automatically only when you're in an HTTP request. 

DDL statements (as you pointed out in the code) happen to be committed no matter what.

This goes along with a solid/standard/legacy "mvc" pattern, where you DO NOT alter the model at the same time you alter the data, which is basically piping DDL and NON-DDL statements.

99% of the times you don't alter the model(no DDL). 

When you migrate, you do it in an HTTP request that does only that, without trying to insert/delete/update data .... this "coincidence of nightmares" is exacerbated by pyDAL users accustomed to "automigrations" without flipping a bit ..... other layers have a whole different package for it, or several different commands, or whatever (south, manage.py, alembic, etc) where at the very least there is a solid and explicit workflow, or when through inspection python can argue what went right and what went wrong after issuing a command.

Back to pyDAL, for that 1% cases where you want to alter the model AND the data (and it just doesn't happen because you're an "accustomed user"), funny thing happen, which is that:

- data piped until a DDL is "forcefully" committed (by the code, (but it'll happen also because of backends internals)) . And this happens irregardless of the environment.

- data piped AFTER the DDL is committed or not depending on the environment (shell or http)