Postgres_fdw：Delete From Foreign Table (Partition Table) 導致異常刪除資料

在 Postgres 有 dblink 以及 foreign data wrapper 可以用來連接不同的資料庫，而 Postgres 彼此之間可以使用 postgres_fdw 來做外部表查詢。

在使用 postgres_fdw 的時候需要注意，SQL 語法是否有正常 Push Down. 下面舉例說明一下 SQL Push Down.

正常 Push Down 情況下 select 查詢做 where 條件判斷，會在 Foreign Server 進行查詢完成 where 條件篩選，而相對沒有 Push down 則代表著會將資料全部從 Remote 拉回來到本地才進行篩選，而這樣的情境會對資料庫造成很大的負擔，使的查詢速度非常緩慢，依據資料量和語法的不同，沒有正常 Push Down 的狀況跟正常 Push Down 的查詢時間可能會差上好幾十分鐘或是好幾小時。

使用 Postgres 外部表做查詢的時候，建議可以在來源的 Foreign Server，以及本地存取的 Local Server 分別執行查詢，看看兩者查詢速度有沒有差異，藉此來判斷是否有正常 Push Down ，雖然是 Postgres 對 Postgres ，但是在部分語法上如果使用到子查詢條件判斷等等情境還是處理的不是很完善，會有無法正常 Push Down 的狀況發生.

目前 Postgres 在 foreign table 上支援 INSERT、UPDATE、DELETE，並且再未來 PGSQL 14 預計新增 TRUNCATE 的支援。

下面舉一個沒有正常 Push Down 所導致資料異動有異常的狀況，此狀況主要會針對來源表格是 Partition table 的時候發生。

Partition table 在設計上使用者只要針對最上層的 table 下查詢，就會自動去對各個 Partition 的資料做查詢與異動，因此一般預設使用 Partition table 作為 Foreign table 的時候，只會將最上層的 Partitioned table 做匯入並且對其操作。

一般情況下針對此 Foreign Table(Partitioned table) 進行操作時都可得到正常結果，但是若沒有正確地進行 SQL Push Down 則可能造成資料損失或發生異常修改的狀況，下面藉由一測試情境來演示這狀況。

在來源端 (Foreign Server) 產生測試表

CREATE TABLE measurement (
    logdate         timestamp not null
) PARTITION BY RANGE (logdate);


CREATE TABLE measurement_y2020m02 PARTITION OF measurement
    FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');

CREATE TABLE measurement_y2020m03 PARTITION OF measurement
    FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');

CREATE TABLE measurement_y2020m04 PARTITION OF measurement
    FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');

CREATE TABLE measurement_y2020m05 PARTITION OF measurement
    FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');

insert into measurement 
select timestamp '2020-02-01 00:00:00' 
FROM generate_series(1, (random() * 500000)::integer);

insert into measurement 
select timestamp '2020-03-01 00:00:00' 
FROM generate_series(1, (random() * 500000)::integer);

insert into measurement 
select timestamp '2020-04-01 00:00:00' 
FROM generate_series(1, (random() * 500000)::integer);

insert into measurement 
select timestamp '2020-05-01 00:00:00' 
FROM generate_series(1, (random() * 500000)::integer);

sourcedb=# select logdate, count(1) from measurement group by logdate;
       logdate       | count
---------------------+--------
 2020-02-01 00:00:00 |  52419
 2020-03-01 00:00:00 | 145653
 2020-04-01 00:00:00 | 378380
 2020-05-01 00:00:00 | 340266

在存取 Foreign Server 的本地端建立 Foreign Table

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '127.0.0.1', port '5444', dbname 'sourcedb');

CREATE USER MAPPING FOR enterprisedb
        SERVER foreign_server
        OPTIONS (user 'enterprisedb', password 'edb');
        
CREATE FOREIGN TABLE measurement_fdw (
    logdate         timestamp not null
)
        SERVER foreign_server
        OPTIONS (schema_name 'public', table_name 'measurement');

建立本地表格用來進行子查詢判斷

create table testtime (day timestamp);
insert into testtime values ('2020-02-01 00:00:00' );

檢查資料筆數

target=# select logdate, count(1) from measurement_fdw group by logdate order by 1;
       logdate       | count
---------------------+--------
 2020-02-01 00:00:00 |  52419
 2020-03-01 00:00:00 | 145653
 2020-04-01 00:00:00 | 378380
 2020-05-01 00:00:00 | 340266
(4 rows)

target=# select distinct day from testtime ;
         day
---------------------
 2020-02-01 00:00:00
(1 row)

檢查執行計畫，此處預計刪除二月的資料，注意紅字部分為實際在Foreign Server 上執行的查詢語句。

target=# explain (verbose) delete from measurement_fdw where logdate in (select distinct day from testtime);
                                           QUERY PLAN
----------------------------------------------------------------------------------------------
 Delete on public.measurement_fdw  (cost=144.75..225.18 rows=2155 width=38)
   Remote SQL: DELETE FROM public.measurement WHERE ctid = $1
   ->  Hash Join  (cost=144.75..225.18 rows=2155 width=38)
         Output: measurement_fdw.ctid, "ANY_subquery".*
         Inner Unique: true，
         Hash Cond: (measurement_fdw.logdate = "ANY_subquery".day)
         ->  Foreign Scan on public.measurement_fdw  (cost=100.00..174.65 rows=2155 width=14)
               Output: measurement_fdw.ctid, measurement_fdw.logdate
               Remote SQL: SELECT logdate, ctid FROM public.measurement FOR UPDATE
         ->  Hash  (cost=42.25..42.25 rows=200 width=40)
               Output: "ANY_subquery".*, "ANY_subquery".day
               ->  Subquery Scan on "ANY_subquery"  (cost=38.25..42.25 rows=200 width=40)
                     Output: "ANY_subquery".*, "ANY_subquery".day
                     ->  HashAggregate  (cost=38.25..40.25 rows=200 width=8)
                           Output: testtime.day
                           Group Key: testtime.day
                           ->  Seq Scan on public.testtime  (cost=0.00..32.60 rows=2260 width=8)
                                 Output: testtime.day
(18 rows)

執行刪除，此處顯示刪除 52419 筆資料，但實際檢視資料會發現，除了原本預計刪除的二月資料，三四五月的資料也遭到刪除了，且都是 52419 筆

target=# delete from measurement_fdw where logdate in (select distinct day from testtime);
DELETE 52419

target=# select logdate, count(1) from measurement_fdw group by logdate order by 1;
       logdate       | count
---------------------+--------
 2020-03-01 00:00:00 |  93234            
 2020-04-01 00:00:00 | 325961               
 2020-05-01 00:00:00 | 287847             
(3 rows)

會造成這樣誤刪除資料的原因在於前面執行計畫中所觀察到的此段內容

Remote SQL: DELETE FROM public.measurement WHERE ctid = $1

由於此處使用子查詢在 SQL Push down 不夠漂亮，雖然子查詢所查到的只有少量資料，但執行計畫仍將資料從遠端拉回來本地做判斷，再回頭告訴遠端資料庫要刪除那些資料，這造成了這句 SQL 會執行很久，但這還不是造成誤刪除資料的原因，

由於本地端並不知道 measurement_fdw 這張表實際上是 Partition table ，而是作為一般 Table 進行認識執行刪除，而刪除在執行上會先找遇刪除資料的 ctid 編號，citd 代表著某筆資料在資料檔案中所在的 page 與 block 位置，每張表都是從 (0,0) 開始，而 Partitioned table 底下各個 partition 都是各自獨立計算。

原本在執行上，應該是找到對應要刪除的資料 ctid，在針對個別 Partition 進行刪除，但此處因為認識不足，直接從上層表格刪除，因此造成底下各個 Partition 有相同 ctid 的資料都被刪除了，本處只示範 Delete 的狀況，但經過確認 UPDATE 也會有相同問題在，造成資料被誤修改。

下面為不透過子查詢作為 where 判斷直接寫死的狀況，有進行 SQL Push Down 資料就不會有異常刪除修改的狀況

target=# explain (verbose) delete from measurement_fdw where logdate in ('2020-02-01');                                                               QUERY PLAN

----------------------------------------------------------------------------------------------
 Delete on public.measurement_fdw  (cost=100.00..8938.05 rows=1 width=6)
   ->  Foreign Delete on public.measurement_fdw  (cost=100.00..8938.05 rows=1 width=6)
         Remote SQL: DELETE FROM public.measurement WHERE ((logdate = '2020-02-01 00:00:00'::timestamp without time zone))
(3 rows)

下面有兩種解決方法。

第一種透過 Procedure 的方式將條件寫死。

CREATE OR REPLACE PROCEDURE delete_expired_measurements()
LANGUAGE plpgsql AS
$$
  DECLARE d timestamp;

  BEGIN
    FOR d IN SELECT DISTINCT day FROM testtime
    LOOP
      RAISE NOTICE 'Deleting date: %', d;
      DELETE FROM measurement_fdw WHERE logdate = d;
    END LOOP;
  END;
$$;

CALL delete_expired_measurements();

第二種在建立 Foreign Table 的時候使用 PARTITION OF 語句，此種方式可以避免資料遭到異常異動，但是如果有新增 Partition 會需要補建表格，也不完全解決 Push Down 的問題。

CREATE TABLE measurement (
    logdate         timestamp not null
) PARTITION BY RANGE (logdate);

CREATE FOREIGN TABLE measurement_y2020m02_fdw PARTITION OF measurement
    FOR VALUES FROM ('2020-02-01') TO ('2020-03-01') SERVER foreign_server
        OPTIONS (schema_name 'public', table_name 'measurement_y2020m02');

CREATE FOREIGN TABLE measurement_y2020m03_fdw PARTITION OF measurement
    FOR VALUES FROM ('2020-03-01') TO ('2020-04-01') SERVER foreign_server
        OPTIONS (schema_name 'public', table_name 'measurement_y2020m03');

CREATE FOREIGN TABLE measurement_y2020m04_fdw PARTITION OF measurement
    FOR VALUES FROM ('2020-04-01') TO ('2020-05-01') SERVER foreign_server
        OPTIONS (schema_name 'public', table_name 'measurement_y2020m04');

CREATE FOREIGN TABLE measurement_y2020m05_fdw PARTITION OF measurement
    FOR VALUES FROM ('2020-05-01') TO ('2020-06-01') SERVER foreign_server
        OPTIONS (schema_name 'public', table_name 'measurement_y2020m05');

透過上面一個範例，提醒在使用 Postgres_fdw 的時候一定要做相關的確認，特別是對 Partition Table 進行 DML 時務必做好相關確認來避免查詢緩慢甚至是誤異動資料的狀況發生。

Postgres 筆記 - Ken

2021年8月4日星期三