bless this jank

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