Thread: Recreating functions after starting the database server.
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
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
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
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
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
> 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
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.
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