Custom VFSes and PENDING_BYTE position

[Alexey Alyaev]

Hi sqlite developers,

As this is my first post to the mailing list, I wanted to say huge thanks
and regards to D. Richard Hipp and his team for all the hard work put into
this wonderful database software/library :-)

I have been working on a custom VFS project since 2017. The project
got released, and has since been in deployment as part of one mobile app.

Recently, we ran into an issue that cannot be addressed using any of the
provided sqlite configuration mechanisms, and I would like to see if it's
possible to make a feature request.

The problem we hit is around PENDING_BYTE position.

Our VFS project is based on test_onefile vfs found in sqlite source tree, with
some modifications to store additional meta data at the beginning of file.

The actual reading/writing, as in original onefile vfs, is delegated to the
auxilary vfs - default (OS) vfs of sqlite.

The database begins at an offset just after the initial metadata. So, the
offsets at which read/writes occur in auxilary vfs, are shifted to the right.

This all worked well, until our db files started growing to sizes > 1GB, and
the lock byte issue became evident on windows platform.

After investigating PENDING_BYTE, the history of lock-byte page, and
mechanisms of tweaking its position, I realized that there is strong coupling
between the lock-byte page position as seen by pager, and the offsets which OS
VFSes use for lock bytes.

As I understand, wrapping default OS VFS for reading/writing in another VFS is
a common use case, so would it not be reasonable to allow setting a different
lock byte offset just in windows/unix VFS, while keeping the lock-byte page
position untouched?

This would allow VFSes such as ours to move the lock-byte region used by
default OS VFS, outside of the database segment, say into the head space
which we have allocated and reserved for meta.

Thanks.

-- Alexey
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp-3]

On 3/9/20, Alexey Alyaev <[hidden email]> wrote:

>
> I have been working on a custom VFS project since 2017....
>
> would it not be reasonable to allow setting a different
> lock byte offset just in windows/unix VFS, while keeping the lock-byte page
> position untouched?
>
> This would allow VFSes such as ours to move the lock-byte region used by
> default OS VFS, outside of the database segment, say into the head space
> which we have allocated and reserved for meta.

In the default implementation, the location of the PENDING_BYTE is set
by a global variable sqlite3PendingByte.  If you want to change that
global variable, you can.  Be warned, however, that changing the
location of the pendingByte results in an incompatible file format.
You will also run into severe problems (assertion-faults and/or
segfaults) if you set the pendingByte to some value that is not a
multiple of the page size or that overlaps with page 1 of the database
file.

Aside:  The PENDING_BYTE location can be adjusted in this way for
testing purposes.  Even release builds are able to move the
PENDING_BYTE (using the sqlite3_test_control() interface) since our
dogma is "fly what you test and test what you fly."

Maybe a better solution for you would be to modify your custom xRead()
and xWrite() routines so that the skip over the bytes in the range of
0x40000000 through 0x40000200.  If the offset is greater than or equal
to 0x40000000 just add 512 to the offset before continuing.  Depending
on your circumstances, I suppose you would also have to do something
similar with xTruncate() and xFileSize().

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Alexey Alyaev]

Hi D. Richard Hipp,

Thank you for quick response.

I did consider all options as it stands:

1) Skipping the lock bytes in our vfs. This becomes really messy very fast, as
   it is based on test_onefile vfs and both db and journal are part of the same
   file, and lock bytes may end up in any of 3 segments: db, free space, or
   journal.
   
   In addition we have logic to grow (during writes)/shrink (via PRAGMA) the
   file as necessary. Basically, really a lot of skipping logic in many places.
   
   And even if I'd go ahead and coded all this up, we'd end up breaking all our
   currently deployed applications for all our users, since the skipped offsets
   would contain valid pages.
   
2) Set new PENDING_BYTE via sqlite3_test_control(). This looked like an option
   at first, until I realized that the same value [sqlite3PendingByte]
   is used in both: in pager, and the os_* vfses.
   
   Since our VFS is based on test_onefile with db, free space, and journal all
   occupying one file, it becomes impossible to handle all 3 cases that could
   end up writing into the lock byte region.
   
   And again, modifying the PENDING_BYTE would break the application for all
   users, due to lock-byte page position change.

3) Which is what I considered as the most suitable solution in cases similar
   to ours (one file vfses), and the reason I decide to write to this list:
   
   To be able to set (via config), an offsets for pending byte region
   in os_* VFSes, but have no effect on the lock-byte page offset as seen by
   higher layers.
   
   Since os_* VFSes are pretty much generic and are used as auxilaries in many
   other "wrapper" VFSes, it would be logical to at least have an option to
   configure the pending byte region only for os_* VFSes.
   
In our case, having an option such as (3), we could move the pending byte
region into the head space (occurs before the database) of the file, which
contains sufficient space to house all the lock bytes and that always exists.

This would handle all possible scenarios with one config tweak, as well as not
break any current deployments.

For others, any custom VFS that allocates space before the database in file,
and use os_* VFSes as auxilary, the same option can be used to add a shift
to the pending byte region, aligning it correctly with the lock byte page
offset used by pager.

Ofcourse, I can patch a few lines in sqlite3 source that we use, but I just
wanted to let you guys know of a situation we ran into and had no real simple
way of going forward with, even though everything else seemed to be configurable
and flexible.

Thanks.

-- Alexey
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users