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.

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

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.
>
> --
>  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.

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

Richard Huxton <dev(at)archonet(dot)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

On 12/02/10 18:13, Tom Lane wrote:
> Richard Huxton<dev(at)archonet(dot)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

On Fri, Feb 12, 2010 at 9:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Richard Huxton <dev(at)archonet(dot)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.