Fediver

性別というプロパティをsexじゃなくてgenderと表現するの、まあこれは現代的で普通だと思うんですけど、これがデータパイプラインの話になって、しかも加工元データでは性別がsexと表現されていたとき

SELECT
  ...
  sex AS gender,
  ...

CREATE OR REPLACE FUNCTION public.timestamp_to_aid(t timestamptz) RETURNS text AS $$
DECLARE
    time_part text;
    num bigint;
BEGIN
	num := extract(epoch from t) * 1000 + mod(extract(millisecond from t),1000)::integer - 946684800000;
    time_part := '00';
    FOR i IN 1..8 LOOP
		time_part := substring('0123456789abcdefghijklmnopqrstuvwxyz', (1 + mod(num, 36))::integer, 1) || time_part;
		num := num / 36;
    END LOOP;
    RETURN time_part;
END;
$$ LANGUAGE plpgsql;

select n.* from "note" n
where
	not exists (select 1 from note reply where reply."replyId" = n.id)
	and not exists (select 1 from note renote where renote."renoteId" = n.id)
	and not exists (select 1 from note_favorite nf where nf."noteId" = n.id )
	and n."clippedCount" =0
	and id < timestamp_to_aid(current_date - 56)
order by id desc;

@dampuzakura ちなみにselect diskで選ぶディスクを間違うとデータが飛ぶので気をつけて

@dampuzakura

diskpart
list disk
select disk (list diskで得たリストからそれっぽいディスクの番号をここに入力)
clean
convert gpt
create partition primary
exit

[Auto Note]:Updated
Fetch URL: https://github.com/misskey-dev/misskey
Commit: 3c81926f to 1fbc129d
fix(frontend): ファイルの添付可能数を超えたらノートボタンを押せないように (#15126)
enhance(frontend/aiscript): セーブ内容を削除できるMk:removeを追加 (#15158)
fix(frontend): ノート検索が使用できない場合でもチャンネルのノート検索欄がでていた問題を修正 (#15082)
Chore: Docker内のDebianのバージョンを上げる(bookworm) (#15073)
fix(frontend): MiAuth認可画面で、認可処理に失敗した場合でもコールバックURLに遷移してしまう問題を修正 (#15154)
fix(backend): アプリ作成方式で作成したトークンの権限を表示するように (#15177)
fix(frontend/AiScript): Ui:C:selectの値が切り替わらない問題を修正 (#15184)
pg_bigmが利用できるよう、ノートの検索をILIKE演算子でなくLIKE演算子でLOWER()をかけたテキストに対して行うように (#15205)
enhance(frontend): ワードミュート・ハードワードミュートの説明を追加 (#15207)
2025 (#15203)

#みーくりあ！UpdateLog

hmm
なんかweb側からこんなログが出てたぞ…

query is slow: SELECT "ChartXinstance"."id" AS "ChartXinstance_id", "ChartXinstance"."date" AS "ChartXinstance_date", "ChartXinstance"."group" AS "ChartXinstance_group", "ChartXinstance"."___requests_failed" AS "ChartXinstance____requests_failed", "ChartXinstance"."___requests_succeeded" AS "ChartXinstance____requests_succeeded", "ChartXinstance"."___requests_received" AS "ChartXinstance____requests_received", "ChartXinstance"."___notes_total" AS "ChartXinstance____notes_total", "ChartXinstance"."___notes_inc" AS "ChartXinstance____notes_inc", "ChartXinstance"."___notes_dec" AS "ChartXinstance____notes_dec", "ChartXinstance"."___notes_diffs_normal" AS "ChartXinstance____notes_diffs_normal", "ChartXinstance"."___notes_diffs_reply" AS "ChartXinstance____notes_diffs_reply", "ChartXinstance"."___notes_diffs_renote" AS "ChartXinstance____notes_diffs_renote", "ChartXinstance"."___notes_diffs_withFile" AS "ChartXinstance____notes_diffs_withFile", "ChartXinstance"."___users_total" AS "ChartXinstance____users_total", "ChartXinstance"."___users_inc" AS "ChartXinstance____users_inc", "ChartXinstance"."___users_dec" AS "ChartXinstance____users_dec", "ChartXinstance"."___following_total" AS "ChartXinstance____following_total", "ChartXinstance"."___following_inc" AS "ChartXinstance____following_inc", "ChartXinstance"."___following_dec" AS "ChartXinstance____following_dec", "ChartXinstance"."___followers_total" AS "ChartXinstance____followers_total", "ChartXinstance"."___followers_inc" AS "ChartXinstance____followers_inc", "ChartXinstance"."___followers_dec" AS "ChartXinstance____followers_dec", "ChartXinstance"."___drive_totalFiles" AS "ChartXinstance____drive_totalFiles", "ChartXinstance"."___drive_incFiles" AS "ChartXinstance____drive_incFiles", "ChartXinstance"."___drive_decFiles" AS "ChartXinstance____drive_decFiles", "ChartXinstance"."___drive_incUsage" AS "ChartXinstance____drive_incUsage", "ChartXinstance"."___drive_decUsage" AS "ChartXinstance____drive_decUsage" FROM "__chart__instance" "ChartXinstance" WHERE (("ChartXinstance"."date" = $1) AND ("ChartXinstance"."group" = $2)) LIMIT 1

なんで多分以下を叩くことになって流石に心配なのでテスト環境上でバックアップ取ってる

DELETE FROM "user_publickey"
WHERE "userId" NOT IN (SELECT "id" FROM "user");

これ叩いたら7000件くらい出てきた

SELECT "userId" FROM "user_publickey"
WHERE "userId" NOT IN (SELECT "id" FROM "user");

If your database is not using C or POSIX for its collation setting (which you can check with SELECT datcollate FROM pg_database WHERE datname = current_database();), your indexes might be inconsistent if you ever ran with a version of glibc prior to 2.28 and did not immediately reindex your databases after updating to glibc 2.28 or newer.