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 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.
--
Dominic Jones <jonesd@xmission.com>
On Mon, May 14, 2018 at 1:42 PM, Dominic Jones <jonesd@xmission.com> wrote:
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 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.
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 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.
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 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.
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 discussion because 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