PostgreSQL 안티패턴: 트리거를 우회하여 데이터 변경

조만간 많은 사람들이 테이블 레코드에서 무언가를 대규모로 수정해야 할 필요성에 직면하게 됩니다. 나는 이미 어떻게 하면 더 잘할 수 있는지 말해줘, 그리고 어떻게-하지 않는 것이 좋습니다. 오늘은 대량 업데이트의 두 번째 측면에 대해 이야기하겠습니다. 트리거 정보.

예를 들어, 무언가를 수정해야 하는 테이블에서 악의적인 트리거가 중단됩니다. ON UPDATE, 모든 변경 사항을 일부 집계로 전송합니다. 그리고 이러한 집계가 영향을 받지 않도록 모든 것을 신중하게 업데이트해야 합니다(예: 새 필드 초기화).

트리거를 비활성화합시다!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

사실 그게 다야 - 모든 것이 걸려있다.

때문에 ALTER TABLE 부과하다 액세스 독점- 단순한 것일지라도 아무도 병렬로 실행하지 않는 잠금 SELECT, 테이블에서 아무 것도 읽을 수 없습니다. 즉, 이 트랜잭션이 끝날 때까지 "그냥 읽기"라도 원하는 모든 사람이 기다릴 것입니다. 그리고 우리는 그것을 기억합니다 UPDATE 우리는 긴 ...

빨리 껐다가 빨리 켜자!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
COMMIT;

UPDATE ...;

BEGIN;
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

여기서 상황은 이미 더 좋고 대기 시간은 훨씬 적습니다. 그러나 두 가지 문제만이 모든 아름다움을 망칩니다.

• ALTER TABLE 긴 작업을 포함하여 테이블의 다른 모든 작업을 기다립니다. SELECT
• 트리거가 꺼져 있는 동안 모든 변경 사항을 "비행" 테이블에, 심지어 우리 것이 아닙니다. 집계에 포함되어야 하지만 집계에 포함되지는 않습니다. 문제!

세션 변수 관리

따라서 이전 버전에서 우리는 근본적인 점을 우연히 발견했습니다. 즉, 테이블의 "우리" 변경 사항과 "우리 변경 사항이 아닌" 변경 사항을 구별하기 위해 어떻게든 트리거를 가르쳐야 합니다. "Ours"는 그대로 건너뛰지만 "not ours"에서는 트리거됩니다. 이를 위해 다음을 사용할 수 있습니다. 세션 변수.

세션_복제_역할

우리는 읽었다. 수동:

트리거 메커니즘은 구성 변수의 영향도 받습니다. 세션_복제_역할. 추가 지침 없이 활성화(기본값), 복제 역할이 "원본"(기본값) 또는 "로컬"일 때 트리거가 실행됩니다. 지정하여 활성화된 트리거 ENABLE REPLICA, 다음 경우에만 작동합니다. 현재 세션 모드 - "복제본" 및 지정하여 활성화된 트리거 ENABLE ALWAYS, 현재 복제 모드에 관계없이 작동합니다.

설정이 한 번에 모두 적용되지 않는다는 점을 특히 강조하겠습니다. ALTER TABLE, 그러나 우리의 별도의 특수 연결에만 해당됩니다. 전체적으로 애플리케이션 트리거가 작동하지 않도록 다음을 수행합니다.

SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние

트리거 내부 조건

그러나 위의 옵션은 한 번에 모든 트리거에 대해 작동합니다(또는 비활성화하지 않으려는 트리거를 미리 "교체"해야 함). 그리고 우리가 필요하다면 하나의 특정 트리거를 "끄기"?

이것은 우리에게 도움이 될 것입니다 "사용자" 세션 변수:

확장 매개변수 이름은 다음과 같이 작성됩니다. SQL의 전체 개체 이름과 유사하게 확장 이름 뒤에 점과 매개변수 이름이 옵니다. 예: plpgsql.variable_conflict.
적절한 확장 모듈을 로드하지 않는 프로세스에서 시스템 외부 옵션을 설정할 수 있으므로 PostgreSQL은 두 가지 구성 요소가 있는 모든 이름의 값.

먼저 다음과 같이 트리거를 마무리합니다.

BEGIN
    -- процессу конвертации можно делать все
    IF current_setting('mycfg.my_table_convert_process') = 'TRUE' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            RETURN NEW;
        ELSE
            RETURN OLD;
        END IF;
    END IF;
...

그건 그렇고, 이것은 차단하지 않고 "이익을 위해" 수행할 수 있습니다. CREATE OR REPLACE 트리거 기능을 위해. 그런 다음 특수 연결에서 "우리" 변수를 콕킹합니다.

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- вернули в исходное состояние

다른 방법을 알고 있습니까? 의견에 공유하십시오.

출처 : habr.com