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.
