why is a highly-viewed post below the fold in the UI?

[BrianHicks]

Hey! I'm self-hosting a goatcounter instance. Recently I had a couple of blog posts do well on aggregators, and the ordering of pages in the overview seems strange. Here's a month view:

(I say "below the fold" because I have to click "Show more" to see that in UI.) What could be going on here? I'd expect the page with 10k views to be at the very top.

I'm running goatcounter 2.4.1 built from revision b4126b. Just in case it's something in how I'm building it, here's the recipe: https://git.bytes.zone/bytes.zone/infrastructure/src/commit/cdaba09ae4cdc924df884328c1bb7c8e449e4840/flake.nix#L44-L55

[arp242]

This seems very similar to #552

I spent quite some time looking in to that, and was never able to reproduce or figure out what's going on. As that was also with self-hosted version, I don't have access to the data. Curiously, no one ever reported it for the hosted version 🤔

If you're still having this problem, is there any chance you can send me the SQLite database file? You can do this with:

1. Stop goatcounter
2. Run goatcounter db query vacuum (adding any -db flag as needed)
3. Make sure that worked and there isn't any .sqlite3-wal WAL file
4. gzip -k db/goatcounter.sqlite3
5. Start goatcounter again

[BrianHicks]

I'm using Postgres, just because when I set it up I already had a postgres DB with good backup routine etc on the machine. I can send you a pgdump or something though. Would that be helpful?

[arp242]

Ah right, for some reason I thought I read sqlite but I must have imagined that.

Interestingly, the two previous people also reported using PostgreSQL. But goatcounter.com uses that as well 🤔

But yeah, a pg_dump would be useful. You can email it to martin@arp242.net if it's not too large.

[BrianHicks]

OK, have done!

[arp242]

Thanks; the reproducible case made it easy to fix.

The problem was pretty simple: it did order by total desc inside the CTE, but not on the select from that. It would still almost always be correct, except in some specific cases when it wasn't. I did it correct pretty much everywhere else, but just forgot it here and somehow never noticed it when I tried to solve it before.

[BrianHicks]

I compiled and deployed 2b0f6e8 to my instance and it works great. Thank you so much!