To see how many comments, posts, persons, etc you have in your database, this query gives accurate counts and takes less than 1/3 of a second to execute. You can re-run this query and see new activity instantly.
SELECT table_schema, table_name,
(xpath('/row/cnt/text()', xml_count))[1]::text::intas row_count
FROM (
SELECT table_name, table_schema,
query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
FROM information_schema.tables
WHERE table_schema ='public'--<< change here for the schema you want
) t;
SELECT p.name, c.name, i.domain, p.local, *FROM community_follower
innerjoin person p on p.id = community_follower.person_id
innerjoin community c on c.id = community_follower.community_id
innerjoin instance i on c.instance_id = i.id
WHERE pending='t';
which will show you the user, the community they’re trying to join, and the instance they’re trying to join on. Example:
(redacted my users in case they don’t want it known what they’re trying to join.
I believe it is a glaring symptom of federation replication failure. Data isn’t making it back from the remote server to confirm the join. Either the outbound never made it to the remote, or the remote never made it back to your server. Multiple instances have had users complaining of these federation failures, example: https://lemmy.ml/post/1280517
You (the end-user) can try to cancel the join of the community and join again to trigger new connection to the server. I would also add the date to the output so you can try to see when these failures are happening (are they all on the same day?)
I believe it is a glaring symptom of federation replication failure. Data isn’t making it back from the remote server to confirm the join. Either the outbound never made it to the remote, or the remote never made it back to your server. Multiple instances have had users complaining of these federation failures, example: https://lemmy.ml/post/1280517
You (the end-user) can try to cancel the join of the community and join again to trigger new connection to the server. I would also add the date to the output so you can try to see when these failures are happening (are they all on the same day?)
Yeah sorry, I didn’t realize you meant remote community joins. I have had that problem in fact I still can’t join several communities due to that issue.
These instructions assume you installed “Lemmy from scratch” on Linux (as opposed to Docker, or FreeBSD instead of Linux, etc).
Linux shell on server of instance
Change users to the database Linux account:
sudo -iu postgresOpen the PostgreSQL shell client application:
psqlquery the database server about table locks
select * from pg_locks;from psql shell, list the lemmy_server tables
connect to the database named “lemmy”:
\c lemmyYou are now connected to database “lemmy” as user “postgres”.
List the tables in the database:
\dtList of relations Schema | Name | Type | Owner --------+----------------------------+-------+------- public | __diesel_schema_migrations | table | lemmy public | activity | table | lemmy public | admin_purge_comment | table | lemmy public | admin_purge_community | table | lemmy public | admin_purge_person | table | lemmy public | admin_purge_post | table | lemmy public | comment | table | lemmy public | comment_aggregates | table | lemmy public | comment_like | table | lemmy public | comment_reply | table | lemmy public | comment_report | table | lemmy public | comment_saved | table | lemmy public | community | table | lemmy public | community_aggregates | table | lemmy public | community_block | table | lemmy public | community_follower | table | lemmy public | community_language | table | lemmy public | community_moderator | table | lemmy public | community_person_ban | table | lemmy public | email_verification | table | lemmy public | federation_allowlist | table | lemmy public | federation_blocklist | table | lemmy public | instance | table | lemmy public | language | table | lemmy public | local_site | table | lemmy public | local_site_rate_limit | table | lemmy public | local_user | table | lemmy public | local_user_language | table | lemmy public | mod_add | table | lemmy public | mod_add_community | table | lemmy public | mod_ban | table | lemmy public | mod_ban_from_community | table | lemmy public | mod_feature_post | table | lemmy public | mod_hide_community | table | lemmy public | mod_lock_post | table | lemmy public | mod_remove_comment | table | lemmy public | mod_remove_community | table | lemmy public | mod_remove_post | table | lemmy public | mod_transfer_community | table | lemmy public | password_reset_request | table | lemmy public | person | table | lemmy public | person_aggregates | table | lemmy public | person_ban | table | lemmy public | person_block | table | lemmy public | person_follower | table | lemmy public | person_mention | table | lemmy public | person_post_aggregates | table | lemmy public | post | table | lemmy public | post_aggregates | table | lemmy public | post_like | table | lemmy public | post_read | table | lemmy public | post_report | table | lemmy public | post_saved | table | lemmy public | private_message | table | lemmy public | private_message_report | table | lemmy public | registration_application | table | lemmy public | secret | table | lemmy public | site | table | lemmy public | site_aggregates | table | lemmy public | site_language | table | lemmy public | tagline | table | lemmy (61 rows)query to get your row counts
To see how many comments, posts, persons, etc you have in your database, this query gives accurate counts and takes less than 1/3 of a second to execute. You can re-run this query and see new activity instantly.
SELECT table_schema, table_name, (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count FROM ( SELECT table_name, table_schema, query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count FROM information_schema.tables WHERE table_schema = 'public' --<< change here for the schema you want ) t;query to list local users
select id,name,display_name,actor_id,inbox_url,admin,published,local,instance_id from person WHERE local='t';query to list Community joins that are pending
SELECT * FROM community_follower WHERE pending='t';This kind of query I’d like to work on adding to the server admin screens for operators.
query to list Communities by name
SELECT id,instance_id,name,title,local,published FROM community ORDER BY name;edit: tickle federation replication
why would community joins be pending?
edit: Ah I see. A better query might be:
SELECT p.name, c.name, i.domain, p.local, * FROM community_follower inner join person p on p.id = community_follower.person_id inner join community c on c.id = community_follower.community_id inner join instance i on c.instance_id = i.id WHERE pending='t';which will show you the user, the community they’re trying to join, and the instance they’re trying to join on. Example:
(redacted my users in case they don’t want it known what they’re trying to join.
Thank you for sharing the query.
I believe it is a glaring symptom of federation replication failure. Data isn’t making it back from the remote server to confirm the join. Either the outbound never made it to the remote, or the remote never made it back to your server. Multiple instances have had users complaining of these federation failures, example: https://lemmy.ml/post/1280517
You (the end-user) can try to cancel the join of the community and join again to trigger new connection to the server. I would also add the date to the output so you can try to see when these failures are happening (are they all on the same day?)
What software are you using to view the queries?
I use DataGrip. It’s fantastic.
Yeah sorry, I didn’t realize you meant remote community joins. I have had that problem in fact I still can’t join several communities due to that issue.
There is a closed issue on Github about ‘pending’ subscribe/joins: https://github.com/LemmyNet/lemmy/issues/2685