PostgreSQL

Database Sequence

• 테이블을 정상적으로 옮겼지만 이후에 필요없어진 테이블을 드롭하면서 sequence에 의한 제약으로 인해서 정상적으로 삭제가 되지 않는 에러가 발생

Sequnce 조회 및 확인

SELECT s.relname AS sequence, n.nspname AS SCHEMA, t.relname AS TABLE
FROM pg_class s
JOIN pg_depend d ON d.objid=s.oid AND d.classid='pg_class':: REGCLASS AND d.refclassid='pg_class':: REGCLASS
JOIN pg_class t ON t.oid=d.refobjid
JOIN pg_namespace n ON n.oid=t.relnamespace
JOIN pg_attribute a ON a.attrelid=t.oid AND a.attnum=d.refobjsubid
WHERE s.relkind='S' AND d.deptype='a'

• 위의 쿼리를 실행하면 아래와 같이 시퀀스가 위치한 스키마와 테이블이 보임

Table PK와 시퀀스

• 흔히 \d 옵션을 통해서 다음과 같은 정보를 볼 수 있다.

• 주목해야할 점은 id의 기본값이 nexval('piks_category_id_seq')라는 것
  • SELECT를 이용하여 위의 함수가 의미하는 바를 확인 가능

• 시퀀스가 가지고 있는 값의 다음값을 자동으로 할당
• 정리하면, 테이블에 데이터를 집어넣는 과정에서 PK를 신경쓰지 않는 이유는 테이블 마다 시퀀스가 정해져 있고 이 시퀀스의 함수에 의해서 다음 값이 자동으로 할당되기 때문
  • 그렇다면 컬럼(필드)의 기본값을 없앨 수도 있을까? 그렇다!

• 위의 테이블에서 DROP DEFAULT를 하게되면 아래와 같이 기본값이 사라지게 됨
  

• 그렇다면 다시 복구(추가)도 가능할까? 그렇다!
  • 위의 명령어를 그대로 사용해주면 다시 기본값에 시퀀스가 할당되게 됨

그래서 뭐가 문제?

• 처음에 생각했던 문제는 바로 dummy_linkhub_link 테이블의 기본값이 linkhub_link의 시퀀스 값과 같아서라고 생각

• 첫번째 가설 → 기본값이 겹처서 생기는 문제
  

• 어떤 테이블의 기본값이 시퀀스 + 함수로 설정된 값이라 하더라도 삭제는 가능

• 위의 메시지를 보면 linkhub_link의 기본값(시퀀스)가 다른 테이블에서 의존적인 관계를 맺고 있다는 에러 메시지를 출력

  • 단순히 기본값 문제가 아니라, 기본값이 다른 테이블과 맺고 있는 의존성 때문
  • 테이블의 기본값을 제거했음에도 해결이 안됐던 이유도 여기에 있음

• 다시 위로 올라가서 가장 위의 쿼리를 실행한 결과를 보면 시퀀스를 소유하고 있는 스키마와 테이블을 볼 수 있음

  • 바로 이곳의 의존성 문제 때문!
  • 정답: 시퀀스 정보 테이블에 있는 FK 의존성 문제

• 정리하면 기존의 시퀀스 정보를 저장할 때, 시퀀스의 소유자(유저), 장소(스키마), 소유 테이블 및 필드까지 다양한 정보를 FK로 저장
  • CASCADE 옵션을 통해서 FK가 존재하지 않게되면 자동으로 테이블을 삭제하면서 시퀀스를 삭제할 수 있지만 이 기본값을 linkhub_link 테이블이 사용하고 있기 때문에 우리는 이 방법을 원하지 않음
  • 가장 간단한 방법은 위의 방법처럼 FK constraint를 제거하는 것
    • 우리가 삭제하려는 dummy_link가 아닌 linkhub_lnk로 바꿔주면 완료!