4 ms·
For now, the following works for us: - For plpgsql functions that are required by app queries, we just roll them out when we write them / when they change (via
by drob 13y ago
For now, the following works for us:
- For plpgsql functions that are required by app queries, we just roll them out when we write them / when they change (via ansible).
- For plpgsql functions used in jobs, the job just reloads the relevant plpgsql functions on the relevant DBs before they start doing anything. (It's a little wasteful, but not in a way that matters for now.)
- The UDFs we've written in C don't change too often, but we deploy them manually when they do.
What are some of the headaches you've had in managing stored procs? How often is your app code changing / requiring new ones?
- erichurkman 13y agoOne headache I had in the past was with a highly-available app with stored procs. It was rare to do parallel app server restarts, they were instead done in serial. (This did make certain migrations a pain.) Outdated app servers would continue calling pgpgsql functions in the outdated way until they received new code. We solved it with a small wrapper around the calls to plpgsql functions — we set up a build process to generate unique names that were called by version. During deploy, we'd have two or more versions of the 'same' function running in parallel. The last deploy step dropped the now-outdated functions. It worked relatively well.
- jeltz 13y agoHave you thought about using schemas and the search path for this? That is what Zalando does to deploy stored procedures without risking any downtime.
- erichurkman 13y agoIf I had to do that again, I would. It's been a while, but as I recall from that project the database adapter had shoddy support for search paths.