使用繼承和触發器進行表分區

我正在嘗試使用繼承和触發器在 PostgreSQL 中進行表分區，但我遇到的問題是數據不會從父表移動到我創建的子表！我編寫了以下 SQL 程式碼：

DROP TABLE IF EXISTS title_basics_1874to1899;
CREATE TABLE title_basics_1874to1899 (
   CHECK (
       CAST(startyear AS INT) >= 1874
       AND
       CAST(startyear AS INT) <= 1899
   )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_1900to1949;
CREATE TABLE title_basics_1900to1949 (
   CHECK (
       CAST(startyear AS INT) >= 1900
       AND
       CAST(startyear AS INT) <= 1949
   )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_1950to1999;
CREATE TABLE title_basics_1950to1999 (
   CHECK (
       CAST(startyear AS INT) >= 1950
       AND
       CAST(startyear AS INT) <= 1999
   )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_2000to2115;
CREATE TABLE title_basics_2000to2115 (
   CHECK (
       CAST(startyear AS INT) >= 2000
       AND
       CAST(startyear AS INT) <= 2115
   )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_null;
CREATE TABLE title_basics_null (
   CHECK (
       startyear IS NULL
   )
) INHERITS (title_basics);

-- INDEXES
DROP INDEX IF EXISTS title_basics_1874to1899_index;
CREATE INDEX title_basics_1874to1899_index ON
title_basics_1874to1899 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_1900to1949_index;
CREATE INDEX title_basics_1900to1949_index ON
title_basics_1900to1949 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_1950to1999_index;
CREATE INDEX title_basics_1950to1999_index ON
title_basics_1950to1999 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_2000to2115_index;
CREATE INDEX title_basics_2000to2115_index ON
title_basics_2000to2115 USING BTREE(startyear);

-- TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION title_basics_partitioner()
RETURNS TRIGGER AS $$
   BEGIN
       IF ( CAST(NEW.startyear AS INT) >= 1874 AND CAST(NEW.startyear AS INT) <= 1899 ) THEN
           INSERT INTO title_basics_1874to1899 VALUES (NEW.*);
       ELSIF ( CAST(NEW.startyear AS INT) >= 1900 AND CAST(NEW.startyear AS INT) <= 1949 ) THEN
           INSERT INTO title_basics_1900to1949 VALUES (NEW.*);
       ELSIF ( CAST(NEW.startyear AS INT) >= 1950 AND CAST(NEW.startyear AS INT) <= 1999 ) THEN
           INSERT INTO title_basics_1950to1999 VALUES (NEW.*);
       ELSIF ( CAST(NEW.startyear AS INT) >= 2000 AND CAST(NEW.startyear AS INT) <= 2115 ) THEN
           INSERT INTO title_basics_2000to2115 VALUES (NEW.*);
       ELSIF (NEW.startyear IS NULL) THEN
           INSERT INTO title_basics_null VALUES (NEW.*);
       ELSE
           RAISE EXCEPTION 'Out of range year value.';
       END IF;
       RETURN NULL;
   END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS insert_title_basics_trigger on title_basics;
CREATE TRIGGER insert_title_basics_trigger
BEFORE INSERT ON title_basics
FOR EACH ROW EXECUTE PROCEDURE title_basics_partitioner();

此外，我的名為 title_basics 的父表具有以下數據類型（下面的 SQL 程式碼）：

CREATE TABLE title_basics (
   t_soundex VARCHAR(7),
   startYear INTEGER,
   isAdult VARCHAR(1),
   genres VARCHAR(150),
   endYear INTEGER,
   primaryTitle VARCHAR(500),
   tconst BIGSERIAL PRIMARY KEY NOT NULL,
   runtimeMinutes VARCHAR(10),
   titleType VARCHAR(15),
   originalTitle VARCHAR(500)
);

我的目標是讓父表（title_basics）中的數據基於觸發器移動到子表，這必須通過觸發器或規則來實現。我的程式碼基於此文件：https ://www.postgresql.org/docs/12/ddl-partitioning.html

• PostgreSQL 版本：11

當您創建觸發器時，觸發器將永遠不會移動表中已經存在的行，如果這就是您的意思的話。

除此之外，您的觸發器工作正常。

INSERT INTO title_basics VALUES ('dsadas', 2020, 'T', 'dsadsa', 2021, 'tit', 42, 'min', 'type', 'orig');
INSERT 0 0

沒有插入任何行，因為觸發器函式返回 NULL。

SELECT * FROM title_basics;

t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle 
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
dsadas    |      2020 | T       | dsadsa |    2021 | tit          |     42 | min            | type      | orig
(1 row)

當然，由於表繼承，這會返回一行。

但該行實際上是在這裡：

SELECT * FROM title_basics_2000to2115 ;

t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle 
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
dsadas    |      2020 | T       | dsadsa |    2021 | tit          |     42 | min            | type      | orig
(1 row)

如果您僅從父表本身中選擇，您將一無所獲：

SELECT * FROM ONLY title_basics;

t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle 
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
(0 rows)

注意：您應該在 v11 上真正使用聲明性分區。它是如此簡單和更好。

引用自：https://dba.stackexchange.com/questions/257411