There are excellent references on partitioning tables that depend on one table, but what happens when you need to partition the referenced table?
Let's find out!
Friday, February 19. 2010
Part(ition)ing Is Such Sweet Sorrow
First, we'll create that table:
Next, a few partitions for it:
Now as usual, let's make sure that things go into their own places.
So far, so good.
Now, another table that's supposed to point to it:
And some partitions. Note how each refers to the corresponding
partition of p. We're getting a little bit tricky here, so examine
these carefully.
And marshal INSERTs on the parent table as usual:
That's fine for INSERTs, but what happens if an UPDATE would move the
row from one partition to another? Answer: triggers.
Then we do the usual thing, creating triggers on each of the
partitions.
Let's try this with some actual data:
Let's have a look at how those line up:
Good so far. Let's see if we can move those down a bit:
Voilà!
CREATE TABLE p (
id SERIAL PRIMARY KEY INITIALLY DEFERRED,
t TEXT
);
Next, a few partitions for it:
CREATE TABLE p0 (
CHECK (id % 5 = 0),
PRIMARY KEY(id)
)
INHERITS (p);
CREATE TABLE p1 (
CHECK (id % 5 = 1),
PRIMARY KEY(id)
)
INHERITS (p);
CREATE TABLE p2 (
CHECK (id % 5 = 2),
PRIMARY KEY(id)
)
INHERITS (p);
CREATE TABLE p3 (
CHECK (id % 5 = 3),
PRIMARY KEY(id)
)
INHERITS (p);
CREATE TABLE p4 (
CHECK (id % 5 = 4),
PRIMARY KEY(id)
)
INHERITS (p);
Now as usual, let's make sure that things go into their own places.
CREATE FUNCTION marshal_p()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.id % 5 = 0
THEN INSERT INTO p0 (id, t) VALUES (NEW.id, NEW.t);
ELSIF NEW.id % 5 = 1
THEN INSERT INTO p1 (id, t) VALUES (NEW.id, NEW.t);
ELSIF NEW.id % 5 = 2
THEN INSERT INTO p2 (id, t) VALUES (NEW.id, NEW.t);
ELSIF NEW.id % 5 = 3
THEN INSERT INTO p3 (id, t) VALUES (NEW.id, NEW.t);
ELSIF NEW.id % 5 = 4
THEN INSERT INTO p4 (id, t) VALUES (NEW.id, NEW.t);
END IF;
RETURN NULL;
END;
$$;
CREATE TRIGGER insert_p_trg
BEFORE INSERT ON p
FOR EACH ROW
EXECUTE PROCEDURE marshal_p();
So far, so good.
Now, another table that's supposed to point to it:
CREATE TABLE f (
p_id INTEGER NOT NULL,
f_t TEXT
);
And some partitions. Note how each refers to the corresponding
partition of p. We're getting a little bit tricky here, so examine
these carefully.
CREATE TABLE f0 (
FOREIGN KEY(p_id) REFERENCES p0 INITIALLY DEFERRED
)
INHERITS(f);
CREATE TABLE f1 (
FOREIGN KEY(p_id) REFERENCES p1 INITIALLY DEFERRED
)
INHERITS(f);
CREATE TABLE f2 (
FOREIGN KEY(p_id) REFERENCES p2 INITIALLY DEFERRED
)
INHERITS(f);
CREATE TABLE f3 (
FOREIGN KEY(p_id) REFERENCES p3 INITIALLY DEFERRED
)
INHERITS(f);
CREATE TABLE f4 (
FOREIGN KEY(p_id) REFERENCES p4 INITIALLY DEFERRED
)
INHERITS(f);
And marshal INSERTs on the parent table as usual:
CREATE FUNCTION marshal_f()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
d_bug BOOLEAN := true;
BEGIN
IF (d_bug) THEN
RAISE WARNING 'FIRING %', TG_NAME;
END IF;
IF NEW.p_id % 5 = 0
THEN INSERT INTO f0 (p_id, f_t) VALUES (NEW.p_id, NEW.f_t);
ELSIF NEW.p_id % 5 = 1
THEN INSERT INTO f1 (p_id, f_t) VALUES (NEW.p_id, NEW.f_t);
ELSIF NEW.p_id % 5 = 2
THEN INSERT INTO f2 (p_id, f_t) VALUES (NEW.p_id, NEW.f_t);
ELSIF NEW.p_id % 5 = 3
THEN INSERT INTO f3 (p_id, f_t) VALUES (NEW.p_id, NEW.f_t);
ELSIF NEW.p_id % 5 = 4
THEN INSERT INTO f4 (p_id, f_t) VALUES (NEW.p_id, NEW.f_t);
END IF;
RETURN NULL;
END;
$$;
CREATE TRIGGER insert_f_trg
BEFORE INSERT ON f
FOR EACH ROW
EXECUTE PROCEDURE marshal_f();
That's fine for INSERTs, but what happens if an UPDATE would move the
row from one partition to another? Answer: triggers.
CREATE FUNCTION late_check_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
t_index INTEGER := regexp_replace(TG_TABLE_NAME, '[^[:digit:]]', '', 'g');
d_bug BOOLEAN := true; /* We'll turn this off later */
BEGIN
IF d_bug THEN
RAISE WARNING 'FIRING %', TG_NAME;
END IF;
IF NEW.p_id % 5 <> t_index THEN
IF TG_OP = 'INSERT' THEN
RAISE EXCEPTION
'Try inserting into f% instead of f%, or just insert into f and let the magic happen.',
NEW.p_id, t_index;
ELSE /* TG_OP = 'UPDATE' */
EXECUTE $q$
DELETE FROM f
WHERE (p_id, f_t) IS NOT DISTINCT FROM ($1, $2);
$q$
USING OLD.p_id, OLD.f_t;
EXECUTE $q$
INSERT INTO f (p_id, f_t)
VALUES ($1, $2);
$q$
USING NEW.p_id, NEW.f_t;
END IF;
END IF;
RETURN NEW;
END;
$$;
Then we do the usual thing, creating triggers on each of the
partitions.
CREATE TRIGGER late_check_f0_trg
BEFORE INSERT OR UPDATE ON f0
FOR EACH ROW
EXECUTE PROCEDURE late_check_fn();
CREATE TRIGGER late_check_f1_trg
BEFORE INSERT OR UPDATE ON f1
FOR EACH ROW
EXECUTE PROCEDURE late_check_fn();
CREATE TRIGGER late_check_f2_trg
BEFORE INSERT OR UPDATE ON f2
FOR EACH ROW
EXECUTE PROCEDURE late_check_fn();
CREATE TRIGGER late_check_f3_trg
BEFORE INSERT OR UPDATE ON f3
FOR EACH ROW
EXECUTE PROCEDURE late_check_fn();
CREATE TRIGGER late_check_f4_trg
BEFORE INSERT OR UPDATE ON f4
FOR EACH ROW
EXECUTE PROCEDURE late_check_fn();
Let's try this with some actual data:
INSERT INTO p(t)
VALUES ('I'), ('love'), ('the'), ('smell'), ('of'), ('coffee');
INSERT INTO f
VALUES (1,'in'),(2,'the'),(3,'morning');
Let's have a look at how those line up:
# SELECT * FROM p LEFT JOIN f ON (p.id = f.p_id);
id | t | p_id | f_t
----+--------+------+---------
1 | I | 1 | in
2 | love | 2 | the
3 | the | 3 | morning
4 | smell | |
5 | of | |
6 | coffee | |
(6 rows)
Good so far. Let's see if we can move those down a bit:
UPDATE f SET p_id=p_id+3;
Voilà!
# SELECT * FROM p LEFT JOIN f ON (p.id = f.p_id);
id | t | p_id | f_t
----+--------+------+---------
1 | I | |
2 | love | |
3 | the | |
4 | smell | 4 | in
5 | of | 5 | the
6 | coffee | 6 | morning
(6 rows)
Do you know a workaround?
Thanks for the heads-up. I think I've fixed this here: http://people.planetpostgresql.org/dfetter/index.php?/archives/59-Partitioning-Glances.html