How to delete unused sequences?

Question

We are using PostgreSQL. My requirement is to delete unused sequences from my database. For example, if I create any table through my application, one sequence will be created, but for deleting the table we are not deleting the sequence, too. If want to create the same table another sequence is being created.

Example: table: file; automatically created sequence for id coumn: file_id_seq

When I delete the table file and create it with same name again, a new sequence is being created (i.e. file_id_seq1). I have accumulated a huge number of unused sequences in my application database this way.

How to delete these unused sequences?

Insufficient

Answer 1

A sequence that is created automatically for a serial column is deleted automatically, when the column (or its table) is dropped. The problem you describe should not exist to begin with. Only very old versions of PostgreSQL did not do that. 7.4 or older?

Solution for the problem

This query will generate the DDL commands to delete all "unbound" sequences in the database it is executed in:

SELECT string_agg('DROP SEQUENCE ' || c.oid::regclass, '; ') || ';' AS ddl
FROM   pg_class       c
LEFT   JOIN pg_depend d ON d.refobjid = c.oid
                       AND d.deptype <> 'i'
WHERE  c.relkind = 'S'
AND    d.refobjid IS NULL;

The cast to regclass in c.oid::regclass automatically schema-qualifies sequence names where necessary according to the current search_path. See:

• How to check if a table exists in a given schema
• How does the search_path influence identifier resolution and the "current schema"

Result:

DROP SEQUENCE foo_id_seq;
DROP SEQUENCE bar_id_seq;
...

Execute the result to drop all sequences that are not bound to a serial column (or any other column). Study the meaning of columns and tables here.

Careful! These sequences might be in use otherwise. There are use cases where sequences are created as standalone objects. For instance, if you want multiple columns to share one sequence. You should know exactly what you are doing.

However, you cannot delete sequences bound to a serial column this way. So the operation is safe in this respect.

DROP SEQUENCE test_id_seq;

Result:

ERROR:  cannot drop sequence test_id_seq because other objects depend on it
DETAIL:  default for table test column id depends on sequence test_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Jacquenette

What I do is same like your solution. It is correct way or not i dont know. i get all the sequences. then saved in file.sql then i run the file. code is: – Insufficient

\o d:/test1.sql SELECT 'drop sequence ' || c.relname || ';' FROM pg_class c WHERE (c.relkind = 'S'); \o \i d:/test1.sql – Insufficient

@user1023877: That tries to drop any and all sequences. Which should fail if any "bound" sequence exists. – Jacquenette

so This is not the proper solution right, so i will move on to another solution .thank you. – Insufficient

yes, i will move on to the another solution means your solution only. thank you. – Insufficient

I'm running postgres 9.3.4 and a table's sequence is NOT deleted for me when the table is dropped. I still have to delete the sequence manually. – Buzzer

@Amalgovinus: Then the sequence is not owned by the PK column like it would be when created with the serial pseudo-type. See: https://mcmap.net/q/28912/-creating-a-postgresql-sequence-to-a-field-which-is-not-the-id-of-the-record or https://mcmap.net/q/525000/-drop-sequence-and-cascade and – Jacquenette

You're right, my problem was that I was lacking a PK on the sequence field. – Buzzer

@Amalgovinus: I am sure I am right, but the PK has nothing to with it. :) The ownership is the important piece here. – Jacquenette

Just reporting that this problem can happen in Postgres 8.4, but not happened every time for me. Maybe it is a bug, maybe somebody messed up something in my database, I don't know. – Robi

Answer 2

If you are using pgAdmin, you can select the sequence and check the "depends on" tab. It will list any object that relies on the sequence.

Another way is to TRY to delete the sequence. If a table references it, pgAdmin will throw an error saying that something is depending on this sequence. If you are able to delete the sequence without any errors, there is no dependency.

Be sure to test this somewhere.

Uretic

But it is too difficult to drop all sequences one by one manually – Insufficient

@Insufficient you should than write a script that does something similar to this. You could read the syscatalog to see dependencies, but writing something like this can be tedious as well. Making it short: I don't think there is an easy way for such a task. – Uretic

Okay Thank you. could you refer any links which will provide information about how to write script? – Insufficient

Try the official docs and infos for the system catalog – Uretic

Thank you. I will go through it. – Insufficient

Answer 3

What i do is first I got all the sequences and then saved these result into a file then i run the file in psql: below content was saved with file name del_seq_all.sql and then list sequences in test1.sql . i dont know this is the correct solution or not. But result is coming as expected.

\o d:/test1.sql
SELECT 'drop sequence ' || c.relname || ';' FROM pg_class c WHERE
(c.relkind = 'S');
\o

\i d:/test1.sql

Insufficient

Answer 4

Proceed with caution, "drop sequence sequence_name_here" will successfully drop a sequence even if it's attached as a default nextval() value of a table column. There seems to be some disconnect here especially if the sequence was created separately. I'm also looking for the perfect one liner to clean up 100% unused sequences.

Former

Answer 5

Building on the answer by @erwin:

DO $$ DECLARE
    r text;
BEGIN
    FOR r IN (
            SELECT cl.relname
            FROM pg_class cl
            LEFT JOIN pg_namespace ns ON ns."oid" = cl.relnamespace
            LEFT JOIN pg_depend d ON d.refobjid = cl."oid" AND d.deptype <> 'i'
            WHERE ns.nspname = 'public' 
                AND cl.relkind = 'S'
                AND d.refobjid IS NULL
        ) LOOP
            -- dangerous, test before you execute!
            RAISE NOTICE '%',  -- once confident, comment this line ...
      --    EXECUTE         -- ... and uncomment this one
                'DROP SEQUENCE ' || quote_ident(r);
    END LOOP;
END $$;

This will actually execute the query and produce the intended result

Mineralogist