self

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

it’d be really weird you didn’t do even a tiny amount of reading into the research by DAIR and other organizations studying the interplay between AI and racism before shitting this post out into our thread, but I’ve seen your takes on transphobia and they’re somehow even more inane so please feel free to go fuck yourself

riskable, sibling, please take this as advice:

you should’ve stuck to things you know like the coefficient of friction of PETG, magnetic field interactions, or any other topic where your output isn’t this absolute buffoon shit

thank you for following up on that! I’m surprised it’s returning an invalid value — we’re not botting (to my knowledge at least, I’ll check the DB later and make sure nobody’s doing anything weird) and I haven’t written any code that touches nodeinfo directly. to be honest, if I were to modify our stats in any way I’d do it to make our instance look smaller, as larger instances tend to attract more bad actors.

I see that @db0@lemmy.dbzer0.com tried to initiate contact in that thread, but unfortunately I’m unable to reply directly because posts between our instances don’t seem to be federating — this could be due to a federation queue delay, or possibly an automated quarantine due to us being on the suspicious instances list.

my hunch is that our instance may be misreporting its nodeinfo due to a bug in our (now rather old) version of lemmy. I’ve been meaning to upgrade us for a while, but there is a bit of outstanding infrastructural work I’d like to do as part of that. since it seems to be impacting the health of our federation, I’ll prioritize an upgrade to the newest stable version.

deezer seems to rightfully consider being flooded with lazy AI slop a problem:

Herault added that a detection tool launched in January was helping the company filter fully AI-generated tracks from the algorithmic recommendations for its 9.7 million subscribers.

Drake used AI to duo with Tupac in one of his songs

this is something different from what the article’s talking about, but also that sounds tacky as fuck

so like a fool I decided to search the web. specifically for which network protocol Lisp REPLs use these days (is it nREPL? or is that just a clojure thing with ambitions?)

and the first extremely SEOed result on ddg was this bizarre blend of an obscure research lisp from 2012 and LLM articles about how Lisp is used in mental health:

Numerous applications and tools are being developed to support mental health and wellness. Among the varied programming languages at the forefront, Lisp stands out due to its unique capabilities in cognitive modeling and behavior analysis.

so I know exactly what this is, but why is this? what even is the game here?

to quote the red ink on your last CS exam: Jesse, what the fuck are you talking about

yeh I know, so calling out zk proofs as “the technology behind crypto” really reads like calling hydrogen gas “the technology behind the Hindenburg”, doesn’t it.

you and your friend really should have spent any time at all looking into who runs worldcoin and how fucking monstrously dystopian it is before rushing into buttcoin of all places to defend the “valid use of the technology behind crypto” (????) you both imagined existed in the OP’s screenshot

oh fuck off

I’m not saying it’s always infosec.pub, but

you got banned before I got to you, but holy fuck are you intolerable

We should be people of science, not reactionaries.

which we should do by parroting press releases and cherry picking which papers count as science, of course

but heaven forbid anyone is rude when they rightly tell you to go fuck yourself

type the words “transhumanism eugenics” into ddg and see what comes up. but mostly just fuck off tbh