트러블 슈팅 - 제약조건 변경에 따른 프로필 생성 누락

최종 수정: 2025.09.11

트러블 슈팅 - 제약조건 변경에 따른 프로필 생성 누락

 

수입과 지출 각각의 카테고리에 동일한 이름을 등록할 수 없는 문제가 있었다. 그 이유는 제약조건에 있었다.

 

현재 categories 테이블에 UNIQUE(user_id, name) 제약조건이 있는 상황이다. 우선 이것을 제거하자.

-- 기존 제약조건 제거
ALTER TABLE categories
DROP CONSTRAINT IF EXISTS categories_user_id_name_key;

 

새로운 제약 조건으로 type을 추가하는 것이다. UNIQUE(user_id, name, type)

type을 통해 수입에 관한 것인지, 지출에 관한 것인지도 함께 구별함으로써 더 특별한 제약조건을 만들었다.

-- 새로운 제약조건 (user_id, name, type 조합)
ALTER TABLE categories
ADD CONSTRAINT categories_user_id_name_type
UNIQUE(user_id, name, type);

---

기존에 사용하기 위한 계정은 문제가 없었다. 지인들에게 테스트를 요청했다. 그런데, 여러 문제가 나타났다.

1. 이메일 인증까지는 잘 됐으나, 프로필이 생성되지 않았다.

stop 1. 인증이 완료됐는지 확인하니, profile_exists 값이 null 이었다.

SELECT
  au.id,
  au.email,
  au.email_confirmed_at,
  p.id as profile_exists
FROM auth.user au
LEFT JOTIN profiles p ON au.id = p.id
WHERE p.id IS NULL;

 

 

step 2. 관련 트리거와 함수에 문제가 있는지 살펴봤다.

-- 트리거 확인
SELECT
  trigger_name,					-- on_auth_user_created
  event_manipulation,				-- INSERT
  action_timing,				-- AFTER
  action_statement				-- EXECUTE FUNCTION handle_new_user()
FROM infomation_schema.triigers
WHERE event_object_table = 'user'
AND trigger_scema = 'auth';


-- 함수 확인
SELECT
  proname as function_name,			-- handle_new_user
  prosrc as function_body			-- ) ON CONFLICT (id) DO NOTHING;
FROM pg_proc
WHERE proname = 'handle_new_user';

 

2) 프로필이 생성되지 않으니, 기능 테스트가 불가능했다.

step 1. 동일하게 나도 진행해보았다. 그랬더니 신기한 점을 발견할 수 있었다. 이메일 인증 버튼을 클릭하면, "localhost에서 연결을 거부했습니다." 라는 메시지가 나타난다.

 

step 2. 그런데, 로그인은 되었다. Local storage에는 access_token도 존재했다. 아래 사진처럼, 회원가입할 때 정한 닉네임도 버젓이 등장한다.

 

step 3. 그럼에도, 다른 기능들은 동작하지 않았고, 위에서 살펴본 것처럼 profile_exists 값은 null이다. 즉, 확인할 수 있는 사실은 다음과 같다.

- 회원가입할 때, 입력한 값들은 유효하다. (아래 SQL문을 보면 프로필 생성까지는 순차적으로 잘 동작했음을 유추할 수 있다.)

- 단지, profile이 생성되지 않았을 뿐이다.

- 그래서 profile과 연결된 다른 기능들을 사용하지 못한다.

 

Q. 왜 이런 일이 발생했나?

-- 8. 이메일 인증 후 프로필 및 카테고리 생성 함수 (수정된 버전)
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  -- 이메일 인증이 완료되었을 때만 실행
  IF NEW.email_confirmed_at IS NOT NULL AND (OLD.email_confirmed_at IS NULL) THEN
    -- 프로필 생성
    INSERT INTO profiles (id, email, name, nickname) VALUES (
      NEW.id,
      NEW.email,
      COALESCE((NEW.raw_user_meta_data->>'name')::text, 'Unknown User'),
      (NEW.raw_user_meta_data->>'nickname')::text
    ) ON CONFLICT (id) DO NOTHING;
    
    -- 기본 카테고리 생성
    INSERT INTO categories (user_id, name, type, is_default) VALUES
      (NEW.id, '통신비', 'expense_fixed', true),
      (NEW.id, '교통비', 'expense_variable', true)
    ON CONFLICT (user_id, name) DO NOTHING;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

handle_new_user 함수의 기능은 이메일 인증 후 프로필 및 카테고리를 생성해준다. 그런데, 위에서 테이블의 제약 조건을 변경하면서, handle_new_user 함수(이메일 인증 후 프로필 및 카테고리 생성 함수)의 기본 카테고리를 생성해주는 INSERT 부분의 ON CONFLECT 절에서 (user_id, name) 부분을 수정하지 않아서 발생한 문제였다.

 

해결

step 1. CONFLICT 절을 위에서 제약조건을 변경한 것과 동일(user_id, name, type)하게 변경했다.

step 2. 기존 인증된 사용자들의 프로필을 수동으로 생성 (아래 SQL 참고) => database에서 확인하니 profile 정보가 나타났다.

step 3. 새롭게 회원가입 시도 -> 이메일 인증과 프로필 생성이 모두 정상적으로 동작했다.

-- 1. 기존 인증된 사용자들의 프로필을 수동으로 생성
INSERT INTO profiles (id, email, name, nickname)
SELECT 
  au.id,
  au.email,
  COALESCE((au.raw_user_meta_data->>'name')::text, 'Unknown User') as name,
  (au.raw_user_meta_data->>'nickname')::text as nickname
FROM auth.users au
LEFT JOIN profiles p ON au.id = p.id
WHERE au.email_confirmed_at IS NOT NULL  -- 이메일 인증 완료된 사용자
  AND p.id IS NULL                       -- 아직 프로필이 없는 사용자
ON CONFLICT (id) DO NOTHING;

-- 2. 해당 사용자들의 기본 카테고리도 생성
INSERT INTO categories (user_id, name, type, is_default)
SELECT 
  au.id,
  '통신비',
  'expense_fixed',
  true
FROM auth.users au
LEFT JOIN profiles p ON au.id = p.id
WHERE au.email_confirmed_at IS NOT NULL
  AND p.id IS NOT NULL  -- 프로필이 있는 사용자만
  AND NOT EXISTS (      -- 아직 카테고리가 없는 사용자만
    SELECT 1 FROM categories c 
    WHERE c.user_id = au.id 
    AND c.name = '통신비' 
    AND c.type = 'expense_fixed'
  )
ON CONFLICT (user_id, name, type) DO NOTHING;