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 🙂
Vey interesting! Thank you for the great methods.
I have a use case in postgres 16, parent table has pk with 4 columns (the id and 3 partitioned columns). Although it is possible to carry these 4 columns to the child table and create a traditional fk, initially I thought this was a bit messy with the redundancy, and I opted to use the approach you have outlined with functions and triggers to manually maintain integrity with a “logical” fk.
However, I ran into a problem when updating one of the partitioned columns. The delete trigger fired on the parent table! This prevented the update because rows existed in the child table.
I learned that Postgres, when updating a partition column, actually does a delete from the original partition table, and an insert into the new partition table.
If possible, I still prefer to manually enforce the pk instead of carrying the 3 columns from the main table to the child tables to allow a traditional fk.