0

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
0

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.

  • Thank you for your answer, but is it possible to move the data from the parent table to the new tables? – Siegrain Jan 14 at 21:17
  • No, you'd have to do that manually. By the way, don't use inheritance partitioning any more. – Laurenz Albe Jan 14 at 21:18
  • Okay, this is an exercise and won't be used in realtime but if you suggest not to use inheritance for partitioning, is there another method to do it? – Siegrain Jan 14 at 21:21
  • I added a link at the end of my answer. – Laurenz Albe Jan 14 at 21:26
  • Thank you very much! I will mark this as solved! – Siegrain Jan 14 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.