Thread: Recreating functions after starting the database server.

Recreating functions after starting the database server.

From
Konrad Witaszczyk
Date:
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.


Konrad


Attachment

Re: Recreating functions after starting the database server.

From
Achilleas Mantzios
Date:
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?
It would help to just give an example of your situation and what you are trying to solve.
Why recreate triggers, indexes and functions after server startup instead of leaving them?
Why create needless traffic?
>
>
> Konrad
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: Recreating functions after starting the database server.

From
Konrad Witaszczyk
Date:
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.

>>
>>
>> Konrad



Attachment

Re: Recreating functions after starting the database server.

From
Achilleas Mantzios
Date:
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



Re: Recreating functions after starting the database server.

From
Konrad Witaszczyk
Date:
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

Re: Recreating functions after starting the database server.

From
Steven Lembark
Date:
> 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 not just update the proc's, etc, that depend on the changes?
 
-- 
Steven Lembark                                       1505 National Ave
Workhorse Computing                                 Rockford, IL 61103
lembark@wrkhors.com                                    +1 888 359 3508


Re: Recreating functions after starting the database server.

From
"David G. Johnston"
Date:
On Wed, Jan 31, 2018 at 5:45 AM, Konrad Witaszczyk <def@freebsd.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.

Re: Recreating functions after starting the database server.

From
raf@raf.org
Date:
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.
> 
> Konrad

Hi,

I think the usual way to do this is to have a table containing a
schema version number that can be consulted to work out where
the schema is up to so as to know which schema migration scripts
still need to be run to bring the database up to date. This is
useful for upgrading a system on multiple database servers. All
the cool kids would be doing this. However, I think this is more
common with ORMs where all the code lives outside the database.

I often have to load old database backups that need to be
brought up to date so that the current software will work with
it. I have date/time stamps in the filename of the database
backup and my script to load the backup consults a directory
full of all schema migration scripts that are also date/time
stamped. It then applies the schema migration scripts that are
dated after the time that the backup was made.

After applying all the schema migration scripts, it runs another
script to audit the state of the database functions with respect
to the "source". It then reloads any functions that differ from
the current source. I don't use views or triggers but the same
idea would apply.

My main advice is to not call any functions (or rely on any
views or triggers) inside schema migration scripts because you
never know if they'll exist or not when they are needed.

Also, if any update scripts massage data rather than just change
the schema, don't assume that the data exists just because it
exists when you wrote the migration script. The script might run
on a very old backup taken before the data existed. Or at least
write migrations that avoid producing errors if the data isn't
present. Minimise the output so that errors don't go unnoticed.

I'd also recommend writing a tool that compares the functions,
triggers and views in the database against the code for them on
disk somewhere and reload the ones that differ from source. Once
you have such an audit tool, it's very easy to see what needs to
be reloaded and the tool can do that too.

A very important part of such an audit tool is that it
(probably?) needs to perform a topological sort on the
functions, triggers and views to load them in a sensible order.
Inspect the source for them all looking for references between
them and do a topoligical sort so you can load things that are
depended on before loading the things that depend on them. This
might not actually be necessary but I think it is (or I wouldn't
have bothered doing it unless it's cargo cult behaviour on my
part from Sybase days). :-)

I use the function auditing tool to load new or changed
functions on multiple servers all the time but I only apply old
schema migration scripts when loading backups.

One last thing, if you need to keep very old backups
indefinitely for legal reasons, make a point of reloading them
every year to apply the last year's worth of schema changes and
make a backup of that updated version. It makes it less likely
that bit rot will ruin your day in the distant future. But also
keep the original backup because sometimes you need the backup
as it was without any changes made after the fact. The ancient
version won't work with current application code but that can be
the problem of whoever has demanded the data in its original
form.

I've never encountered any problems with consistency and
performance issues in doing something like this but I only have
small databases so that doesn't mean there won't be any.

Good luck!

cheers,
raf