On 2017/12/19 6:15 PM, Dinu wrote:
2) Structure alterations; either drop table, or drop indexes : I am
reluctant to do this; my evangelical instinct tells me hacking the semantics
of life might lead to implosion of Earth :)
Oh the irony....
Your instinct (like most such instincts) is simply wrong.
But don't take our word for it. Take a copy of the data somewhere else,
construct some readers mimicking the in-use case (or whatever it takes
to adequately convince yourself) and try the suggested fix.
Not only will it work, it will do so really fast and prove to be not
only the best, but also the absolutely correct fix.
The good people here have even tried to build suggested scripts around
your irrational fears, but in truth the simplest version would work the
best, which is:
In a Transaction -
- Rename the Table,
- Recreate a new empty version of it,
- Copy the rows you want to keep.
- Drop the renamed table.
- Recreate any Indexes/Triggers.
A solution that might be slightly slower but much less complicated and
still very fast is simply:
In a Transaction -
- Drop the Indexes/Triggers,
- Do the deletes,
- Recreate the Indexes/Triggers.
This will work 100% even while other readers are active (thanks to the
WAL [1]) and without making them implode (perhaps pause a little bit[2],
but not implode).
Why am I (and others here) so confident this will work? Because this is
a fundamental design principle and indeed a requirement of a
Transactional and Relational Database (Which SQLite is) - Plus we do it
all the time very much relying on exactly those characteristics. There
is no "hacking" going on in any of our suggestions, this is quite the
mainstream way in which it works.
It's just like learning to ride a bicycle. Initially your fears feel
justified and the physics seem impossible magic from where you watch the
others... until that first day you find your balance and soar.... Then
pretty soon, you do it without hands on the steering wheel, just like
the other cool kids. :)
Cheers,
Ryan
[1] - It will work even with other Journal modes than WAL, it's just
that the readers then will wait quite a bit more on the transaction to
finish, whereas the WAL allows updates to not affect concurrent readers
until a checkpoint/commit.
[2] - The file is several Gigabytes in size, it's never going to be
instant, there WILL be some waiting, but it won't take very long - try
do it at a quiet time though. You will get a feeling for the time-frame
if you do the test-case thing.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
