I've found a root cause and issued a very temporary fix. here's what's up:
nodeinfo and the sidebar statistics are both pulled from a database table named site_aggregates. some of the columns in that table, like the ones for active users per time period, are calculated by the Lemmy backend on a schedule. the posts and comments columns are calculated live, but not by the backend; those columns are driven purely by database triggers calling stored procedures when the post and comment tables update. this is an awful pattern.
anyway, to illustrate the bug in lemmy, the database migration that established the site_aggregates table correctly initializes the posts column like so:
SELECT coalesce(count(*), 0) FROM post WHERE local = TRUE) AS posts
but that's not what keeps the column up to date. on updates to the post table, the database calls the site_aggregates_post_insert() stored procedure, which has the following body -- see if you can spot the mistake:
CREATE OR REPLACE FUNCTION public.site_aggregates_post_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
UPDATE
site_aggregates sa
SET
posts = posts + (
SELECT
count(*)
FROM
new_post)
FROM
site s
WHERE
sa.site_id = s.id;
RETURN NULL;
END
$function$
(and for completeness, this is called by this database trigger:)
CREATE OR REPLACE TRIGGER site_aggregates_post_insert
AFTER INSERT ON post REFERENCING NEW TABLE AS new_post
FOR EACH STATEMENT
EXECUTE PROCEDURE site_aggregates_post_insert ();
did you spot the mistake? no shame if you didn't, stored procedures can be hard to follow. here it is: the statement that initializes the posts column has a WHERE local = true clause that correctly filters out non-local posts from the statistics it pulls. the stored procedure doesn't have that; there's no mechanism in place that filters out non-local posts from the count, so our database was incrementing the count every time anything was stored in the posts table, including posts discovered via federation.
I have temporarily corrected our posts column and our nodeinfo along with it by setting the value of that column to the value of the initialization statement above. the stored procedure and trigger in our database are still incorrect; I will need to carefully fix the stored procedure in our database in a way that won't break future migrations when we upgrade Lemmy.
as far as I can tell trying to piece together the SQL over a year of migrations (another reason why you don't use stored procedures if you can help it), this bug was never fixed. a migration dated 2024-02-24 dropped all of the procedures and triggers that used to update site_aggregates. I don't know what mechanism replaced them, and I won't find out until I evaluate the newest "stable" version of lemmy for suitability to be deployed into production.
someone should probably inform db0 that nodeinfo statistics for lemmy instances running anything before the commit with that migration are incorrect; this likely affects small instances much more than large ones. also tell him the following:
- I'm still suspicious as fuck
- I'll fuckin do it again
oof, I’m sorry you’re caught in the middle of this crap; it’s not a great feeling to be put into this kind of situation.
take this with a grain of salt because I’m exhausted from a hell workweek, but this felt like a thread you could pull on:
if their AI horseshit is doing so well in your niche, why are they hiring for it? that’s fucking weird, right? use your best judgement, but be as aggressive with your questions as feels appropriate.
also, and I hope this isn’t too obvious: you’re in the middle of a vapid power game between two sociopaths who lie for a living (and the pilfered livings of many other people). craft your questions and statements with that in mind — you’re there to sell the idea that the opposing executive has done something foolish, so come up with responses to the potential bullshit these professional bullshitters might fling at you (“the new hire is just to train/support/monitor the AI” “oh, but wasn’t it already a success? that doesn’t sound very efficient, can you go into more detail?”). one of the biggest mistakes I’ve made in similar situations is to stress absolute truth and precision in conversation — and it’s a trap that a lot of tech people fall into, that the executive class tends to use as a mechanism for control. the truth is on our side but these people don’t give a fuck about that, so sell them a story.