Foreign Key to partitioned table – part 3

Previously I tested performance of pl/PgSQL coded foreign keys to partitioned table.

Now, let's see if I can make creation of them a bit easier.

Using the same schema as before, I see that adding actual fkeys is pretty complicated. I need to create two separate functions, and four triggers, remembering what goes where. This is not really good idea.

I'll try to make a function that will create all necessary things for me.

First things first, I need to know what arguments will that function require. This is rather simple:

  • referencing table
  • referencing column
  • referenced table
  • referenced column

I need the primary key column, as I can't easily get this data out of system catalogs (main table doesn't have primary key, just partitions).

First thing that we need to decide is how to name the functions, as they shouldn't collide if, in single database, there would be many fkeys to partitioned tables.

I figured that I can make the names use oids of tables, and “numbers" for attributes (numbers are positive integers).

This leads to code like this:

CREATE OR REPLACE FUNCTION create_fkey(
    IN p_referencing_table  regclass,
    IN p_referencing_column TEXT,
    IN p_referenced_table   regclass,
    IN p_referenced_column  TEXT
) RETURNS void AS $$
DECLARE
    v_referencing_column_num INT4;
    v_referenced_column_num INT4;
    v_trigger_function_name TEXT;
BEGIN
    SELECT attnum INTO v_referencing_column_num FROM pg_attribute WHERE attrelid = p_referencing_table AND attname = p_referencing_column;
    IF NOT FOUND THEN
        raise exception 'Given table/column doesn''t exist: %/%', p_referencing_table, p_referencing_column;
    END IF;
    SELECT attnum INTO v_referenced_column_num FROM pg_attribute WHERE attrelid = p_referenced_table AND attname = p_referenced_column;
    IF NOT FOUND THEN
        raise exception 'Given table/column doesn''t exist: %/%', p_referenced_table, p_referenced_column;
    END IF;
    v_trigger_function_name := format('trg_fn_%s_%s_%s_%s', p_referencing_table::INT4, v_referencing_column_num, p_referenced_table::INT4, v_referenced_column_num );
    raise notice '[[%]]', v_trigger_function_name;
END;
$$ LANGUAGE plpgsql;

which generates:

=$ SELECT create_fkey( 'accounts', 'user_id', 'users', 'id' );
NOTICE:  [[trg_fn_22965_2_22671_1]]

There will be two functions, but I'll just add _1 and _2 to them.

Actual body of the function is:

CREATE OR REPLACE FUNCTION create_fkey(
    IN p_referencing_table  regclass,
    IN p_referencing_column TEXT,
    IN p_referenced_table   regclass,
    IN p_referenced_column  TEXT
) RETURNS void AS $$
DECLARE
    v_referencing_column_num INT4;
    v_referenced_column_num INT4;
    v_trigger_function_name TEXT;
    v_fun_1_body TEXT;
    v_fun_2_body TEXT;
BEGIN
    SELECT attnum INTO v_referencing_column_num FROM pg_attribute WHERE attrelid = p_referencing_table AND attname = p_referencing_column;
    IF NOT FOUND THEN
        raise exception 'Given table/column doesn''t exist: %/%', p_referencing_table, p_referencing_column;
    END IF;
    SELECT attnum INTO v_referenced_column_num FROM pg_attribute WHERE attrelid = p_referenced_table AND attname = p_referenced_column;
    IF NOT FOUND THEN
        raise exception 'Given table/column doesn''t exist: %/%', p_referenced_table, p_referenced_column;
    END IF;
 
    v_trigger_function_name := format('trg_fn_%s_%s_%s_%s', p_referencing_table::INT4, v_referencing_column_num, p_referenced_table::INT4, v_referenced_column_num );
 
    EXECUTE format(
        $SQL$
        CREATE OR REPLACE FUNCTION %I() RETURNS TRIGGER AS $f$
        DECLARE
        BEGIN
            perform 1 FROM %I x WHERE %I = NEW.%I FOR KEY SHARE OF x;
            IF NOT FOUND THEN
                raise exception 'Constraint violation.'
                    USING
                        ERRCODE = 'foreign_key_violation',
                        HINT = 'Value ' || NEW.%I || ' does not exist in column %I in table %I.';
            END IF;
            RETURN NULL;
        END;
        $f$ LANGUAGE plpgsql;
        $SQL$,
        v_trigger_function_name || '_1',
        p_referenced_table,
        p_referenced_column,
        p_referencing_column,
        p_referencing_column,
        p_referenced_column,
        p_referenced_table
    );
 
    EXECUTE format(
        $SQL$
        CREATE OR REPLACE FUNCTION %I() RETURNS TRIGGER AS $f$
        DECLARE
        BEGIN
            perform 1 FROM %I x WHERE x.%I = OLD.%I FOR KEY SHARE OF x LIMIT 1;
            IF FOUND THEN
                raise exception 'Constraint violation.'
                    USING
                        ERRCODE = 'foreign_key_violation',
                        HINT = 'Value ' || OLD.%I || ' still exists in column %I in table %I.';
            END IF;
            RETURN NULL;
        END;
        $f$ LANGUAGE plpgsql;
        $SQL$,
        v_trigger_function_name || '_2',
        p_referencing_table,
        p_referencing_column,
        p_referenced_column,
        p_referenced_column,
        p_referencing_column,
        p_referencing_table
    );
 
    EXECUTE format(
        $SQL$
        CREATE CONSTRAINT TRIGGER %I AFTER INSERT ON %I FOR EACH ROW EXECUTE PROCEDURE %I();
        $SQL$,
        v_trigger_function_name || '_1',
        p_referencing_table,
        v_trigger_function_name || '_1'
    );
    EXECUTE format(
        $SQL$
        CREATE CONSTRAINT TRIGGER %I AFTER UPDATE ON %I FOR EACH ROW WHEN (OLD.%I <> NEW.%I) EXECUTE PROCEDURE %I();
        $SQL$,
        v_trigger_function_name || '_2',
        p_referencing_table,
        p_referencing_column,
        p_referencing_column,
        v_trigger_function_name || '_1'
    );
    EXECUTE format(
        $SQL$
        CREATE CONSTRAINT TRIGGER %I AFTER DELETE ON %I FOR EACH ROW EXECUTE PROCEDURE %I();
        $SQL$,
        v_trigger_function_name || '_3',
        p_referenced_table,
        v_trigger_function_name || '_2'
    );
    EXECUTE format(
        $SQL$
        CREATE CONSTRAINT TRIGGER %I AFTER UPDATE ON %I FOR EACH ROW WHEN (OLD.%I <> NEW.%I) EXECUTE PROCEDURE %I();
        $SQL$,
        v_trigger_function_name || '_4',
        p_referenced_table,
        p_referenced_column,
        p_referenced_column,
        v_trigger_function_name || '_2'
    );
END;
$$ LANGUAGE plpgsql;

Long and complicated. But thanks to this, I can now:

=$ SELECT create_fkey( 'accounts', 'user_id', 'users', 'id' );
 create_fkey 
-------------
 
(1 ROW)

And the fkey things are in place:

=$ \d accounts
                             TABLE "public.accounts"
 COLUMN  |  TYPE   | Collation | NULLABLE |               DEFAULT                
---------+---------+-----------+----------+--------------------------------------
 id      | INTEGER |           | NOT NULL | NEXTVAL('accounts_id_seq'::regclass)
 user_id | INTEGER |           | NOT NULL | 
 balance | INTEGER |           | NOT NULL | 0
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (id)
Triggers:
    trg_fn_22965_2_22671_1_1 AFTER INSERT ON accounts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE trg_fn_22965_2_22671_1_1()
    trg_fn_22965_2_22671_1_2 AFTER UPDATE ON accounts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW WHEN (OLD.user_id <> NEW.user_id) EXECUTE PROCEDURE trg_fn_22965_2_22671_1_1()
 
=$ \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:
    trg_fn_22965_2_22671_1_3 AFTER DELETE ON users NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE trg_fn_22965_2_22671_1_2()
    trg_fn_22965_2_22671_1_4 AFTER UPDATE ON users NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW WHEN (OLD.id <> NEW.id) EXECUTE PROCEDURE trg_fn_22965_2_22671_1_2()
NUMBER OF partitions: 26 (USE \d+ TO list them.)

And thanks to PostgreSQL magic, it got propagated to all partitions, for example:

=$ \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:
    trg_fn_22965_2_22671_1_3 AFTER DELETE ON users_z NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE trg_fn_22965_2_22671_1_2()
    trg_fn_22965_2_22671_1_4 AFTER UPDATE ON users_z NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW WHEN (OLD.id <> NEW.id) EXECUTE PROCEDURE trg_fn_22965_2_22671_1_2()

Hope you'll find it either useful, or at least interesting 🙂