ROWID stability about axiom

[mithrandi]

https://pypi.org/project/axiom/0.8.0rc1/ c/o https://github.com/twisted/axiom/blob/0.8.0rc1/.github/workflows/publish.yml

[glyph]

There is an evil incantation which will make a quick upgrade possible.

http://nbviewer.ipython.org/gist/glyph/2f3fd023b273e53c84fc

[lvh]

does the evil mad scientist cackle

[lvh]

It might be a good idea to ask drh about this specifically. I'm reasonably sure it'll work consistently, but he's the only authority on the subject.

Also, did you test the case where VACUUM does in fact work correctly now, just in case there's something special about ROWID we're forgetting about?

[glyph]

Here's a version of the notebook that does a VACUUM, names the oid column differently, bumps the schema version, and re-opens the store. http://nbviewer.ipython.org/urls/glyph.im/blob/2C4F608E-26FC-4688-8432-C19C33171FBF.ipynb

[mithrandi]

I think you need to delete a row that was not the last row added to the table in order to really demonstrate that VACUUM is behaving correctly.

[glyph]

@mithrandi - feel free to make a hacked up version of the notebook :). Maybe it's time to start actually having a branch / PR for this somewhere though?

[glyph]

@mithrandi - also, some very basic experiments I did showed OIDs changing if there were more than 10 rows or so in the database - was I doing those wrong?

[mithrandi]

So I played around a bit, with the following results:

A version of your example which demonstrates ROWID stability after deleting some rows before VACUUMing.

A further modified version which demonstrates how ROWIDs are not stable without making the schema change.

So, I'm fairly satisfied with these empirical results, but I would still love to hear from drh whether this can be expected to work reliably.

[glyph]

Is drh on github? @\drh isn't him, but it would be neat to just tag him in :)

[mithrandi]

Posted about this on the sqlite-users list; sadly, drh seems to think it doesn't work.

EDIT: Fixed the sqlite-users archive link, which apparently changed(?)

[ldanielburr]

Just a comment from the peanut gallery, but what's wrong with adding a migration that does what drh suggested?

BEGIN;
ALTER TABLE some_table_name RENAME TO temp_name;
CREATE TABLE some_table_name(oid INTEGER PRIMARY KEY, ....);
INSERT INTO some_table_name SELECT oid, * FROM temp_name;
DROP TABLE temp_name;
COMMIT;

[glyph]

@ldanielburr Yes, we'll have to do that. In fact it could be expressed as even less work; we don't do ALTER TABLE anywhere, and all our migrations already copy code from old tables to new tables, deleting as they go.

The problem is in detecting that the change needs to happen. Right now we have only a single axis of migration: up through different versions of classes; this version is encoded in the table name. To implement this, we need a meta-schema version; one that indicates what sqlite3 features are available, which means we need a different way of encoding that information; either by adding features to the table-naming convention or by putting it somewhere else in the database. Not impossible, just something we were hoping to avoid with the simple ALTER TABLE hack.

[mithrandi]

So I'm looking at fixing this issue again. When opening a store, we can check whether the system tables (axiom_objects etc.) have an explicit PK or not. We also know which columns are in each item type's schema, so we can check for the presence of a storeID column there. Coupled with ignoring storeID in the schema mismatch check this would allow us to handle existing stores.

New types / versions / tables could always be created with the storeID column, and axiomatic upgrade could upgrade the system tables as well as even upgrading existing types without bumping the schema version. There could also be a flag to auto-upgrade system tables on store open.

[mithrandi]

Okay, I have a PR up; reviews greatly appreciated.

[glyph]

Thank you, so much, everybody who worked on this. It's weirdly kind of emotional seeing a bug that was really bothering me so much over a decade ago actually, finally get fixed.

[mithrandi]

It was pretty satisfying working on the fix too! Although I think we still need to roll a new release?

[glyph]

@ldanielburr are you interested in being the release manager? Happy to add you on PyPI.

[ldanielburr]

@glyph, please point me at the release management docs, and I'll take a look. I don't want to promise anything yet, but I will commit to reviewing the release management process docs and giving you a firm answer :)

[glyph]

@ldanielburr It's really just

$ python setup.py sdist bdist_wheel
$ twine upload dist/*

what's your username on PyPI? I'll give you upload access and you can do this.

[mithrandi]

Ideally we would update NEWS.txt with info about the ROWID change since it's a big one and eg. may make axiomatic upgrade unexpectedly take a long time. You'll also need to bump axiom/_version.py

[ldanielburr]

@glyph, my PyPI username is "somenamenobodyelsehas".

[ldanielburr]

@mithrandi, agreed, there should be a NEWS.txt, version bump, etc.

[glyph]

@ldanielburr You now have upload access to Axiom.

[ldanielburr]

@glyph thanks for the PyPI access. I will commit to getting the newsfile and version updates up for a PR sometime next week, and then the upload to PyPI.

[ldanielburr]

Ok, so I didn't get to this task yet. Just updating this thread to let people know I still intend to make a release.

[glyph]

@ldanielburr we all know how it goes with open source :-). Thanks for the update.

[glyph]

@ldanielburr soooooo… how’s it going?

[ldanielburr]

@glyph, it is going like pride before a fall ;) Sorry I haven't gotten to this yet. Holiday break is coming up in a few weeks, and I'll try to give this a go in December.

[glyph]

@ldanielburr 🤞

[glyph]

How do we promote this to “not an RC”?

[mithrandi]

Basically just create a release on GitHub with a not-an-RC version number (ie. 0.8.0); the release artifacts will then be built and published to PyPI automatically. My plan is to do the final release in a few days just to allow a little testing if anyone wants.

[glyph]

All right, happy to let that play out.

[mithrandi]

Problems: #112

[mithrandi]

https://pypi.org/project/axiom/0.8.0/

[glyph]

Amazing. At long, long last :)