Age | Commit message (Collapse) | Author | Files | Lines |
|
Decided to change the naming scheme of files in util/updates/
|
|
This keeps the function body and the trigger declaration closer to each
other, so it's easier to tell when the function will be called and which
cases it needs to handle. If you ignore errors from existing
trigger declarations, the triggers.sql script is idempotent and can be
used in from scripts.
|
|
|
|
To my surprise, I actually managed to achieve acceptable performance by
just adding two indices. I totally expected I'd have to keep a cache
column in ulist_vns whether the row is private or not. The partial index
on the users table in fact improves the performance of the vote graph
query. A covering index improves that even further, but that requires
Postgres 11+, which the Docker image doesn't have yet (and isn't all
that crucial anyway).
There's a rather annoying potential for confusion regarding the private
flag on votes. The user page & list stats only look at whether the
'Voted' label is private, whereas the VN stats use the "proper" approach
of checking for any public label. Not entirely sure which of the two is
more intuitive.
|
|
This adds the users.c_vns and c_wish columns and a function to update
the cache.
Unlike my previous cache update approaches, I did not use SQL triggers
here, as that seemed more complex and less efficient than updating the
cache manually. That's not to say that I'm happy with the current
approach, but meh...
The cache update function is not automatically run for all users, but
that could be added to Multi::Maintenance if it turns out that the
cached values will not be updated properly in some cases.
|
|
These ensure that, for each row in rlists, all the linked VNs have an
entry in ulist_vns. This implementation should be more robust than
before; It now isn't possible to remove an item from ulist_vns without
also removing all relevant rows from rlists, and adding more VNs to an
existing release entry will ensure that those VNs are also added to
ulist_vns.
|
|
It turned out that naively changing the existing queries to refer to
ulist_vns with a (WHERE vote IS NOT NULL) made them ultra slow (like,
DNF-slow), for a reason I haven't been able to figure out yet. I had to
rewrite the queries to use CTEs and in doing so figured that I could get
an extra performance improvement by combining the popularity and rating
updates in one query. The combined update function now runs faster than
the old queries.
|
|
|
|
To directly query the new list feature. I'll migrate the old
vnlist/wishlist/votelist APIs later to query the 'ulist' tables.
|
|
changes
|
|
rlisted VNs
|
|
|
|
|
|
Very much experimental. I'm not sure this is even the right model.
|