Re: [HACKERS] pg_upgrade vs extension upgrades

[Robert Haas]

On Thu, Apr 13, 2017 at 3:48 PM, Magnus Hagander  wrote:
>> Well, pg_upgrade creates ./analyze_new_cluster.sh, but that just
>> contains:
>>
>> "/u/pgsql/bin/vacuumdb" --all --analyze-in-stages
>>
>> Seems like we should just get rid of ./analyze_new_cluster.sh and tell
>> the user to run vacuumdb directly.  I guess I will have to wait for PG
>> 11 to do that though.
>
> Yeah, at this point that probably makes a lot of sense, now that we don't
> need the logic in the script anymore.
>
> FWIW, I'm not sure the feature freeze means we can't *remove* a feature? But
> I'll defer to others on that.

I would view this as unnecessary tinkering that could just as well
wait until v11, although it's such a small change that I am not
prepared to spend much time arguing if you're determined to force it
through.  The point of the feature freeze is to focus effort on the
things that we need to do in order to be able to get a beta and then a
final release out the door, and to get them out the door on time and
with adequate quality.  Whether or not a change which doesn't further
those goals is technically speaking a feature isn't really relevant in
my view.  The point is to mop up the loose ends and ship, and to avoid
things that have a chance of creating new loose ends.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Magnus Hagander]

On Thu, Apr 13, 2017 at 9:13 PM, Bruce Momjian  wrote:

> On Thu, Apr 13, 2017 at 09:02:27PM +0200, Magnus Hagander wrote:
> > On Thu, Apr 13, 2017 at 12:30 AM, Peter Eisentraut <
> > peter.eisentr...@2ndquadrant.com> wrote:
> >
> > On 4/10/17 11:30, Magnus Hagander wrote:
> > > After you've run pg_upgrade, you have to loop through all your
> databases
> > > and do an "ALTER EXTENSION abc UPDATE" once for each extension.
> > >
> > > Is there a reason we shouldn't have pg_upgrade emit a script that
> does
> > > this, similar to how it emits a script to run ANALYZE?
> >
> > Shouldn't pg_dump do this, and perhaps by default?
> >
> >
> > If I restore a dump into another instance, I need to upgrade all my
> > extensions to that installations's versions, no?  That's not
> particular
> > to pg_upgrade.
> >
> > Sure, there's an argument to be made for that.  But pg_dump (or in this
> case,
> > it would more be pg_restore I guess) also doesn't run ANALYZE or
> generate a
> > script to do that, does it? ISTM that we have already decided that
> pg_upgrade
> > has a different requirement on providing those things, whereas pg_dump/
> > pg_restore is more of a low-level tool where people have to figure more
> things
> > out themselves.
>
> Well, pg_upgrade creates ./analyze_new_cluster.sh, but that just
> contains:
>
> "/u/pgsql/bin/vacuumdb" --all --analyze-in-stages
>
> Seems like we should just get rid of ./analyze_new_cluster.sh and tell
> the user to run vacuumdb directly.  I guess I will have to wait for PG
> 11 to do that though.
>
>
Yeah, at this point that probably makes a lot of sense, now that we don't
need the logic in the script anymore.

FWIW, I'm not sure the feature freeze means we can't *remove* a feature?
But I'll defer to others on that.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: [HACKERS] pg_upgrade vs extension upgrades

[Bruce Momjian]

On Thu, Apr 13, 2017 at 09:02:27PM +0200, Magnus Hagander wrote:
> On Thu, Apr 13, 2017 at 12:30 AM, Peter Eisentraut <
> peter.eisentr...@2ndquadrant.com> wrote:
> 
> On 4/10/17 11:30, Magnus Hagander wrote:
> > After you've run pg_upgrade, you have to loop through all your databases
> > and do an "ALTER EXTENSION abc UPDATE" once for each extension.
> >
> > Is there a reason we shouldn't have pg_upgrade emit a script that does
> > this, similar to how it emits a script to run ANALYZE?
> 
> Shouldn't pg_dump do this, and perhaps by default? 
> 
> 
> If I restore a dump into another instance, I need to upgrade all my
> extensions to that installations's versions, no?  That's not particular
> to pg_upgrade.
>
> Sure, there's an argument to be made for that.  But pg_dump (or in this case,
> it would more be pg_restore I guess) also doesn't run ANALYZE or generate a
> script to do that, does it? ISTM that we have already decided that pg_upgrade
> has a different requirement on providing those things, whereas pg_dump/
> pg_restore is more of a low-level tool where people have to figure more things
> out themselves.

Well, pg_upgrade creates ./analyze_new_cluster.sh, but that just
contains:

"/u/pgsql/bin/vacuumdb" --all --analyze-in-stages

Seems like we should just get rid of ./analyze_new_cluster.sh and tell
the user to run vacuumdb directly.  I guess I will have to wait for PG
11 to do that though.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Magnus Hagander]

On Thu, Apr 13, 2017 at 6:06 PM, Robert Haas  wrote:

> On Thu, Apr 13, 2017 at 11:48 AM, Peter Eisentraut
>  wrote:
> > On 4/12/17 22:56, Bruce Momjian wrote:
> >> Is pg_upgrade the right place for an extension upgrade script?  When
> >> pg_upgrade started creating an incremental-analyze script, people
> >> thought it should be more generic so it was moved to vacuumdb
> >> --analyze-in-stages.  Seems we should do the same thing for the
> >> extension upgrade script.
> >
> > Yeah, many of the things that pg_upgrade would do or suggest after an
> > upgrade could also apply to other upgrade methods, such as by logical
> > replication.  So offering them separately would be good.
>
> And in theory, extension upgrades could happen any time, not just when
> there's a major version upgrade.  You could be using a
> separately-bundled extension, or we could bump an extension version in
> a minor release to fix some issue.
>
> I think we should invent a clever name for a new utility, like
> pg_maintain or something.  It could let you know about (and optionally
> perform) a variety of optional maintenance tasks:
>
> - upgrading extensions
> - reindexing indexes for which the version has been bumped, like hash
> indexes in v10
> - reindexing or dropping indexes marked invalid that were left behind
> by a CIC failure
> - analyzing tables that lack (full?) statistics
> - triggering heap scans to reclaim HEAP_MOVED_* bits, if we have that
> kind of thing someday
>
>
I agree it makes a lot of sense to have a separate tool that can do these
things, and that pg_upgrade can point the user towards it.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: [HACKERS] pg_upgrade vs extension upgrades

[Magnus Hagander]

On Thu, Apr 13, 2017 at 12:30 AM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 4/10/17 11:30, Magnus Hagander wrote:
> > After you've run pg_upgrade, you have to loop through all your databases
> > and do an "ALTER EXTENSION abc UPDATE" once for each extension.
> >
> > Is there a reason we shouldn't have pg_upgrade emit a script that does
> > this, similar to how it emits a script to run ANALYZE?
>
> Shouldn't pg_dump do this, and perhaps by default?


> If I restore a dump into another instance, I need to upgrade all my
> extensions to that installations's versions, no?  That's not particular
> to pg_upgrade.
>
>
Sure, there's an argument to be made for that.  But pg_dump (or in this
case, it would more be pg_restore I guess) also doesn't run ANALYZE or
generate a script to do that, does it? ISTM that we have already decided
that pg_upgrade has a different requirement on providing those things,
whereas pg_dump/pg_restore is more of a low-level tool where people have to
figure more things out themselves.


-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: [HACKERS] pg_upgrade vs extension upgrades

[Robert Haas]

On Thu, Apr 13, 2017 at 11:48 AM, Peter Eisentraut
 wrote:
> On 4/12/17 22:56, Bruce Momjian wrote:
>> Is pg_upgrade the right place for an extension upgrade script?  When
>> pg_upgrade started creating an incremental-analyze script, people
>> thought it should be more generic so it was moved to vacuumdb
>> --analyze-in-stages.  Seems we should do the same thing for the
>> extension upgrade script.
>
> Yeah, many of the things that pg_upgrade would do or suggest after an
> upgrade could also apply to other upgrade methods, such as by logical
> replication.  So offering them separately would be good.

And in theory, extension upgrades could happen any time, not just when
there's a major version upgrade.  You could be using a
separately-bundled extension, or we could bump an extension version in
a minor release to fix some issue.

I think we should invent a clever name for a new utility, like
pg_maintain or something.  It could let you know about (and optionally
perform) a variety of optional maintenance tasks:

- upgrading extensions
- reindexing indexes for which the version has been bumped, like hash
indexes in v10
- reindexing or dropping indexes marked invalid that were left behind
by a CIC failure
- analyzing tables that lack (full?) statistics
- triggering heap scans to reclaim HEAP_MOVED_* bits, if we have that
kind of thing someday

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Peter Eisentraut]

On 4/12/17 22:56, Bruce Momjian wrote:
> Is pg_upgrade the right place for an extension upgrade script?  When
> pg_upgrade started creating an incremental-analyze script, people
> thought it should be more generic so it was moved to vacuumdb
> --analyze-in-stages.  Seems we should do the same thing for the
> extension upgrade script.

Yeah, many of the things that pg_upgrade would do or suggest after an
upgrade could also apply to other upgrade methods, such as by logical
replication.  So offering them separately would be good.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Bruce Momjian]

On Wed, Apr 12, 2017 at 06:59:32PM -0400, Robert Haas wrote:
> On Wed, Apr 12, 2017 at 6:30 PM, Peter Eisentraut
> > If I restore a dump into another instance, I need to upgrade all my
> > extensions to that installations's versions, no?  That's not particular
> > to pg_upgrade.
> 
> No, it's an optional step.  If we did the upgrade by default, it would
> greatly increase the chance of something failing.  For example,
> suppose the upgrade does a DROP and then CREATE of a function whose
> signature has changed.  If there's anything that depends on that
> function, this will fail.  But if we don't do it, there's no actual
> problem: the shared library is supposed to be prepared to cope with
> people still using the old SQL definition.  It is probably desirable
> to update where possible to gain access to new features, etc., but it
> shouldn't be required.
> 
> I do think there might be some value in a tool that looked for old
> extensions and tried to update them, but I'm not sure it should be
> pg_dump.

Is pg_upgrade the right place for an extension upgrade script?  When
pg_upgrade started creating an incremental-analyze script, people
thought it should be more generic so it was moved to vacuumdb
--analyze-in-stages.  Seems we should do the same thing for the
extension upgrade script.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Peter Eisentraut]

On 4/12/17 18:59, Robert Haas wrote:
> I do think there might be some value in a tool that looked for old
> extensions and tried to update them, but I'm not sure it should be
> pg_dump.

This reminds me a bit of the problem of upgrading all collations after
an upgrade.  Perhaps we can find similar solutions to both problems.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Robert Haas]

On Wed, Apr 12, 2017 at 6:30 PM, Peter Eisentraut
 wrote:
> On 4/10/17 11:30, Magnus Hagander wrote:
>> After you've run pg_upgrade, you have to loop through all your databases
>> and do an "ALTER EXTENSION abc UPDATE" once for each extension.
>>
>> Is there a reason we shouldn't have pg_upgrade emit a script that does
>> this, similar to how it emits a script to run ANALYZE?
>
> Shouldn't pg_dump do this, and perhaps by default?
>
> If I restore a dump into another instance, I need to upgrade all my
> extensions to that installations's versions, no?  That's not particular
> to pg_upgrade.

No, it's an optional step.  If we did the upgrade by default, it would
greatly increase the chance of something failing.  For example,
suppose the upgrade does a DROP and then CREATE of a function whose
signature has changed.  If there's anything that depends on that
function, this will fail.  But if we don't do it, there's no actual
problem: the shared library is supposed to be prepared to cope with
people still using the old SQL definition.  It is probably desirable
to update where possible to gain access to new features, etc., but it
shouldn't be required.

I do think there might be some value in a tool that looked for old
extensions and tried to update them, but I'm not sure it should be
pg_dump.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Peter Eisentraut]

On 4/10/17 11:30, Magnus Hagander wrote:
> After you've run pg_upgrade, you have to loop through all your databases
> and do an "ALTER EXTENSION abc UPDATE" once for each extension.
> 
> Is there a reason we shouldn't have pg_upgrade emit a script that does
> this, similar to how it emits a script to run ANALYZE?

Shouldn't pg_dump do this, and perhaps by default?

If I restore a dump into another instance, I need to upgrade all my
extensions to that installations's versions, no?  That's not particular
to pg_upgrade.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Magnus Hagander]

On Mon, Apr 10, 2017 at 6:57 PM, Tom Lane  wrote:

> Magnus Hagander  writes:
> > After you've run pg_upgrade, you have to loop through all your databases
> > and do an "ALTER EXTENSION abc UPDATE" once for each extension.
>
> > Is there a reason we shouldn't have pg_upgrade emit a script that does
> > this, similar to how it emits a script to run ANALYZE?
>
> +1 --- I think this has been discussed before, but nobody got round
> to it.
>
> Do we need to worry about the order of the updates when there are
> cross-extension dependencies?  I'm thinking that if extension A
> requires extension B, it'd be safest to update B first.
>

Good point, I wasn't considering dependencies. I was envisioning something
as simple as querying pg_available_extensions and check the versions and
then just run -- that has always worked for me, but when I think of it I
don't think I've ever had dependent extensions in those systems.

But that should probably be parsable out of
pg_available_extension_versions, I think. Because surely it should be OK to
rely only on the new dependency information, in case it has actually
changed?

And since it's an external script, it's not the end of the world if it
fails in super-cornery cases, as long as it covers the majority.


-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: [HACKERS] pg_upgrade vs extension upgrades

[Tom Lane]

Magnus Hagander  writes:
> After you've run pg_upgrade, you have to loop through all your databases
> and do an "ALTER EXTENSION abc UPDATE" once for each extension.

> Is there a reason we shouldn't have pg_upgrade emit a script that does
> this, similar to how it emits a script to run ANALYZE?

+1 --- I think this has been discussed before, but nobody got round
to it.

Do we need to worry about the order of the updates when there are
cross-extension dependencies?  I'm thinking that if extension A
requires extension B, it'd be safest to update B first.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade vs extension upgrades

[Robert Haas]

On Mon, Apr 10, 2017 at 11:30 AM, Magnus Hagander  wrote:
> After you've run pg_upgrade, you have to loop through all your databases and
> do an "ALTER EXTENSION abc UPDATE" once for each extension.
>
> Is there a reason we shouldn't have pg_upgrade emit a script that does this,
> similar to how it emits a script to run ANALYZE?

I don't know whether it's useful, but I doubt it would hurt anything.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pg_upgrade vs extension upgrades

[Magnus Hagander]

After you've run pg_upgrade, you have to loop through all your databases
and do an "ALTER EXTENSION abc UPDATE" once for each extension.

Is there a reason we shouldn't have pg_upgrade emit a script that does
this, similar to how it emits a script to run ANALYZE?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/