PostgreSQL stuff

I ran pg_upgrade on a dev cluster of mine and it halted on some shared libraries I had forgot to install for the new cluster.

Most of them were plain contrib that I'll continue to use so no problem there but there were some testing code I had been playing around with. I didn't know in which of my test databases I had used it so I had to find out somehow.

This is what I came up with, a query to list what shared libraries are used in a database. With that information I could drop the functions depending on shared libraries I no longer used.

select
    p.proname as "name",
    pg_catalog.pg_get_function_arguments(p.oid) as "args",
    n.nspname as "namespace",
    p.probin::text as "lib",
    u.usename as "owner"
from pg_proc p
    join pg_user u on u.usesysid=p.proowner
    join pg_language l on l.oid=p.prolang
    join pg_namespace n on n.oid=p.pronamespace
where
    nspname not in ('pg_catalog', 'information_schema')
    and lanname='c'

To avoid running it manually on all databases I wrote some python code to do that for me. If it's of use to anyone I'd be more than happy to share that too.