Re: Weeding out unused user created database objects, could I use pg_catalog?

On 12/02/10 15:10, Allan Kamau wrote:
> On Fri, Feb 12, 2010 at 3:47 PM, Richard Huxton<dev(at)archonet(dot)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