0

In my vb.net application's back-end using is PostgreSQL.application uses temporary tables to do some works(since am a DBA i dont know what they are doing). today i saw something wrong in the DB.Basically DB 's number of sequences is 70, but in one DB i saw (see the below image)

enter image description here

  • this DB has some problems like slowness,unexpected server shut down

So give some suggestions to delete these sequences from DB

Community
  • 1
  • 1
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
  • 1
    This seems to be a duplicate of http://stackoverflow.com/questions/8265540/how-to-delete-unused-sequences. Use this answer http://stackoverflow.com/a/8270947/1283020 – Krut Jun 12 '14 at 07:23

1 Answers1

0

i got the solution for this

create a query to find out the sequences to drop

SELECT 'drop sequence ' || c.relname || ';' FROM pg_class c WHERE (c.relkind = 'S') and (c.relname not ilike '%gtab%' and c.relname not ilike '%gcom%')

  • this will return all the unsed(in my case) sequences

enter image description here

Using copy command exported the results to .CSV

  • copy( SELECT 'drop sequence ' || c.relname || ';' FROM pg_class c WHERE (c.relkind = 'S') and (c.relname not ilike '%gtab%' and c.relname not ilike '%gcom%')) to 'D:/DelSeq.csv' with csv header

  • myCsv File enter image description here

  • I have copied the entire results from .csv and executed it as pgScript

DONE

Vivek S.
  • 19,945
  • 7
  • 68
  • 85