Andrew Piskorski wrote:
On Thu, Mar 17, 2005 at 08:33:03PM -0700, Ara.T.Howard wrote:How about using a second in-memory database for your modifications, then using ATTACH to merge
On Sat, 12 Mar 2005, Andrew Piskorski wrote:
On Sat, Mar 12, 2005 at 10:03:25AM -0700, Ara.T.Howard wrote:
does anyone have a strategy for doing massive updates to a db and atomicly replacing it in a multi-process situation?
You proposed procedure below is an ugly hack. I do not know the particulars of your situation, so maybe for you it really is justified over the correct "use PostgreSQL" alternative. But I would think long and hard about that.
(And no, I don't know what the best/simplest way to do an "atomically
replace whole db" step is with SQLite. That would be useful to know,
regardless.)
the changes. I'm fairly sure I remember Dr. Hipp stating that moving complete rows between attached
database tables is very speedy. Depending on the particulars of your situation you could do a couple
different things:
* if you are just adding new rows, open an in-mem database and create an identical table to do all your
inserts. Insert the new rows, then attach the db, and finally start a transaction and:
INSERT INTO old-table SELECT * FROM memory.new-table;
* if you must modify existing data, you could do the process in reverse-- select the contents of one or
more tables into the memory db, modify them, then inside a transaction, delete the contents of your
main db's table(s) and select them back into the main table(s).
If only one table is getting modified but the readers don't need to access it, this will get around the
problem of one transaction holding an EXCLUSIVE lock for long periods of time. If you are touching
more tables, you may have create and track some sort of dirty indicator or last update time.
Because you are using a transaction, it is still atomic. But because you are just moving the rows from
another database, it should solve the problem of holding the lock for too long. In-memory databases
are created by naming them ":memory:" You can find more info by doing a mailing list search on
"ATTACH in memory" or poking around the docs for ATTACH.
-Eli
