the commit in the drop()

31 views
Skip to first unread message

Michele Comitini

unread,
Jan 21, 2015, 5:35:57 PM1/21/15
to web2py-developers
What's the idea behind this?
IMHO that's really dangerous for at least 2 reasons:

1.  There could be a complex transaction with stuff still to be committed
2.  There could be an exception in code during the same request and one could need/want to safely rollback.


Niphlod

unread,
Jan 22, 2015, 4:16:23 PM1/22/15
to web2py-d...@googlegroups.com
uhm. apart from the fact that it shouldn't be in the baseadapter for sure, are you positive that you can drop and rollback on backends?
from my memory, mysql and oracle can't by design.......... postgresql and mssql do (although not on all serialization levels). not sure about sqlite.

I don't have the time to test but it seems another nifty commit for legacy mysql (i.e. no multiple ddl in one transaction)


Michele Comitini

unread,
Jan 22, 2015, 4:23:46 PM1/22/15
to web2py-developers
I am sure postgresql handles any ddl rollback.
IMHO I would start from the fact that ddl could be rolled back, also ddl effects are available readily inside a running transaction without having to commit. Only those backends that don't have a transactions handling ddl should do the commit.
Let's not make the best ones behave just like the worst ones...

 

--
-- 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

unread,
Jan 22, 2015, 4:59:39 PM1/22/15
to web2py-d...@googlegroups.com
Let's not make the best ones behave just like the worst ones...

LOL. fine for me. I'm a fan of orchestrating complex transactions in app code (a small FSM) or in a stored proc, but that's probably just me playing safe.
Also, I rarely feel the need of drop()ping.

baseadapter should stick to basics and involve the sanest-faster approach (uhm, have a look towards create_table() ... ).
backends with bad behaviour can override.
The only problem I see is that I fear that postgresql would be the only backend to actually benefit.
Others needs investigation (i.e. commit() is really needed or it's implicit by default) but in any case they won't be able to recover from a drop even if the rollback() is called.

I guess it'll be a not-so-pretty commit.


BTW: the real deal-breaker is 1) . It could be circumvented moving the commit BEFORE the drop . 2) is reeeeeally a corner-case unless you want to play with fire and multiple ddls in a single transaction

Michele Comitini

unread,
Jan 22, 2015, 5:32:00 PM1/22/15
to web2py-developers
I just see that the unwanted behavior is in CREATEs too.

- DROP COLUMN -> COMMIT
- DROP TABLE -> COMMIT
- ADD COLUMN -> COMMIT
- CREATE TABLE -> COMMIT

That's seems all that I have found.
Having the commit at the end of all migration generated changes, would make much safer migrations at least on databases where that is possible.



--

Massimo DiPierro

unread,
Jan 22, 2015, 5:56:24 PM1/22/15
to web2py-d...@googlegroups.com
To my knowledge, only postgresql supports what you propose. 

Michele Comitini

unread,
Jan 23, 2015, 3:26:28 AM1/23/15
to web2py-developers
I am reading that oracle does an implicit commit on every DDL in any case.  That is simply an awful thing to do.

Massimo DiPierro

unread,
Jan 23, 2015, 8:28:39 AM1/23/15
to web2py-d...@googlegroups.com
postgres is the best!

Michele Comitini

unread,
Jan 23, 2015, 9:54:05 AM1/23/15
to web2py-developers
+1
Reply all
Reply to author
Forward
0 new messages