1

I intended to drop all functions in all schemas within a certain database, however, after I executed the bash file containing the following code block, the functions are still there

# use pg_dump and pg_restore to migrate some databases from another server

psql $DBNAME << EOF
DO \$$
DECLARE
    myschema RECORD;
    myview RECORD;
    myfunc RECORD;
    myseq RECORD;
BEGIN

    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE 'DROP VIEW ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myview.viewname) || ';';
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE 'DROP SEQUENCE ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myseq.sequence_name) || ';';
        END LOOP;

        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name )
        LOOP
            EXECUTE 'DROP FUNCTION ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myfunc.routine_name) || ';';
        END LOOP;

    END LOOP;

END;
\$$ LANGUAGE plpgsql;

After I partition the do block into three separate do blocks for dropping all views/functions/sequences, I managed to drop all sequences. I assume this is because the transaction must be atomic. However, because I intend to clean the databases and drop all unused functions/sequences/views, I cannot know ahead which are dependent ones and will raise errors. Can I ask plpgsql to ignore the error so that the unused views/functions/functions can be successfully dropped?


I revised my code to based the suggestions from @LaurenzAlbe. However, I got to error ERROR: syntax error at or near "EXCEPTION". Did I place the EXCEPTION statements in the wrong place?

psql $DBNAME << EOF    
DO \$$
DECLARE
    myschema RECORD;
    myseq RECORD;
    myview RECORD;
    myfunc RECORD;
BEGIN
    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP
        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name)
        LOOP
            EXECUTE format('DROP FUNCTION %I.%I', myschema.schema_name, myfunc.routine_name);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myfunc.routine_name;
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE format('DROP SEQUENCE %I.%I', myschema.schema_name, myseq.sequence_name);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myseq.sequence_name;
        END LOOP;

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE format('DROP VIEW %I.%I', myschema.schema_name, myview.viewname);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myview.viewname;
        END LOOP;
    END LOOP;

END;
\$$ LANGUAGE plpgsql;
  • Do you get any error messages? – Laurenz Albe Jun 18 at 14:15
  • @LaurenzAlbe I got error from dropping the sequences, because some other objects depend on them. But after switching the order of dropping sequences and functions, the problem did not go away. Of course, the error from dropping the sequence remains. Is it the reason? – kumom Jun 18 at 14:19
1

Your code is executed in a single DO statement and hence in a single transaction. So if any of the statements cause an error, all statements will be rolled back.

If you want to avoid that, you can use subtransactions by using an EXCEPTION clause, for example:

BEGIN
    EXECUTE format('DROP SEQUENCE %I.%I',
                   myschema.schema_name),
                   myseq.sequence_name);
EXCEPTION
    WHEN dependent_objects_still_exist THEN
        RAISE WARNING 'could not drop object %.% because of dependencies',
                      myschema.schema_name,
                      myseq.sequence_name;
END;

Then the failed statement is rolled back, but processing continues.

Another thing you could consider is using

DROP ... CASCADE

which will remove dependent objects automatically.

  • Can I make it output which sequence causes the error? I added EXCEPTION \ WHEN dependent_objects_still_exist THEN \ RAISE WARNING format('Could not drop sequence %I because of dependencies', myseq.sequence_name); in the inner loop after the EXECUTE statement, but I got ERROR: syntax error at or near "EXCEPTION" – kumom Jun 18 at 14:40
  • The syntax error did not go away. Could you take a look at my modified code? – kumom Jun 18 at 14:50
  • 1
    You forgot the BEGIN and END;, they are required and define the scope of the subtransaction. – Laurenz Albe Jun 18 at 14:52
  • Thanks a lot! I did not know this is required for subtransaction. – kumom Jun 18 at 14:55

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.