create/drop transaction
Posted February 1st, 2009 by WilliamCarrier
Log from IRC server Freenode (IPv6) channel #SQL.
| <me> | I have a table which will be very likely to ever be used by only one user at a time and I'm looking into a transaction mechanism, basically I'd rewrite the whole table ; I read that you can create temporary tables and I'm wondering if using that and at the end simply dropping the original table and renaming the temporary table to the original name would be better than working one row at a... |
| <me> | ...time erasing the data and rewriting it (which could lead to pretty large IDs) ; I'm very new to transactions and any advice on the matter about my particular case would be helpful, thanks |
| <Xgc> | me: The idea of a temporary table is database/implementation specific. Often, a temporary table is connection specific and exists (at most) for the duration of the session/connection. It is automatically dropped. So the rename procedure you describe would not work in this case. |
| <me> | Xgc: ok, this is chinese for me right now but thanks for the warning :) |
| <Xgc> | me: It might be easier if you describe your real requirement. |
| <me> | Xgc: a simple structure which will completely be replace on update as there's no concurrency ; in detail it's adding choices to another table like t1 ID (won't be affected by this at all) ; t1b t1_ID, choice |
| <me> | Xgc: got my description ? |
| <Xgc> | me: You're trying to ALTER the DDL of an existing table? |
| <Xgc> | me: I still don't quite understand your requirement. Can you show a complete example? |
| <me> | Xgc: I dunno what DDL but I'm not altering t1, I'm just adding choices to it |
| <Xgc> | me: Why can't you just INSERT new rows or UPDATE existing rows? |
| <Xgc> | and DELETE rows. |
| <Xgc> | me: Most databases support transactions where those adjustments to the table won't be seen until you COMMIT; the changes. |
| <me> | Xgc: t1 = table ; chair; - t1b = table, round ; table, square ; chair, wood ; chair, leather; |
| <me> | Xgc: the user basically recreate the whole table so I though it'd be faster to just drop it and recreate it ; securised through ACID of course |
| <Xgc> | me: It's usually a bad idea to DROP / CREATE tables, apart from administrative system updates. The application should normally just deal with the existing schema. |
| <me> | Xgc: even in transactions ?! my basic concern is performance, I get the whole table through the form so the first though is, just create it from scratch, maybe I'm being optimistic. |
| <Xgc> | me: DROP / CREATE happen without regard to transactions. |
| <Xgc> | me: That's one of the reasons to avoid them. |
| <me> | Xgc: so you think empty then insert is the fastest way to go ? |
| <Xgc> | me: Normally, you would not completely empty/clear the table. You'd just perform the necessary changes (inserts, updates, deletes). But yes. That's the way. |
| <me> | Xgc: ok so begin work ; delete from table ; inserts ; commit ? |
| <Xgc> | me: Something like that. |
| <me> | Xgc: thanks I've just learned some chinese ! :) |
