Thread: Weeding out unused user created database objects, could I use pg_catalog?
Hi, I have been written several psql functions, tiggers and defined several tables over time for a database application I have been developing. The application has evolved as I have gained better understanding of the solution and so I have written newer psql functions and other database objects inline with this evolution and stopped calling (making use of) the older database objects I had previously written. Now I would like to only keep these database objects that are currently been used by the application. I have dedicted a single database object for use with this application, and I have all the DDL scripts in files which I execute for a clean deployment. If I start with a clean deployment, is there a way I could perhaps query the table(s) in pg_catalog for example to find out the database objects (I have constructed) that have been invoked or used in some way during a complete run of my application. I had a quick look at the pg_catalog but was unable to determine the tables that may contain pieces of this information. If pg_catalog could provide me with this solution, what are the table(s) to query? Allan.
Re: Weeding out unused user created database objects, could I use pg_catalog?
From
Richard Huxton
Date:
On 12/02/10 12:32, Allan Kamau wrote: > If I start with a clean deployment, is there a way I could perhaps > query the table(s) in pg_catalog for example to find out the database > objects (I have constructed) that have been invoked or used in some > way during a complete run of my application. I had a quick look at the > pg_catalog but was unable to determine the tables that may contain > pieces of this information. If pg_catalog could provide me with this > solution, what are the table(s) to query? Quickest solution might be to use the --list option of pg_restore (you'll need -Fc on pg_dump too). That will list everything in the database dump and you can just compare the lists. -- Richard Huxton Archonet Ltd
Re: Weeding out unused user created database objects, could I use pg_catalog?
From
Allan Kamau
Date:
On Fri, Feb 12, 2010 at 3:47 PM, Richard Huxton <dev@archonet.com> wrote: > On 12/02/10 12:32, Allan Kamau wrote: >> >> If I start with a clean deployment, is there a way I could perhaps >> query the table(s) in pg_catalog for example to find out the database >> objects (I have constructed) that have been invoked or used in some >> way during a complete run of my application. I had a quick look at the >> pg_catalog but was unable to determine the tables that may contain >> pieces of this information. If pg_catalog could provide me with this >> solution, what are the table(s) to query? > > Quickest solution might be to use the --list option of pg_restore (you'll > need -Fc on pg_dump too). That will list everything in the database dump and > you can just compare the lists. > > -- > Richard Huxton > Archonet Ltd > I have the DDL scripts of both the old and the new database objects mixed together, I am looking for a way to distinguish between them. The objects accessed at any point during the complete run of the application are the ones I would like to retain. I have no other way to distinguish between the useful and the defunct objects. Therefore I am looking for a solution that contains "last-accessed-time" data for these objects, especially for the functions and maybe the triggers. Allan.
Re: Weeding out unused user created database objects, could I use pg_catalog?
From
Richard Huxton
Date:
On 12/02/10 15:10, Allan Kamau wrote: > On Fri, Feb 12, 2010 at 3:47 PM, Richard Huxton<dev@archonet.com> wrote: >> On 12/02/10 12:32, Allan Kamau wrote: >>> >>> If I start with a clean deployment, is there a way I could perhaps >>> query the table(s) in pg_catalog for example to find out the database >>> objects (I have constructed) that have been invoked or used in some >>> way during a complete run of my application. I had a quick look at the >>> pg_catalog but was unable to determine the tables that may contain >>> pieces of this information. If pg_catalog could provide me with this >>> solution, what are the table(s) to query? >> >> Quickest solution might be to use the --list option of pg_restore (you'll >> need -Fc on pg_dump too). That will list everything in the database dump and >> you can just compare the lists. > I have the DDL scripts of both the old and the new database objects > mixed together, I am looking for a way to distinguish between them. > The objects accessed at any point during the complete run of the > application are the ones I would like to retain. I have no other way > to distinguish between the useful and the defunct objects. > > Therefore I am looking for a solution that contains > "last-accessed-time" data for these objects, especially for the > functions and maybe the triggers. Ah, sorry - misunderstood. There's not any timestamp kept. As you can imagine, it would be a cost you'd have to pay every time you accessed an object. The best you can do is to turn on statement logging, parse the logs to see what objects are used and then keep those and their dependencies. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> On 12/02/10 15:10, Allan Kamau wrote:
>> Therefore I am looking for a solution that contains
>> "last-accessed-time" data for these objects, especially for the
>> functions and maybe the triggers.
> Ah, sorry - misunderstood. There's not any timestamp kept. As you can
> imagine, it would be a cost you'd have to pay every time you accessed an
> object.
> The best you can do is to turn on statement logging, parse the logs to
> see what objects are used and then keep those and their dependencies.
Or: remove some objects, run your test case, see if it succeeds.
Repeat as needed.
regards, tom lane
Re: Weeding out unused user created database objects, could I use pg_catalog?
From
Richard Huxton
Date:
On 12/02/10 18:13, Tom Lane wrote: > Richard Huxton<dev@archonet.com> writes: > >> The best you can do is to turn on statement logging, parse the logs to >> see what objects are used and then keep those and their dependencies. > > Or: remove some objects, run your test case, see if it succeeds. > Repeat as needed. If you've lost track of which database objects are in use, I'm not sure your test suite is such a reliable indicator. -- Richard Huxton Archonet Ltd
Re: Weeding out unused user created database objects, could I use pg_catalog?
From
Allan Kamau
Date:
On Fri, Feb 12, 2010 at 9:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Richard Huxton <dev@archonet.com> writes: >> On 12/02/10 15:10, Allan Kamau wrote: >>> Therefore I am looking for a solution that contains >>> "last-accessed-time" data for these objects, especially for the >>> functions and maybe the triggers. > >> Ah, sorry - misunderstood. There's not any timestamp kept. As you can >> imagine, it would be a cost you'd have to pay every time you accessed an >> object. > >> The best you can do is to turn on statement logging, parse the logs to >> see what objects are used and then keep those and their dependencies. > > Or: remove some objects, run your test case, see if it succeeds. > Repeat as needed. > > regards, tom lane > Thanks Richard and Tom for your suggestions, I already have statement logging (as the application is still in development phase) in CSV format. I will create a table of the same structure as this log file and import the data into the DB, then perform the neccessary queries on this table. Since I am also capturing the duration per statement I will use the opportunity to streamline some of my queries and stored procedures. Allan.