PostgreSQL Table Partitioning – Using Inheritance and Triggers

inheritancepartitioningpostgresqltrigger

I am trying to do table partitioning in PostgreSQL using inheritance and triggers but the problem that have occured to me is that the data does not move from the parent table to the children tables I have created! I wrote the following SQL code:

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();

Also, my parent table which is called title_basics has the following data types (SQL code below):

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)
);

My goal is to have the data from the parent table (title_basics) move over to the children table based on the trigger, this must be achieved through a trigger or a rule. My code was based on this documentation: https://www.postgresql.org/docs/12/ddl-partitioning.html

  • PostgreSQL Version: 11

Best Answer

The trigger will never move rows that are already in the table when you create the trigger, if that is what you mean.

Apart from that, your trigger works just fine.

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

No row was inserted, because the trigger function returned 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)

Sure, that returns a row, because of the table inheritance.

But the row is actually here:

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)

If you select only from the parent table itself, you get nothing:

SELECT * FROM ONLY title_basics;

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

Note: You should really use declarative partitioning instead on v11. It is so much simpler and better.

Related Question