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 $$
key     ALIAS FOR  $1;
//check if the record exists in the feature table of the local schema
EXECUTE 'SELECT feature_id FROM ' || schemaname || '.feature WHERE feature_id=' || key';
RETURN true;
//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;
RETURN false;
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.

  • Thank you. Wouldn't copying all gene ids to the public schema solve the issue (and have the FKs reference only one table, the public one)? (and for the record, I have no idea, just guessing that the functional check is causing the efficiency problem. There might be some other solution for that.) – ypercubeᵀᴹ Apr 6 '16 at 13:05
  • I thought about that and it seems as a simple solution,but unfortunately this is not an option because of how database is set up. The schemas are used so that the data could be separately modified without touching other schemas(each species has its own schema so you can add and remove species easily), which worked very neatly before adding these records which need cross schema records, and these are a few maybe couple of hundreds but they are important. There are also other tables which depend on this within each schema but I didn't list them all so I could keep things simple. – Fluid51 Apr 6 '16 at 13:24
  • And how is the public feature table updated? Can each schema app add and delete rows there at will? Or just add new ones? Or is it some other procedure that does that? – ypercubeᵀᴹ Apr 6 '16 at 13:25
  • there is a separate procedure for that, most of the schemas data is entered through bulk load scripts while these records are entered through custom CMS record by record, and have a separate backup pipeline. – Fluid51 Apr 6 '16 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.