On Mon, Apr 22, 2024 at 12:02 AM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
I suggest you share a script that demonstrates exactly what you are trying to accomplish. Which event triggers you need to create from the application and what the functions those triggers call do.
We are using pg_partman for automatic partition maintenance however as we have foreign keys created on the tables, so the partition drop from parent is taking longer as it scans all the partitions of the child table and also locks the full child table for that duration(even SELECT query not allowed during that period). So we are thinking of creating foreign keys on partitions rather than on tables however there is no direct option for that to happen through pg_partman.
So we are thinking of first creating the table without any foreign keys and creating the partitions using pg_partman, then create the below event trigger which will add the foreign key to the new partitions for all new future partitions. And we are planning to create such an event trigger for all such child tables that are partitioned and having FK's.
CREATE OR REPLACE FUNCTION add_partition_foreign_key()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
partition_table TEXT;
parent_table TEXT;
partition_name TEXT;
BEGIN
IF TG_TAG = 'CREATE TABLE' THEN
partition_table := TG_TABLE_NAME;
parent_table := 'parent_table_' || to_char(NEW.partition_key, 'YYYY_MM_DD');
EXECUTE format('ALTER TABLE %I ADD CONSTRAINT fk_%I_%I FOREIGN KEY (partition_key, id) REFERENCES %I (partition_key, id)', partition_table, partition_table, parent_table, parent_table);
END IF;
END;
$$;
CREATE EVENT TRIGGER add_partition_foreign_key_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION add_partition_foreign_key();
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
partition_table TEXT;
parent_table TEXT;
partition_name TEXT;
BEGIN
IF TG_TAG = 'CREATE TABLE' THEN
partition_table := TG_TABLE_NAME;
parent_table := 'parent_table_' || to_char(NEW.partition_key, 'YYYY_MM_DD');
EXECUTE format('ALTER TABLE %I ADD CONSTRAINT fk_%I_%I FOREIGN KEY (partition_key, id) REFERENCES %I (partition_key, id)', partition_table, partition_table, parent_table, parent_table);
END IF;
END;
$$;
CREATE EVENT TRIGGER add_partition_foreign_key_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION add_partition_foreign_key();
