On 31/01/2018 15:22, Konrad Witaszczyk wrote:
On 01/31/2018 14:03, Achilleas Mantzios wrote:
On 31/01/2018 14:45, Konrad Witaszczyk 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.
Do you have indexes that use those functions?
There are no indexes which use functions. I can see it would be a problem in the
other case since indexes would have to be rebuilt. Thanks for pointing it out.
It would help to just give an example of your situation and what you are trying
to solve.
In my case the upgrade routines run migration scripts which modify a database
schema. Besides that I have a bunch of SQL files with the schema that can be
used to initialize a new database.
Why recreate triggers, indexes and functions after server startup instead of
leaving them?
Why create needless traffic?
I would like to eliminate the migration scripts to have all definitions in one
file which would be easier to maintain. I'm mainly interested in functions and
hence objects depending on them. However I wouldn't like to recreate indexes as
it's not needed.
If it is only functions which retain their signature and only change the code, there is no need to touch their related objects. You can CREATE OR REPLACE those functions and change nothing else.
Konrad
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
