LIKE 문 사용
LIKE 및 인덱스
데이터베이스 열을 신뢰할 수 있게 참조하기
- Good (prefer)
- Bad (avoid)
ID 가져오기
ApplicationRecord에서 상속받기
UNION 사용하기
- UNION 서브쿼리에서 열이 불균형한 경우
생성 날짜별로 정렬
WHERE IN 대신 WHERE EXISTS 사용
.find_or_create_by는 원자적이지 않음
- .safe_find_or_create_by를 사용할 수 있을까요?
대안 1: UPSERT
- 대안 2: 존재 여부 확인하고 복구
프로덕션에서 SQL 쿼리 모니터링
공통 테이블 표현식 사용 시기

SQL 쿼리 지침

본 문서는 SQL 쿼리를 작성할 때 따를 여러 지침을 설명합니다. ActiveRecord/Arel 또는 원시 SQL 쿼리를 사용하는 경우에 해당됩니다.

`LIKE` 문 사용

데이터를 검색하는 가장 일반적인 방법은 LIKE 문을 사용하는 것입니다. 예를 들어, “Draft:”로 시작하는 제목을 가진 모든 이슈를 가져오려면 다음 쿼리를 작성할 수 있습니다.

SELECT *
FROM issues
WHERE title LIKE 'Draft:%';

PostgreSQL에서 LIKE 문은 대소문자를 구분합니다. 대소문자를 구분하지 않으려면 PostgreSQL에서는 ILIKE를 사용해야 합니다.

이를 자동으로 처리하려면 PostgreSQL에서는 Arel을 사용하여 LIKE 쿼리를 사용해야 합니다.

Issue.where('title LIKE ?', 'Draft:%')

대신에 다음과 같이 작성해야 합니다.

Issue.where(Issue.arel_table[:title].matches('Draft:%'))

여기서 matches는 사용 중인 데이터베이스에 따라 올바른 LIKE / ILIKE 문을 생성합니다.

여러 개의 OR 조건을 연결해야 하는 경우 Arel을 사용하여 다음과 같이 수행할 수도 있습니다.

table = Issue.arel_table

Issue.where(table[:title].matches('Draft:%').or(table[:foo].matches('Draft:%')))

PostgreSQL에서는 다음과 같이 생성됩니다.

SELECT *
FROM issues
WHERE (title ILIKE 'Draft:%' OR foo ILIKE 'Draft:%')

`LIKE` 및 인덱스

PostgreSQL은 시작 부분에 와일드카드를 사용하는 LIKE / ILIKE를 사용할 때 어떤 인덱스도 사용하지 않습니다. 예를 들어, 다음과 같이 인덱스를 사용하지 않습니다.

SELECT *
FROM issues
WHERE title ILIKE '%Draft:%';

ILIKE의 값이 와일드카드로 시작하기 때문에 데이터베이스는 인덱스를 어디서부터 검색해야 하는지 모르기 때문에 인덱스를 사용할 수 없습니다.

다행히도, PostgreSQL은 해결책을 제공합니다: trigram Generalized Inverted Index (GIN) 인덱스입니다. 이러한 인덱스는 다음과 같이 생성할 수 있습니다.

CREATE INDEX [CONCURRENTLY] index_name_here
ON table_name
USING GIN(column_name gin_trgm_ops);

여기서 중요한 부분은 GIN(column_name gin_trgm_ops)입니다. 이렇게 하면 연산자 클래스가 gin_trgm_ops로 설정된 GIN 인덱스가 생성됩니다. 이러한 인덱스는 ILIKE / LIKE를 사용할 수 있으며 성능을 크게 향상시킬 수 있습니다. 이러한 인덱스의 단점 중 하나는 데이터가 많을수록(인덱스화된 데이터 양에 따라) 크기가 커질 수 있다는 것입니다.

이러한 인덱스의 명명을 일관되게 유지하기 위해 다음과 같은 명명 패턴을 사용해야 합니다.

index_TABLE_on_COLUMN_trigram

예를 들어, issues.title에 대한 GIN/trigram 인덱스는 index_issues_on_title_trigram로 지정해야 합니다.

이러한 인덱스를 구축하는 데 상당한 시간이 걸리기 때문에 동시에 구축되어야 합니다. 이를 위해 CREATE INDEX 대신 CREATE INDEX CONCURRENTLY를 사용할 수 있습니다. 동시 인덱스는 트랜잭션 내에서 생성할 수 없습니다. 마이그레이션에 대한 트랜잭션을 비활성화하려면 다음 패턴을 사용할 수 있습니다.

class MigrationName < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!
end

예시:

class AddUsersLowerUsernameEmailIndexes < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!
  
  def up
    execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_username ON users (LOWER(username));'
    execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_email ON users (LOWER(email));'
  end
  
  def down
    remove_index :users, :index_on_users_lower_username
    remove_index :users, :index_on_users_lower_email
  end
end

데이터베이스 열을 신뢰할 수 있게 참조하기

기본적으로 ActiveRecord는 질의된 데이터베이스 테이블에서 모든 열을 반환합니다. 경우에 따라 반환된 행을 사용자 정의해야 할 수 있습니다.

예시: - 데이터베이스로부터 반환되는 데이터 양을 줄이기 위해 일부 열만 지정 - JOIN 관계에서 열 포함 - 계산 수행 (SUM, COUNT)

다음 예제에서는 열을 지정하지만 열에 대한 테이블을 지정하지 않습니다.

projects 테이블의 path
merge_requests 테이블의 user_id

쿼리:

# bad, avoid
Project.select("path, user_id").joins(:merge_requests) # SELECT path, user_id FROM "projects" ...

나중에 새로운 기능이 projects 테이블에 추가된다면 (user_id), 이러한 기간에 쿼리가 실행되면 다음과 같은 오류 메시지로 실패할 수 있습니다: PG::AmbiguousColumn: ERROR: column reference "user_id" is ambiguous

이 문제는 데이터베이스에서 열을 선택하는 방식에 의해 발생합니다. user_id 열은 users 및 merge_requests 테이블 모두에 존재합니다. 쿼리 플래너는 user_id 열을 조회할 때 어떤 테이블을 사용해야 하는지 결정할 수 없습니다.

사용자 정의 SELECT 문을 작성할 때는 명시적으로 테이블 이름과 열을 지정하는 것이 좋습니다.

Good (prefer)

Project.select(:path, 'merge_requests.user_id').joins(:merge_requests)

# SELECT "projects"."path", merge_requests.user_id as user_id FROM "projects" ...

Project.select(:path, :'merge_requests.user_id').joins(:merge_requests)

# SELECT "projects"."path", "merge_requests"."id" as user_id FROM "projects" ...

Arel을 사용한 예시 (arel_table):

Project.select(:path, MergeRequest.arel_table[:user_id]).joins(:merge_requests)

# SELECT "projects"."path", "merge_requests"."user_id" FROM "projects" ...

원시 SQL 쿼리를 작성할 때:

SELECT projects.path, merge_requests.user_id FROM "projects"...

원시 SQL 쿼리에 매개변수화된 경우(이스케이핑이 필요한 경우):

include ActiveRecord::ConnectionAdapters::Quoting

"""
SELECT
  #{quote_table_name('projects')}.#{quote_column_name('path')},
  #{quote_table_name('merge_requests')}.#{quote_column_name('user_id')}
FROM ...
"""

Bad (avoid)

Project.select('id, path, user_id').joins(:merge_requests).to_sql

# SELECT id, path, user_id FROM "projects" ...

Project.select("path", "user_id").joins(:merge_requests)
# SELECT "projects"."path", "user_id" FROM "projects" ...

# or

Project.select(:path, :user_id).joins(:merge_requests)
# SELECT "projects"."path", "user_id" FROM "projects" ...

열 디렉터리이 주어지면 ActiveRecord는 인수와 projects 테이블에서 정의된 열을 일치시키려고 시도하고 테이블 이름을 자동으로 앞에 추가하려고 합니다. 이 경우 id 열은 문제가 되지 않지만 user_id 열은 예상치 못한 데이터를 반환할 수 있습니다.

Project.select(:id, :user_id).joins(:merge_requests)

# 배포 전 (user_id는 merge_requests 테이블에서 가져옴):
# SELECT "projects"."id", "user_id" FROM "projects" ...

# 배포 후 (user_id는 projects 테이블에서 가져옴):
# SELECT "projects"."id", "projects"."user_id" FROM "projects" ...

ID 가져오기

ActiveRecord의 pluck를 사용하여 값 집합을 메모리로 가져와 다른 쿼리의 인수로 사용하지 마십시오. 예를 들어, 다음은 추가적인 불필요한 데이터베이스 쿼리를 실행하고 많은 불필요한 데이터를 메모리로 로드합니다:

projects = Project.all.pluck(:id)

MergeRequest.where(source_project_id: projects)

대신에 성능이 훨씬 더 좋은 서브쿼리를 사용할 수 있습니다:

MergeRequest.where(source_project_id: Project.all.select(:id))

실제로 루비에서 값에 작업을 수행해야 하는 경우에만 pluck를 사용해야 합니다(예: 파일에 쓰기). 거의 모든 다른 경우에는 “서브쿼리를 사용할 수 없을까?” 라고 자문해야 합니다.

우리의 CodeReuse/ActiveRecord cop을 준수하여, 모델 코드 내에서 (:id)나 (:user_id)와 같은 형식을 사용해야 합니다. 전자의 경우 ApplicationRecord에서 제공하는.pluck_primary_key 도우미 메서드를 대신 사용할 수 있습니다. 후자의 경우 관련된 모델에 작은 도우미 메서드를 추가해야 합니다.

pluck를 사용할 강력한 이유가 있다면 검색된 레코드 수를 제한하는 것이 의미가 있을 수 있습니다. MAX_PLUCK는 ApplicationRecord에서 기본적으로 1,000입니다.

ApplicationRecord에서 상속받기

GitLab 코드베이스의 대부분의 모델은 ActiveRecord::Base가 아닌 ApplicationRecord 또는 Ci::ApplicationRecord에서 상속해야 합니다. 이를 통해 도우미 메서드를 쉽게 추가할 수 있습니다.

이 규칙에는 데이터베이스 마이그레이션에서 생성된 모델에 대한 예외도 있습니다. 애플리케이션 코드와 격리되어 있어야 하므로 이러한 모델은 계속해서 MigrationRecord에서 하위 클래스를 만들어야 합니다.

UNION 사용하기

UNION은 대부분의 Rails 애플리케이션에서는 사용되지 않지만 매우 강력하고 유용합니다. 쿼리는 관련 데이터 또는 특정 기준에 따라 데이터를 가져오기 위해 많은 JOIN을 사용하지만 JOIN 성능은 관련된 데이터가 증가함에 따라 빠르게 저하될 수 있습니다.

예를 들어, 이름에 특정 값을 포함하는 프로젝트 디렉터리 또는 해당 네임스페이스의 이름에 특정 값을 포함하는 프로젝트 디렉터리을 가져오려는 경우 대부분의 사람들은 다음 쿼리를 작성할 것입니다:

SELECT *
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE projects.name ILIKE '%gitlab%'
OR namespaces.name ILIKE '%gitlab%';

이런 방식으로 큰 데이터베이스를 사용하면 이 쿼리가 쉽게 800밀리초 정도 소요될 수 있습니다. UNION을 사용하면 다음과 같이 작성하게 될 것입니다:

SELECT projects.*
FROM projects
WHERE projects.name ILIKE '%gitlab%'

UNION

SELECT projects.*
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE namespaces.name ILIKE '%gitlab%';

이 쿼리는 정확히 같은 레코드를 반환하면서 완료하는 데 약 15밀리초 정도만 소요됩니다.

이는 모든 곳에서 UNION을 시작해서는 안 된다는 의미는 아니지만, 쿼리에서 많은 JOIN을 사용하고 조인된 데이터를 기반으로 레코드를 필터링할 때 고려해야 할 사항입니다.

GitLab에는 여러 ActiveRecord::Relation 객체의 UNION을 빌드할 수 있는 Gitlab::SQL::Union 클래스가 함께 제공됩니다. 이 클래스를 다음과 같이 사용할 수 있습니다:

union = Gitlab::SQL::Union.new([projects, more_projects, ...])

Project.from("(#{union.to_sql}) projects")

FromUnion 모델 concern은 동일한 결과를 생성하기 위한 더 편리한 방법을 제공합니다:

class Project
  include FromUnion
  ...
end

Project.from_union(projects, more_projects, ...)

UNION은 코드베이스 전반에 일반적으로 사용되지만 EXCEPT와 INTERSECT와 같은 다른 SQL 집합 연산자를 사용하는 것도 가능합니다:

class Project
  include FromIntersect
  include FromExcept
  ...
end

intersected = Project.from_intersect(all_projects, project_set_1, project_set_2)
excepted = Project.from_except(all_projects, project_set_1, project_set_2)

`UNION` 서브쿼리에서 열이 불균형한 경우

UNION 쿼리에 SELECT 절의 열이 불균형한 경우 데이터베이스에서 오류가 발생합니다. 다음과 같은 UNION 쿼리를 고려해보십시오:

SELECT id FROM users WHERE id = 1
UNION
SELECT id, name FROM users WHERE id = 2
end

이 쿼리는 다음 오류 메시지를 반환합니다:

each UNION query must have the same number of columns

이 문제는 명백하며 개발 중에 쉽게 수정할 수 있습니다. UNION 쿼리가 ActiveRecord 스키마 캐시에서 나온 명시적인 열 디렉터리이 있는 경우 문제가 발생합니다.

예시 (나쁨, 피하세요):

scope1 = User.select(User.column_names).where(id: [1, 2, 3]) # 열을 명시적으로 선택함
scope2 = User.where(id: [10, 11, 12]) # SELECT users.*를 사용함

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

이 코드가 배포된 경우, 즉시 문제를 일으키지는 않습니다. 그러나 다른 개발자가 users 테이블에 새로운 데이터베이스 열을 추가하면 이 쿼리가 프로덕션 환경에서 실패하고 다운타임을 야기할 수 있습니다. 두 번째 쿼리 (SELECT users.*)는 새로 추가된 열을 포함하고 있지만 첫 번째 쿼리는 포함하고 있지 않습니다. column_names 메서드는 (ActiveRecord 스키마 캐시 내에서 캐시되는) 구식 값(새로운 열이 누락됨)을 반환합니다. 이러한 값들은 일반적으로 애플리케이션 부팅 시에 채워집니다.

이 지점에서 유일한 수정 방법은 스키마 캐시가 업데이트되도록 전체 애플리케이션을 다시 시작하는 것입니다. GitLab 16.1 이후로는 스키마 캐시가 자동으로 재설정되어 이후 쿼리가 성공합니다. 이 재설정은 ops 피처 플래그 reset_column_information_on_statement_invalid를 비활성화함으로써 비활성화할 수 있습니다.

이 문제는 항상 SELECT users.*를 사용하거나 열을 명시적으로 정의하는 경우에만 피할 수 있습니다.

SELECT users.* 사용하기:

# 나쁘며, 피하세요
scope1 = User.select(User.column_names).where(id: [1, 2, 3])
scope2 = User.where(id: [10, 11, 12])

# 좋음, 두 쿼리 모두 SELECT users.*를 생성함
scope1 = User.where(id: [1, 2, 3])
scope2 = User.where(id: [10, 11, 12])

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

명시적 열 디렉터리 정의:

# 좋음, SELECT 열이 일관되게 정의됨
columns = User.cached_column_list # 헬퍼는 완전히 자격이 주어진 (테이블.열) 열 이름들을 반환함 (Arel)
scope1 = User.select(*columns).where(id: [1, 2, 3]) # 열을 명시적으로 선택함
scope2 = User.select(*columns).where(id: [10, 11, 12]) # SELECT users.*를 사용함

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

생성 날짜별로 정렬

레코드를 생성된 시간을 기준으로 정렬할 때 created_at으로 정렬하는 대신에 id 열로 정렬할 수 있습니다. 왜냐하면 ID는 항상 고유하고 행이 생성된 순서대로 증가되기 때문에 동일한 결과를 얻을 수 있습니다. 이는 created_at에 인덱스를 추가할 필요가 없음을 의미하며, 기본적으로 id가 이미 인덱싱되어 일관된 성능을 보장하기 때문입니다.

`WHERE IN` 대신 `WHERE EXISTS` 사용

WHERE IN과 WHERE EXISTS은 동일한 데이터를 생성할 수 있지만 가능한 경우에는 WHERE EXISTS를 사용하는 것이 좋습니다. 많은 경우 PostgreSQL은 WHERE IN을 잘 최적화할 수 있지만 WHERE EXISTS가 (대부분) 더 잘 수행되는 경우도 많이 있습니다.

Rails에서는 이를 SQL 조각으로 생성하여 사용해야 합니다:

Project.where('EXISTS (?)', User.select(1).where('projects.creator_id = users.id AND users.foo = X'))

그러면 다음과 같은 쿼리가 생성됩니다:

SELECT *
FROM projects
WHERE EXISTS (
    SELECT 1
    FROM users
    WHERE projects.creator_id = users.id
    AND users.foo = X
)

`.find_or_create_by`는 원자적이지 않음

.find_or_create_by와 .first_or_create와 같은 메소드의 내재된 패턴은 원자적이지 않습니다. 즉, 먼저 SELECT를 실행하고 결과가 없는 경우 INSERT가 수행됩니다. 동시 프로세스를 염두에 둔다면 경합 조건이 발생하여 유사한 레코드를 두 개 삽입하려고 할 수 있습니다. 이는 원하는 바가 아니거나, 제약 조건 위반으로 인해 쿼리 중 하나가 실패할 수 있습니다.

트랜잭션을 사용해도 이 문제를 해결할 수 없습니다.

이 문제를 해결하기 위해 우리는 ApplicationRecord.safe_find_or_create_by를 추가했습니다.

이 메소드는 find_or_create_by와 동일한 방식으로 사용할 수 있지만 호출을 새로운 트랜잭션(또는 서브트랜잭션)으로 래핑하고, ActiveRecord::RecordNotUnique 오류 때문에 실패할 경우 재시도합니다.

이 메소드를 사용하려면 사용하려는 모델이 ApplicationRecord에서 상속되어 있는지 확인하십시오.

Rails 6 이상에서는 .create_or_find_by 메소드가 있습니다. 이 메소드는 우리의 .safe_find_or_create_by 메소드와 다르지만 INSERT를 수행한 다음 호출이 실패하는 경우에만 SELECT 명령을 수행합니다.

INSERT에 실패하면 죽은 튜플을 남기고 기본 키 시퀀스를 증가시키며, 기타 단점이 있습니다.

만약 공통 경로가 처음 생성된 후 재사용되는 단일 레코드를 갖고 있는 경우 우리는 .safe_find_or_create_by를 선호합니다. 그러나 더 일반적인 경로가 새로운 레코드를 생성하는 것이고, 우리가 에지 케이스(예: 작업 재시도)에서 중복 레코드가 삽입되는 것을 피하고 싶은 경우 .create_or_find_by는 SELECT를 저장할 수 있습니다.

모든 메소드는 기존 트랜잭션의 컨텍스트에서 실행될 경우 내부적으로 서브트랜잭션을 사용합니다. 이는 단일 트랜잭션 내에서 64개 이상의 라이브 서브트랜잭션이 사용되는 경우에 특히 전반적인 성능에 중요한 영향을 미칠 수 있습니다.

`.safe_find_or_create_by`를 사용할 수 있을까요?

귀하의 코드가 일반적으로 격리되어 있다면(예: 워커에서만 실행) 다른 트랜잭션으로 래핑되지 않고 실행된다면 .safe_find_or_create_by를 사용할 수 있습니다. 그러나 다른 사람이 귀하의 코드를 트랜잭션 내에서 호출하는 경우를 잡아내기 위한 도구가 없음에 주의하십시오. .safe_find_or_create_by를 사용하는 것은 현재 완전히 제거할 수 없는 몇 가지 위험을 불러일으킬 것입니다.

또한, Performance/ActiveRecordSubtransactionMethods라는 RuboCop 규칙이 있어 .safe_find_or_create_by의 사용을 방지합니다. 이 규칙은 # rubocop:disable Performance/ActiveRecordSubtransactionMethods를 통해 경우에 따라 비활성화할 수 있습니다.

대안 1: `UPSERT`

테이블이 고유 인덱스로 지원될 때 .upsert 메소드는 대안 솔루션이 될 수 있습니다.

.upsert 메소드의 간단한 사용법:

BuildTrace.upsert(
  {
    build_id: build_id,
    title: title
  },
  unique_by: :build_id
)

주의할 점 몇 가지:

기본 키 시퀀스는 레코드가 업데이트되지 않았더라도 증가됩니다.
생성된 레코드는 반환되지 않습니다. returning 옵션은 INSERT가 발생할 때(새 레코드)만 데이터를 반환합니다.
ActiveRecord 유효성 검사가 실행되지 않습니다.

유효성 검사와 레코드 로딩이 포함된 .upsert 메소드의 예시:

params = {
  build_id: build_id,
  title: title
}

build_trace = BuildTrace.new(params)

unless build_trace.valid?
  raise '여기에서 사용자에게 알림'
end

BuildTrace.upsert(params, unique_by: :build_id)

build_trace = BuildTrace.find_by!(build_id: build_id)

# 여기서 build_trace로 무언가 수행

위의 코드 조각은 build_id 열에 모델 수준의 고유성 유효성 검사가 있는 경우 잘 작동하지 않을 것입니다. 왜냐하면 우리는 .upsert를 호출하기 전에 유효성 검사를 호출하기 때문입니다.

이를 해결하려면 두 가지 옵션이 있습니다:

ActiveRecord 모델에서 고유성 유효성 검사를 제거합니다.
on 키워드를 사용하고 컨텍스트별 유효성 검사를 구현합니다.

대안 2: 존재 여부 확인하고 복구

동시에 동일한 레코드를 생성할 가능성이 매우 낮은 경우 간단한 접근 방법을 사용할 수 있습니다:

def my_create_method
  params = {
    build_id: build_id,
    title: title
  }
  
  build_trace = BuildTrace
    .where(build_id: params[:build_id])
    .first
  
  build_trace = BuildTrace.new(params) if build_trace.blank?
  
  build_trace.update!(params)

rescue ActiveRecord::RecordInvalid => invalid
  retry if invalid.record&.errors&.of_kind?(:build_id, :taken)
end

해당 메소드는 다음 작업을 수행합니다:

고유한 열로 모델 조회
레코드를 찾지 못한 경우 새로운 레코드를 생성
레코드를 지속

룩업 쿼리와 지속 쿼리 사이에 짧은 경합 조건이 있어 다른 프로세스가 레코드를 삽입하고 ActiveRecord::RecordInvalid 예외를 발생할 수 있습니다.

코드는 이 특정 예외를 복구하고 작업을 재시도합니다. 두 번째 실행에서 레코드는 성공적으로 찾아질 것입니다. 예를 들어 PreventApprovalByAuthorService의 이 코드 블록을 확인하십시오.

프로덕션에서 SQL 쿼리 모니터링

GitLab 팀원들은 PostgreSQL 로그를 사용하여 GitLab.com에서 느린 쿼리나 취소된 쿼리를 모니터링할 수 있습니다. 이 로그는 Elasticsearch에 색인되어 Kibana를 사용하여 검색할 수 있습니다.

더 많은 세부 정보는 런북를 참조하세요.

공통 테이블 표현식 사용 시기

공통 테이블 표현식(CTE)을 사용하여 보다 복잡한 쿼리 내에서 임시 결과 집합을 생성할 수 있습니다. 또한 재귀 CTE를 사용하여 쿼리 내에서 CTE의 결과 집합을 참조할 수도 있습니다. 다음 예제에서는 previous_personal_access_token_id 열에서 서로 참조하는 개인 액세스 토큰의 체인을 쿼리합니다.

WITH RECURSIVE "personal_access_tokens_cte" AS (
(
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = 15)
  UNION (
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens",
      "personal_access_tokens_cte"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = "personal_access_tokens_cte"."id"))
SELECT
  "personal_access_tokens".*
FROM
  "personal_access_tokens_cte" AS "personal_access_tokens"
 
 id | previous_personal_access_token_id
----+-----------------------------------
 16 |                                15
 17 |                                16
 18 |                                17
 19 |                                18
 20 |                                19
 21 |                                20
(6 rows)

CTE는 임시 결과 집합이므로 다른 SELECT 문 내에서 사용할 수 있습니다. CTE를 UPDATE 또는 DELETE와 함께 사용하는 것은 예상치 못한 동작을 일으킬 수 있습니다:

다음 메서드를 고려해보세요:

def personal_access_token_chain(token)
  cte = Gitlab::SQL::RecursiveCTE.new(:personal_access_tokens_cte)
  personal_access_token_table = Arel::Table.new(:personal_access_tokens)
  
  cte << PersonalAccessToken
           .where(personal_access_token_table[:previous_personal_access_token_id].eq(token.id))
  cte << PersonalAccessToken
           .from([personal_access_token_table, cte.table])
           .where(personal_access_token_table[:previous_personal_access_token_id].eq(cte.table[:id]))
  PersonalAccessToken.with.recursive(cte.to_arel).from(cte.alias_to(personal_access_token_table))
end

이 쿼리가 사용되었을 때 예상대로 작동합니다:

> personal_access_token_chain(token)

WITH RECURSIVE "personal_access_tokens_cte" AS (
(
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = 11)
  UNION (
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens",
      "personal_access_tokens_cte"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = "personal_access_tokens_cte"."id"))
SELECT
    "personal_access_tokens".*
FROM
    "personal_access_tokens_cte" AS "personal_access_tokens"

그러나 #update_all과 함께 사용할 때 CTE는 제거됩니다. 결과적으로 메서드는 전체 테이블을 업데이트합니다:

> personal_access_token_chain(token).update_all(revoked: true)

UPDATE
    "personal_access_tokens"
SET
    "revoked" = TRUE

이 동작을 해결하기 위해 다음을 고려해보세요:

레코드의 ids를 쿼리하세요:

> token_ids = personal_access_token_chain(token).pluck_primary_key
=> [16, 17, 18, 19, 20, 21]

이 배열을 사용하여 PersonalAccessTokens를 범위로 지정하세요:
```
PersonalAccessToken.where(id: token_ids).update_all(revoked: true)
```

또는 이 두 단계를 결합하세요:

PersonalAccessToken
  .where(id: personal_access_token_chain(token).pluck_primary_key)
  .update_all(revoked: true)

무제한 데이터의 대량 업데이트를 피하세요. 데이터에 애플리케이션 제한이 없거나 데이터 양을 확신할 수 없는 경우, 데이터를 일괄로 업데이트하세요.

SQL 쿼리 지침

`LIKE` 문 사용

`LIKE` 및 인덱스

데이터베이스 열을 신뢰할 수 있게 참조하기

Good (prefer)

Bad (avoid)

ID 가져오기

ApplicationRecord에서 상속받기

UNION 사용하기

`UNION` 서브쿼리에서 열이 불균형한 경우

생성 날짜별로 정렬

`WHERE IN` 대신 `WHERE EXISTS` 사용

`.find_or_create_by`는 원자적이지 않음

`.safe_find_or_create_by`를 사용할 수 있을까요?

대안 1: `UPSERT`

대안 2: 존재 여부 확인하고 복구

프로덕션에서 SQL 쿼리 모니터링

공통 테이블 표현식 사용 시기

도움말

기능 사용 가능성과 제품 평가판

도움받기

SQL 쿼리 지침

LIKE 문 사용

LIKE 및 인덱스

데이터베이스 열을 신뢰할 수 있게 참조하기

Good (prefer)

Bad (avoid)

ID 가져오기

ApplicationRecord에서 상속받기

UNION 사용하기

UNION 서브쿼리에서 열이 불균형한 경우

생성 날짜별로 정렬

WHERE IN 대신 WHERE EXISTS 사용

.find_or_create_by는 원자적이지 않음

.safe_find_or_create_by를 사용할 수 있을까요?

대안 1: UPSERT

대안 2: 존재 여부 확인하고 복구

프로덕션에서 SQL 쿼리 모니터링

공통 테이블 표현식 사용 시기

도움말

기능 사용 가능성과 제품 평가판

도움받기

`LIKE` 문 사용

`LIKE` 및 인덱스

`UNION` 서브쿼리에서 열이 불균형한 경우

`WHERE IN` 대신 `WHERE EXISTS` 사용

`.find_or_create_by`는 원자적이지 않음

`.safe_find_or_create_by`를 사용할 수 있을까요?

대안 1: `UPSERT`