On 01/31/2018 14:32, Achilleas Mantzios wrote: > 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. I know that it works as long as a function doesn't change its return type. I'd like to cover this case as well not to worry about it in the future. Hence I'm looking for a general method with the above assumptions.
Attachment:
signature.asc
Description: OpenPGP digital signature
