Thread: 10.4 upgrade, function markings, and template0
Good afternoon, The PostgreSQL 10.4 upgrade involves changes to some function markings (see release notes, E.1.2, second and third bulletpoints for specifics). One way to make these changes is to use `ALTER FUNCTION` to change the functions in your existingdatabases. While this was mainly straightforward, I'm unclear on whether the `template0` database must be changedin this manner or if it is automatically updated when the upgrade is applied. The documentation indicates that ingeneral you shouldn't manually change the `template0` database. -- Dominic Jones <jonesd@xmission.com>
Good afternoon,
The PostgreSQL 10.4 upgrade involves changes to some function markings (see release notes, E.1.2, second and third bullet points for specifics). One way to make these changes is to use `ALTER FUNCTION` to change the functions in your existing databases. While this was mainly straightforward, I'm unclear on whether the `template0` database must be changed in this manner or if it is automatically updated when the upgrade is applied. The documentation indicates that in general you shouldn't manually change the `template0` database.
If you ever go and CREATE DATABASE TEMPLATE template0 you will not get the correct markings unless you've updated template0 (w/o TEMPLATE template0 you pull from template1, probably want to update that as well).
Related question - the post-installation instructions that are part of the news releases seem like something that should be part of the release notes...but they are not.
David J.
Dominic Jones <jonesd@xmission.com> writes: > The PostgreSQL 10.4 upgrade involves changes to some function markings (see release notes, E.1.2, second and third bulletpoints for specifics). One way to make these changes is to use `ALTER FUNCTION` to change the functions in your existingdatabases. While this was mainly straightforward, I'm unclear on whether the `template0` database must be changedin this manner or if it is automatically updated when the upgrade is applied. The documentation indicates that ingeneral you shouldn't manually change the `template0` database. Yes, you'd need to fix it in template0 as well, or you risk subsequently-created databases not having the fix. See previous minor releases where we've given more painstaking detail about applying catalog corrections, e.g. 9.6.4: https://www.postgresql.org/docs/current/static/release-9-6-4.html I didn't bother with spelling it all out in full detail this time, which maybe was a mistake, but I felt that probably most users wouldn't need to bother with these changes at all (unlike the case where a catalog correction is security-related). regards, tom lane
On 05/14/2018 02:02 PM, Tom Lane wrote: > Dominic Jones <jonesd@xmission.com> writes: >> The PostgreSQL 10.4 upgrade involves changes to some function markings (see release notes, E.1.2, second and third bulletpoints for specifics). One way to make these changes is to use `ALTER FUNCTION` to change the functions in your existingdatabases. While this was mainly straightforward, I'm unclear on whether the `template0` database must be changedin this manner or if it is automatically updated when the upgrade is applied. The documentation indicates that ingeneral you shouldn't manually change the `template0` database. > > Yes, you'd need to fix it in template0 as well, or you risk > subsequently-created databases not having the fix. See previous > minor releases where we've given more painstaking detail about > applying catalog corrections, e.g. 9.6.4: > > https://www.postgresql.org/docs/current/static/release-9-6-4.html > > I didn't bother with spelling it all out in full detail this time, > which maybe was a mistake, but I felt that probably most users > wouldn't need to bother with these changes at all (unlike the case > where a catalog correction is security-related). Well what is nice about the news release is you can cut and past the entire list of commands and do the updates en masse. > > regards, tom lane > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 05/14/2018 02:02 PM, Tom Lane wrote:
>> I didn't bother with spelling it all out in full detail this time,
>> which maybe was a mistake, but I felt that probably most users
>> wouldn't need to bother with these changes at all (unlike the case
>> where a catalog correction is security-related).
> Well what is nice about the news release is you can cut and past the
> entire list of commands and do the updates en masse.
It'd be nice to have some more-automated way of doing this type of
correction. Ordinary scripting doesn't look very promising, because
I don't see an easy way to deal with the need to connect to every
database in the cluster; that seems to depend on a lot of local
characteristics about usernames and authentication.
Maybe it'd be worth building some sort of infrastructure that would
allow this to be done at a lower level. It's not hard to imagine
an autovacuum-like or bgworker-based thingy that could run around
and apply a given SQL script in every database, bypassing the usual
worries about authentication and connections-disabled databases.
That seems like a lot of work for a need that only comes up once in
awhile, but perhaps it'd have more applications than just catalog
corrections.
regards, tom lane
On 05/14/2018 02:22 PM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> On 05/14/2018 02:02 PM, Tom Lane wrote: >>> I didn't bother with spelling it all out in full detail this time, >>> which maybe was a mistake, but I felt that probably most users >>> wouldn't need to bother with these changes at all (unlike the case >>> where a catalog correction is security-related). > >> Well what is nice about the news release is you can cut and past the >> entire list of commands and do the updates en masse. > > It'd be nice to have some more-automated way of doing this type of > correction. Ordinary scripting doesn't look very promising, because > I don't see an easy way to deal with the need to connect to every > database in the cluster; that seems to depend on a lot of local > characteristics about usernames and authentication > > Maybe it'd be worth building some sort of infrastructure that would > allow this to be done at a lower level. It's not hard to imagine > an autovacuum-like or bgworker-based thingy that could run around > and apply a given SQL script in every database, bypassing the usual > worries about authentication and connections-disabled databases. > That seems like a lot of work for a need that only comes up once in > awhile, but perhaps it'd have more applications than just catalog > corrections. That would be helpful given that a major version has a 5 year supported life span. I can see folks not deciding to do the manual work at the minor release because at that time it does not apply and the work does not seem worth it. Then at some point in the future conditions change and they wonder why things are not working the way they should. I know I would be grateful. > > regards, tom lane > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, 14 May 2018 17:02:25 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dominic Jones <jonesd@xmission.com> writes: > > The PostgreSQL 10.4 upgrade involves changes to some function markings (see release notes, E.1.2, second and third bulletpoints for specifics). One way to make these changes is to use `ALTER FUNCTION` to change the functions in your existingdatabases. While this was mainly straightforward, I'm unclear on whether the `template0` database must be changedin this manner or if it is automatically updated when the upgrade is applied. The documentation indicates that ingeneral you shouldn't manually change the `template0` database. > > Yes, you'd need to fix it in template0 as well, or you risk > subsequently-created databases not having the fix. See previous > minor releases where we've given more painstaking detail about > applying catalog corrections, e.g. 9.6.4: > > https://www.postgresql.org/docs/current/static/release-9-6-4.html > > I didn't bother with spelling it all out in full detail this time, > which maybe was a mistake, but I felt that probably most users > wouldn't need to bother with these changes at all (unlike the case > where a catalog correction is security-related). > > regards, tom lane > Yes, the link does address the issue and answer the question. It looks like I didn't see the previous upgrade's discussionbecause the change to which it was tied didn't appear to be relevant to the database deployment involved. -- Dominic Jones <jonesd@xmission.com>
On Mon, May 14, 2018 at 05:22:39PM -0400, Tom Lane wrote: > Maybe it'd be worth building some sort of infrastructure that would > allow this to be done at a lower level. It's not hard to imagine > an autovacuum-like or bgworker-based thingy that could run around > and apply a given SQL script in every database, bypassing the usual > worries about authentication and connections-disabled databases. A portion of the infrastructure is already available for background workers which can use BGWORKER_BYPASS_ALLOWCONN since Postgres 11 to enforce connections to databases even if an administrator disables connections to it. -- Michael