I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records. The way I did this was to remove the foreign key on some tables in each schema and instead adding a check function which then checks if the reference exists in the current schema or the public schema. All this went well, but now I have a problem with pg_restore, before I could restore a database in 30 minutes now it takes a few days. Could it be the problem of the function checking row by row, is there a way to speed up this process (I tried with the -j job option) ?
Here is the function and how it is applied.
CREATE FUNCTION check_features(integer, schemaname text) RETURNS boolean AS $$ DECLARE key ALIAS FOR $1; BEGIN //check if the record exists in the feature table of the local schema EXECUTE 'SELECT feature_id FROM ' || schemaname || '.feature WHERE feature_id=' || key'; IF FOUND THEN RETURN true; END IF; //if it does not exist in the local schema check if it exists in the public schema IF EXISTS (SELECT feature_id FROM public.feature WHERE feature_id=key) THEN RETURN true; ELSE RETURN false; END IF; END; $$ LANGUAGE plpgsql;
the way you use the function
ALTER TABLE particular_schema.feature_relationship DROP CONSTRAINT feature_relationship_subject_id_fkey; ALTER TABLE particular_schema.feature_relationship ADD CONSTRAINT feature_relationship_subject_id_check CHECK (check_features(subject_id,'particular_schema'));
thanks for help
Why the foreign key didn't work
I will try to make this as simple as I can.
I have a table(and a copy of this table in each schema) containing for example gene ids, before each schema had separated data and there was no problem.
With development there was a need to have some of the gene ids which were common and needed to be linked to other schemas, so to be able to link them with other schemas I decided to copy them to public schema, but the remaining ones which are schema specific stayed in their own particular schemas.
This gene id table is also linked to other tables like gene_relationship(gene_id1, gene_id2) etc. and of course these tables had FK constraint pointing to the gene ids table. So as I had to separate these records into public and particular schema I decided to use the function shown here.
The function checks first within the particular schema to see if the record is there(returns true if found) and after it checks within the public(returns true if found false if not found), it is something like the conditional foreign key.
Also this is a specific database which should not be changed much because it follows a certain design to be able to link other applications to it.