Foreign Key to partitioned table – select * from depesz;

One of the long standing limitations of partitions is that you can't have foreign keys pointing to them.

Let's see if I can make it possible to have some kind of constraint that would do the same thing as fkey.

First, obviously, we need partitioned table with some data:

=$ CREATE TABLE USERS (
    id             serial not null,
    username       text not null,
    unique (username)
)
PARTITION BY RANGE ( username );
 
=$ CREATE TABLE users_a
    partition of users (primary key (id))
    for values from (minvalue) to ('b');
=$ CREATE TABLE users_b
    partition of users (primary key (id))
    for values from ('b') to ('c');
=$ CREATE TABLE users_c
    partition of users (primary key (id))
    for values from ('c') to ('d');
=$ CREATE TABLE users_d
    partition of users (primary key (id))
    for values from ('d') to ('e');
=$ CREATE TABLE users_e
    partition of users (primary key (id))
    for values from ('e') to ('f');
=$ CREATE TABLE users_f
    partition of users (primary key (id))
    for values from ('f') to ('g');
=$ CREATE TABLE users_g
    partition of users (primary key (id))
    for values from ('g') to ('h');
=$ CREATE TABLE users_h
    partition of users (primary key (id))
    for values from ('h') to ('i');
=$ CREATE TABLE users_i
    partition of users (primary key (id))
    for values from ('i') to ('j');
=$ CREATE TABLE users_j
    partition of users (primary key (id))
    for values from ('j') to ('k');
=$ CREATE TABLE users_k
    partition of users (primary key (id))
    for values from ('k') to ('l');
=$ CREATE TABLE users_l
    partition of users (primary key (id))
    for values from ('l') to ('m');
=$ CREATE TABLE users_m
    partition of users (primary key (id))
    for values from ('m') to ('n');
=$ CREATE TABLE users_n
    partition of users (primary key (id))
    for values from ('n') to ('o');
=$ CREATE TABLE users_o
    partition of users (primary key (id))
    for values from ('o') to ('p');
=$ CREATE TABLE users_p
    partition of users (primary key (id))
    for values from ('p') to ('q');
=$ CREATE TABLE users_q
    partition of users (primary key (id))
    for values from ('q') to ('r');
=$ CREATE TABLE users_r
    partition of users (primary key (id))
    for values from ('r') to ('s');
=$ CREATE TABLE users_s
    partition of users (primary key (id))
    for values from ('s') to ('t');
=$ CREATE TABLE users_t
    partition of users (primary key (id))
    for values from ('t') to ('u');
=$ CREATE TABLE users_u
    partition of users (primary key (id))
    for values from ('u') to ('v');
=$ CREATE TABLE users_v
    partition of users (primary key (id))
    for values from ('v') to ('w');
=$ CREATE TABLE users_w
    partition of users (primary key (id))
    for values from ('w') to ('x');
=$ CREATE TABLE users_x
    partition of users (primary key (id))
    for values from ('x') to ('y');
=$ CREATE TABLE users_y
    partition of users (primary key (id))
    for values from ('y') to ('z');
=$ CREATE TABLE users_z
    partition of users (primary key (id))
    for values from ('z') to (maxvalue);
 
=$ insert into users (username) values
    ('atom'), ('aught'), ('bides'), ('blob'), ('count'),
    ('flags'), ('fleck'), ('leggy'), ('otter'), ('plan'),
    ('rice'), ('riots'), ('semis'), ('serf'), ('sped'),
    ('stab'), ('tics'), ('vocal'), ('ward'), ('wrist');

I purposely picked partition key that is not based on primary.

Now – let's make a table that should have foreign key to users:

=$ CREATE TABLE accounts (
    id serial PRIMARY KEY,
    user_id int4 NOT NULL references users (id)
);

When I tried to run it, I got:

ERROR:  cannot reference partitioned table "users"

OK. So let's make it without fkey, and add our own constraint:

=$ CREATE TABLE accounts (
    id serial PRIMARY KEY,
    user_id int4 NOT NULL
);

In sources we can see that fkeys, use select like:

=$ SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...] FOR KEY SHARE OF x

The “FOR KEY SHARE" part is important, so we need it there too.

Let's consider how many cases we have for a trigger:

on insert to accounts we need to check if inserted user exists
on update to accounts, if user_id has changed, we have to check new user_id if it exists
on delete from users, we have to check if there are no rows in accounts with given user_id
on update in users, if id column has changed, we have to check if there are no rows in accounts with old value of id in user_id

None of these looks overly complicated, so let's write them.

To test the code, we will need to know what are the working, and not working, user_id values, so:

=$ select min(id), max(id), count(*) from users;
 min | max | count 
-----+-----+-------
   1 |  20 |    20
(1 row)

Great. So every user_id from range 1 to 20 (including) will be ok. Anything outside should be rejected.

First trigger:

=$ CREATE OR REPLACE FUNCTION fkey_fn_1() RETURNS trigger as $$
DECLARE
BEGIN
    perform 1 FROM users x WHERE id = NEW.user_id FOR KEY SHARE OF x;
    IF NOT FOUND THEN
        raise exception 'Constraint violation.'
            USING
                ERRCODE = 'foreign_key_violation',
                HINT = 'Value ' || NEW.user_id || ' does not exist in column id in table users.';
    END IF;
    RETURN NULL;
END;
$$ language plpgsql;
 
=$ CREATE CONSTRAINT TRIGGER fkey_fn_1
    AFTER INSERT ON accounts FOR EACH ROW EXECUTE PROCEDURE fkey_fn_1();

With this in place, I can run a simple test to make sure that it does work:

=$ INSERT INTO accounts (user_id) VALUES (20);
INSERT 0 1
 
=$ INSERT INTO accounts (user_id) VALUES (21);
ERROR:  Constraint violation.
HINT:  Value 21 does not exist in column id in table users.
CONTEXT:  PL/pgSQL function fkey_fn_1() line 6 at RAISE

Sweet. Correct value was approved, and incorrect one was rejected.

Second trigger will use identical function, but let's create separate one, for now:

=$ CREATE OR REPLACE FUNCTION fkey_fn_2() RETURNS trigger as $$
DECLARE
BEGIN
    perform 1 FROM users x WHERE id = NEW.user_id FOR KEY SHARE OF x;
    IF NOT FOUND THEN
        raise exception 'Constraint violation.'
            USING
                ERRCODE = 'foreign_key_violation',
                HINT = 'Value ' || NEW.user_id || ' does not exist in column id in table users.';
    END IF;
    RETURN NULL;
END;
$$ language plpgsql;
 
=$ CREATE CONSTRAINT TRIGGER fkey_fn_2
     AFTER UPDATE ON accounts
     FOR EACH ROW
     WHEN (OLD.user_id <> NEW.user_id)
     EXECUTE PROCEDURE fkey_fn_2();

Because of my prior calls, my data in accounts looks like:

=$ SELECT * FROM accounts;
┌────┬─────────┐
│ id │ user_id │
╞════╪═════════╡
│ 13 │      20 │
└────┴─────────┘

So, let's test the updates:

=$ UPDATE accounts SET user_id = 19 WHERE user_id = 20;
UPDATE 1
 
=$ UPDATE accounts SET user_id = 21 WHERE user_id = 19;
ERROR:  Constraint violation.
HINT:  Value 21 does not exist in column id in table users.
CONTEXT:  PL/pgSQL function fkey_fn_2() line 6 at RAISE

Sweet. Now, let's move on checking users:

=$ CREATE OR REPLACE FUNCTION fkey_fn_3() RETURNS trigger as $$
DECLARE
BEGIN
    perform 1 FROM accounts x WHERE x.user_id = OLD.id FOR KEY SHARE OF x LIMIT 1;
    IF FOUND THEN
        raise exception 'Constraint violation.'
            USING
                ERRCODE = 'foreign_key_violation',
                HINT = 'Value ' || OLD.id || ' still exists in column user_id in table accounts.';
    END IF;
    RETURN NULL;
END;
$$ language plpgsql;
CREATE FUNCTION
 
=$ CREATE CONSTRAINT TRIGGER fkey_fn_3 AFTER DELETE ON users FOR EACH ROW EXECUTE PROCEDURE fkey_fn_3();
CREATE TRIGGER

OK. First question is: did it get propagated to partitions?

=$ \d users
                             Table "public.users"
  Column  |  Type   | Collation | Nullable |              Default              
----------+---------+-----------+----------+-----------------------------------
 id       | integer |           | not null | nextval('users_id_seq'::regclass)
 username | text    |           | not null | 
Partition key: RANGE (username)
Indexes:
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
Triggers:
    fkey_fn_3 AFTER DELETE ON users NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE fkey_fn_3()
Number of partitions: 26 (Use \d+ to list them.)
 
=$ \d users_a
                            Table "public.users_a"
  Column  |  Type   | Collation | Nullable |              Default              
----------+---------+-----------+----------+-----------------------------------
 id       | integer |           | not null | nextval('users_id_seq'::regclass)
 username | text    |           | not null | 
Partition of: users FOR VALUES FROM (MINVALUE) TO ('b')
Indexes:
    "users_a_pkey" PRIMARY KEY, btree (id)
    "users_a_username_key" UNIQUE CONSTRAINT, btree (username)
Triggers:
    fkey_fn_3 AFTER DELETE ON users_a NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE fkey_fn_3()
 
=$ \d users_z
                            Table "public.users_z"
  Column  |  Type   | Collation | Nullable |              Default              
----------+---------+-----------+----------+-----------------------------------
 id       | integer |           | not null | nextval('users_id_seq'::regclass)
 username | text    |           | not null | 
Partition of: users FOR VALUES FROM ('z') TO (MAXVALUE)
Indexes:
    "users_z_pkey" PRIMARY KEY, btree (id)
    "users_z_username_key" UNIQUE CONSTRAINT, btree (username)
Triggers:
    fkey_fn_3 AFTER DELETE ON users_z NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE fkey_fn_3()

Looks like it did, so let quickly recheck what account we have:

=$ SELECT * FROM accounts;
 id | user_id 
----+---------
 13 |      19
(1 row)

And now test the deletes:

=$ DELETE FROM users WHERE id = 1;
DELETE 1
 
=$ DELETE FROM users WHERE id = 19;
ERROR:  Constraint violation.
HINT:  Value 19 still exists in column user_id in table accounts.
CONTEXT:  PL/pgSQL function fkey_fn_3() line 6 at RAISE

Sweet, worked and failed as expected, and as needed.

So, the last trigger is the one on users update …

=$ CREATE OR REPLACE FUNCTION fkey_fn_4() RETURNS trigger as $$
DECLARE
BEGIN
    perform 1 FROM accounts x WHERE x.user_id = OLD.id FOR KEY SHARE OF x LIMIT 1;
    IF FOUND THEN
        raise exception 'Constraint violation.'
            USING
                ERRCODE = 'foreign_key_violation',
                HINT = 'Value ' || OLD.id || ' still exists in column user_id in table accounts.';
    END IF;
    RETURN NULL;
END;
$$ language plpgsql;
CREATE FUNCTION
 
=$ CREATE CONSTRAINT TRIGGER fkey_fn_4 AFTER UPDATE ON users FOR EACH ROW WHEN (OLD.id <> NEW.id) EXECUTE PROCEDURE fkey_fn_4();
CREATE TRIGGER

We know that we have account for user 19, so first, let's try update that should work:

=$ update users set id = 1 where id = 2;
UPDATE 1

No surprises here. And how about one that shouldn't work?

=$ update users set id = 2 where id = 19;
ERROR:  Constraint violation.
HINT:  Value 19 still exists in column user_id in table accounts.
CONTEXT:  PL/pgSQL function fkey_fn_4() line 6 at RAISE

Great. Looks like I got it to work. But it's not done yet – I should test performance, and perhaps make it in such a way that one could use the same functions, with no modifications, regardless of source and destination table and column names.

This will have to wait a bit, though …

One thought on “Foreign Key to partitioned table”

Matt says:

2019-05-16 at 13:21

I just discovered your blog while researching the partition fk limitations in PostgreSQL 10. Your solution here is great, well thought out, and explained clearly. Thank you! I also found parts 2 and 3 very insightful. I can’t wait for PG12 to be released so that it’s supported natively, but this will be immensely helpful right now. Thanks again!

Comments are closed.