Re: Recreating functions after starting the database server.

On Wed, Jan 31, 2018 at 5:45 AM, Konrad Witaszczyk <def(at)freebsd(dot)org> wrote:

> Hi,
>
> I'm considering changing my database schema upgrade routines to recreate
> functions and all objects depending on them (e.g. triggers, views) just
> after
> the database server is started. It would make the routines much simpler
> since
> they wouldn't have to depend on the history of the schema.
>
> Does anyone has any experience with recreating all functions and triggers
> to
> upgrade a database schema assuming that during the upgrade there are no
> client
> connections to the database?
>
> Does anyone see any problems with such approach in terms of consistency and
> performance? I'm not familiar with PostgreSQL internals and I'm not sure
> how it
> would affect the planner when using various function volatile categories.

​The planner pretty much starts from scratch every time a client starts a
new database session. You area of concern would be limited to bloating of
the pg_* catalogs. Tossing a vacuum in at the end of rebuild should
largely solve that problem.

I've done limited work with recreating the entire database, tables
included, though obviously only is situations where you can handle filling
in table data on every reload. It has its advantages but its quite limited
and I'm quickly coming to the conclusion that learning and using a proper
database migration tool would be more generally useful. While it probably
makes development iteration more difficult if you don't use it in
development you aren't testing the tools and code you'd be applying to
production.

I'm not sure I have the right answer but having a couple of iterations of
doing this with an adhoc and custom framework (using the term loosely) I'm
coming to believe this is something that, like backups, you want to find an
existing tool and community and join it.

David J.