[Supabase] cron (스케줄링) 설정하기

Supabase로 스케줄링을 작성할 수 있다고 해서 서비스를 이용해보기로 했다.

도전!!

 

1. Database > Extensions 클릭

기본적으로 제공하는 기능은 아니고 확장형 기능이기 때문에

pg_cron을 활성화해야 한다. 

지금은 활성화했기 때문에 바로 위에 올라와서 보이지만.. 안보인다면 검색창에 pg_cron 검색 후 활성화할 것

 

2. 스케줄링 실행할 함수 만들기

내 로직에는 기존의 테이블 데이터 전부 삭제 → 랜덤 주제 테이블에서 새로운 row선택  →  새로운 주제 insert였기 때문에 다음과 같이 작성했다.

 

1. 랜덤 주제 INSERT

CREATE OR REPLACE FUNCTION dalmuri.get_random_topic_fix() 
RETURNS VOID 
LANGUAGE plpgsql
SET search_path = dalmuri, public
AS $$ 
DECLARE
    selected_topic_content text; 
BEGIN
    -- 1. 조회 로직 ...
    SELECT 
        "RANTO_CONTENT" INTO selected_topic_content
    FROM 
        "RANDOM_TOPIC_VIEW" 
    ORDER BY 
        RANDOM() 
    LIMIT 1;

    -- 2. 삽입 로직 ...
    IF selected_topic_content IS NOT NULL THEN
        INSERT INTO 
            dalmuri."TODAY_WORD_TOPIC" ("TOPIC_ID", "TODAY_TOPIC", "INSERT_DATETIME")
        VALUES (
            gen_random_uuid(), 
            selected_topic_content, 
            NOW() 
        );
    ELSE
        RAISE NOTICE '[WARN]: RANDOM_TOPIC_VIEW에서 토픽을 찾을 수 없습니다.';
    END IF;
    
    RETURN; 
    
END;
$$;

함수 정의 및 설정

• CREATE OR REPLACE FUNCTION : 함수가 이미 존재하면 교체하고, 없으면 새로 생성. 함수 이름은 get_random_topic_fix이며 인수(파라미터)는 없음
• RETURNS VOID : 특정 데이터를 호출자에게 반환하지 않고 내부 작업을 수행하는데 사용됨
• LANGUAGE plpgsql : 함수 본문이 PostgreSQL로 작성됬음을 명시
• SET search_path : 함수 실행 시 검색 경로(search_path)를 설정. 이 함수 내부에서 테이블 혹은 뷰를 참조할 때 스키마 이름을 명시하지 않으면 dalmuri 스키마를 먼저 찾고, 없으면 public 스키마를 찾음
• DECLARE : 함 수 내에서 사용할 변수를 선언. 여기선 selected_topic_content

함수 본문

• BEGIN ~ END 안에 작성

1️⃣ 조회 로직 (무작위 토픽 선택)

SELECT
    "RANTO_CONTENT" INTO selected_topic_content
FROM
    "RANDOM_TOPIC_VIEW"
ORDER BY
    RANDOM()
LIMIT 1;

• "RANDOM_TOPIC_VIEW"뷰에서 "RANTO_CONTENT 컬럼의 값을 조회
• ORDER BY RANDOM() : 조회된 결과 집합을 무작위 순서로 정렬
• LIMIT 1: 무작위로 정렬된 결과 중 첫번째 항목 단 하나만 선택

2️⃣ 삽입 로직 (선택된 토픽 저장)

IF selected_topic_content IS NOT NULL THEN
    INSERT INTO
        dalmuri."TODAY_WORD_TOPIC" ("TOPIC_ID", "TODAY_TOPIC", "INSERT_DATETIME")
    VALUES (
        gen_random_uuid(),
        selected_topic_content,
        NOW()
    );
ELSE
    RAISE NOTICE '[WARN]: RANDOM_TOPIC_VIEW에서 토픽을 찾을 수 없습니다.';
END IF;

 

• IF selected_topic_content INS NOT NULL THEN : 1단계 조회 로직에서 selected_topic_content를 성공적으로 가져왔는지 확인
• NULL이 아닐 경우 = 토픽을 찾았을 경우
  • dalmuri."TODAY_WORD_TOPIC" 테이블에 새로운 행을 삽입
  • "TOPIC_ID" 컬럼 : gen_random_uuid() 함수를 사용하여 고유한 범용 고유 식별자(UUID)를 생성하여 저장
  • "TODAY_TOPIC" 컬럼 : 조회한 무작위 토픽 내용(selected_topic_content)을 저장
  • "INSERT_DATETIME" : NOW()함수(현재 시각)를 저장 
• NULL일 경우 = 토픽을 찾지 못한 경우
  • RAISE NOTICE : 데이터베이스 메시지 창에 경고 메시지 출력
• RETURN : 함수 종료

2. 오늘의 랜덤 주제 및 댓글 DELETE

CREATE OR REPLACE FUNCTION dalmuri.reset_today_topic()
RETURNS void
LANGUAGE plpgsql
SET search_path = dalmuri, public
AS $$
  BEGIN
  -- TODAY_WORD_CMNT 관련 모든 행 삭제
  DELETE FROM dalmuri."TODAY_WORD_CMNT";
  -- TODAY_WORD_TOPIC 관련 모든 행 삭제
  DELETE FROM dalmuri."TODAY_WORD_TOPIC";
  -- get_random_topic_fix 실행으로 랜덤한 행 추가
  PERFORM dalmuri.get_random_topic_fix();
  RETURN;
END;
$$;

함수 정의 및 설정

• CREATE OR REPLACE FUNCTION : 함수가 이미 존재한다면 교채, 없으면 새로 생성, 인수는 없음
• RETURNS VOID : 특정 값을 반환하지 않는 함수. 내부 작업을 수행
• LANGUAGE plpgsql : 함수 본문이 PostgreSQL로 작성됨
• SET search path = dalmuri, public : 함수 실행 시 검색 경로를 dalmuri와 public으로 설정

함수 본문

1️⃣ TODAY_WORD_CMNT 테이블 초기화 (댓글 삭제)

DELETE FROME dalmuri."TODAY_WORD_CMNT";

• dalmuri 스키마의 "TODAY_WORD_CMNT" 테이블에 있는 모든 행을 삭제

2️⃣ TODAY_WORD_TOPIC 테이블 초기화 (토픽 삭제)

DELETE FROM dalmuri."TODAY_WORD_TOPIC";

• dalmuri 스키마의 "TODAY_WORD_TOPIC" 테이블에 있는 모든 행을 삭제

3️⃣ 새로운 무작위 토픽 설정

PERFORM dalmuri.get_random_topic_fix();

• PERFORM 명령어를 사용하여 다른 함수인 dalmuri.get_random_topic_fix()를 호출하고 실행
• PERFORM : 함수가 반환하는 값은 무시하고, 함수의 내부 작업(side effect)만 실행할 때 사용

👽 지피티가 알려주는 PERFORM문👽

 

"겉으로 보기엔 아무것도 안하는 것처럼 보이지만 뭔가 하고 있음"

함수가 뭔가 값을 돌려주긴 하지만 그 값을 굳이 받을 필요는 없고, 함수의 내부 행동(INSERT, UPDATE, DELETE)만 실행하고 싶을 때 쓰는 명령어.

 

대부분 함수 호출은

SELECT dalmuri.get_random_topic_fix();

처럼 생겼지만 SELECT는 반환값을 받아서 테이블 형태로 보여주는 목적이 있다.

반면 DB 내부에서 돌아가는 함수들은 대부분 부수효과(side effect)를 일으키려고 쓰는 경우가 많다.

• 자동으로 insert하거나
• 로그를 남기거나
• 어떠한 계산을 트리거로 수행하거나

이럴때 굳이 SELECT문을 작성하면 "결과 값이 없습니다" 등의 형태로 쓸데없는 반환값이 따라오는데,

이때 PERFORM을 사용하면 결과는 버리고("결과값이 없습니다"라는 쓸데없는걸 버림) 함수만 실행한다.

PERFORM dalmuri.get_random_topic_fix();

 이렇게 작성한다면 함수의 리턴값(="결과값이 없습니다")는 버리고 함수 안에서 벌어지는 일만 실행한다. 

3. 스케줄링문 작성

SELECT cron.schedule(
    'daily-topic-reset',                 -- 1. 작업 이름 (job_name)
    '0 0 * * *',                         -- 2. Cron 스케줄 (schedule)
    'SELECT dalmuri.reset_today_topic();' -- 3. 실행할 SQL 명령 (command)
);

pg_cron 확장 모듈에 새로운 스케줄링 작업을 등록했다. 

• 작업 이름 : 관리 목적으로 사용하는 고유한 이름
• Cron 스케줄 : 작업을 실행할 시간을 정의하는 표준 크론 표현식
• SQL 명령 : 스케줄에 맞추어 실제로 실행될 SQL 코드

라고 했는데 실행이 안됬따ㅋ 진짜 오만가지 명령어를 다썼다

 

1. 스케줄링 변경

SELECT cron.alter_job(
    (SELECT jobid FROM cron.job WHERE jobname = 'daily-topic-reset'), -- 1. job_id
    '0 15 * * *',                                                    -- 2. 새로운 Cron 스케줄
    NULL                                                             -- 3. command (기존 유지)
);

jobid를 사용해야 하므로 작업 이름을 통해 jobid를 조회하는 서브쿼리를 사용해야 했다. 

2. 등록된 모든 스케줄링 작업 조회

SELECT * FROM cron.job;

 2-1 특정 컬럼의 등록된 작업 조회

SELECT jobid, jobname, schedule, active, database, username
FROM cron.job
WHERE jobname = 'daily-topic-reset';

3. 스케줄러 프로세스 활성화 확인

SELECT application_name, backend_type, state
FROM pg_stat_activity
WHERE backend_type = 'pg_cron launcher' OR application_name LIKE 'pg\_cron%';

PostgreSQL 서버에서 실행 중인 프로세스 목록(pg_stat_activity)를 조회하여 pg_cron의 핵심 프로세스인 pg_cron launcher가 정상적으로 실행되는지 확인하는 쿼리이다.

pg_cron launcher가 실행중이어야 스케줄링 작업이 트리거될 수 있기 때문이다.

state가 null일 때 프로세스가 정상적으로 시작되어 다음 스케줄 시간 대기중(휴면상태)이라는 의미다.

(참고로 나도 null이 나왔다)

 

4. 시간대 확인 👈여기서 오류 발견

SHOW timezone;

이걸 실행해보니,

이게 나왔다. 

이게 왜 문제냐면, 한국 시간은 UTC가 아닌 KST를 사용해야 하기 때문이다. 

그러므로 UTC를 기준으로

이런식으로 작성하면, UTC 기준의 시간대로 오전 12시이기 때문에 한국 시간으론 오후 3시에 실행되는 스케줄링이다.

한국 입장에선 오후 3시로 설정해놓고 왜 오전 12시에 스케줄링 실행이 안된거지?? 한거다!

UTC - 협정 세계시	국제적인 시간 표준으로, 런던 그리니치 천문대 시각 기준
KST - 한국 표준시	한국이 사용하는 표준시로, UTC보다 9시간 빠름

따라서 스케줄링에서 시간을 표시할 때 

'0 15 * * *',

이렇게 작성해야 내가 원하는 한국의 오전 12시 실행이 완성되는거다.

어쩐지 9시 45분에 insert된걸 왜 0시 45분에 insert됬다고 뜬건지...!!! 나중가서야 알아챘다...

 

🕒 Cron 표현식 구조

Cron 표현식은 별도의 5개 필드이며 각 필드별로 특정 시간 단위를 나타낸다.

순서	필드	의미	허용 범위
1	*	분(Minute)	0 ~ 59
2	*	시(Hour)	0 ~ 23
3	*	일(Day)	1 ~ 31
4	*	월(Month)	1 ~ 12
5	*	요일(Week)	0 ~7

따라서 내가 작성한 '0 15 * * * ' 이거는

• 매 시간 0분(정각)
• 하루 중 15시(=오후 3시)
• 매일 (*)
• 매월 (*)
• 매 요일 (*)

이라는 뜻이었다...

참고로 Supabase에선 타임존을 KST로 바꾸는 방법이 없어서 UTC 기준으로 맞췄다.

 

결과

실험한대로 주제도 잘 들어갔고, 댓글도 지워졌다. 만족!

 

[참고 자료]

https://silver-line-blog.tistory.com/3

UTC와 KST, 헷갈려서 정리해봤습니다