Thread: [HACKERS] pg_upgrade and missing loadable libraries
I have seen a few reports where people are getting this pg_upgrade error: Your installation references loadable libraries that are missingfrom the new installation. You can add these libraries tothenew installation, or remove the functions using them from theold installation. A list of problem libraries is in thefile: ./loadable_libraries.txt and the file contains: could not load library "$libdir/pgpool-regclass":ERROR: could not access file "$libdir/pgpool-regclass": No such file ordirectory The problem is that there is no indicate of which database to look in. Should we adjust the output to suggest the first database that has it, or update the instructions to mention they have to look in all databases, and give them some instructions on finding the problem? This seems to be one of the last pg_upgrade problems, along with preserving optimizer statistics, which I am hoping to do for PG 11. -- Bruce Momjian <bruce@momjian.us> 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 +
On 2017-06-04 13:06:25 -0400, Bruce Momjian wrote: > This seems to be one of the last pg_upgrade problems Famous last words.
Bruce Momjian wrote: > I have seen a few reports where people are getting this pg_upgrade > error: > > Your installation references loadable libraries that are missing > from the new installation. You can add these libraries to the > new installation, or remove the functions using them from the > old installation. A list of problem libraries is in the file: > > ./loadable_libraries.txt > > and the file contains: > > could not load library "$libdir/pgpool-regclass": > ERROR: could not access file "$libdir/pgpool-regclass": No such file or directory > > The problem is that there is no indicate of which database to look in. I think it'd be better to be exhaustive about the report, i.e. report all problems in all databases, if possible. Doing repeated pg_upgrade attempts until you've nailed all the problems is boring ... -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sun, Jun 4, 2017 at 01:20:12PM -0400, Alvaro Herrera wrote: > Bruce Momjian wrote: > > I have seen a few reports where people are getting this pg_upgrade > > error: > > > > Your installation references loadable libraries that are missing > > from the new installation. You can add these libraries to the > > new installation, or remove the functions using them from the > > old installation. A list of problem libraries is in the file: > > > > ./loadable_libraries.txt > > > > and the file contains: > > > > could not load library "$libdir/pgpool-regclass": > > ERROR: could not access file "$libdir/pgpool-regclass": No such file or directory > > > > The problem is that there is no indicate of which database to look in. > > I think it'd be better to be exhaustive about the report, i.e. report > all problems in all databases, if possible. Doing repeated pg_upgrade > attempts until you've nailed all the problems is boring ... Well, I think there are three open items: * should we print all the database names involved * should we print all the pg_proc.pronames that are involved, not just the unique library names * should we output a query helping people find the pg_proc entries I think there are many cases where DROP EXTENSION XXX fixes the problem, and in those cases showing pg_proc.pronames or giving them a query to find them is a negative --- they should be pointed to DROP EXTENSION. Can we detect when to recommend one over the other? Can we tell which proc entries will not be in the dump and can be ignored? -- Bruce Momjian <bruce@momjian.us> 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 +
Bruce Momjian <bruce@momjian.us> writes:
> On Sun, Jun 4, 2017 at 01:20:12PM -0400, Alvaro Herrera wrote:
>> I think it'd be better to be exhaustive about the report, i.e. report
>> all problems in all databases, if possible. Doing repeated pg_upgrade
>> attempts until you've nailed all the problems is boring ...
> Well, I think there are three open items:
> * should we print all the database names involved
Yes.
> * should we print all the pg_proc.pronames that are involved, not just
> the unique library names
> * should we output a query helping people find the pg_proc entries
> I think there are many cases where DROP EXTENSION XXX fixes the problem,
Yes. I think in most cases nowadays there's a one-for-one correlation
between extensions and libraries; drilling down to the level of individual
functions would just be confusing clutter. I think if you just print
a report saying "these libraries are referenced in these databases",
that would be sufficiently usable in most cases.
You could think about printing a script full of DROP EXTENSION commands,
but aside from the sheer difficulty of doing that, it doesn't seem all
that helpful. Simply dropping every extension is usually *not* the
right answer, and it could easily lead to data loss if done blindly.
Usually people are going to need to stop and think anyway.
regards, tom lane
On Sun, Jun 4, 2017 at 01:55:01PM -0400, Tom Lane wrote: > > * should we print all the pg_proc.pronames that are involved, not just > > the unique library names > > * should we output a query helping people find the pg_proc entries > > > I think there are many cases where DROP EXTENSION XXX fixes the problem, > > Yes. I think in most cases nowadays there's a one-for-one correlation > between extensions and libraries; drilling down to the level of individual > functions would just be confusing clutter. I think if you just print > a report saying "these libraries are referenced in these databases", > that would be sufficiently usable in most cases. OK. > You could think about printing a script full of DROP EXTENSION commands, > but aside from the sheer difficulty of doing that, it doesn't seem all > that helpful. Simply dropping every extension is usually *not* the > right answer, and it could easily lead to data loss if done blindly. > Usually people are going to need to stop and think anyway. The problem is that in some cases extensions are improperly removed or the extension has bugs that leaves pg_proc entries around that aren't dumped, but are seen by pg_upgrade and generate an error. In these cases, and I have seen a few recently, we don't give the user any way to find the cause except ask for assistance, i.e. we don't show them the query we used to find the problem libraries. -- Bruce Momjian <bruce@momjian.us> 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 +
Bruce Momjian <bruce@momjian.us> writes:
> The problem is that in some cases extensions are improperly removed or
> the extension has bugs that leaves pg_proc entries around that aren't
> dumped, but are seen by pg_upgrade and generate an error. In these
> cases, and I have seen a few recently, we don't give the user any way to
> find the cause except ask for assistance, i.e. we don't show them the
> query we used to find the problem libraries.
Meh. I think that sort of situation is one in which non-experts are
going to need help in any case. It's unlikely that pg_upgrade can,
or should try to, offer them advice sufficient to fix the problem.
Also, I completely reject the idea that pg_upgrade's output should
be optimized for that situation rather than the typical "you forgot
to install these extensions in the new installation" case.
regards, tom lane
On Sun, Jun 4, 2017 at 02:04:37PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > The problem is that in some cases extensions are improperly removed or > > the extension has bugs that leaves pg_proc entries around that aren't > > dumped, but are seen by pg_upgrade and generate an error. In these > > cases, and I have seen a few recently, we don't give the user any way to > > find the cause except ask for assistance, i.e. we don't show them the > > query we used to find the problem libraries. > > Meh. I think that sort of situation is one in which non-experts are > going to need help in any case. It's unlikely that pg_upgrade can, > or should try to, offer them advice sufficient to fix the problem. > > Also, I completely reject the idea that pg_upgrade's output should > be optimized for that situation rather than the typical "you forgot > to install these extensions in the new installation" case. I didn't want to optimize for it --- I wanted a way to detect when DROP EXTENSION has no hope of working, and give more details. I assume the problem with that is the the object names are inside SQL scripts that cannot be easily interrogated. Are the pg_proc entries tied to the extension in some verifiable way that we could identify orphaned pg_proc lines? -- Bruce Momjian <bruce@momjian.us> 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 +
Bruce Momjian <bruce@momjian.us> writes:
> I didn't want to optimize for it --- I wanted a way to detect when DROP
> EXTENSION has no hope of working, and give more details. I assume the
> problem with that is the the object names are inside SQL scripts that
> cannot be easily interrogated. Are the pg_proc entries tied to the
> extension in some verifiable way that we could identify orphaned pg_proc
> lines?
You could look for 'e'-type pg_depend entries.
regards, tom lane
On Sun, Jun 4, 2017 at 02:30:58PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > I didn't want to optimize for it --- I wanted a way to detect when DROP > > EXTENSION has no hope of working, and give more details. I assume the > > problem with that is the the object names are inside SQL scripts that > > cannot be easily interrogated. Are the pg_proc entries tied to the > > extension in some verifiable way that we could identify orphaned pg_proc > > lines? > > You could look for 'e'-type pg_depend entries. OK, I will run some tests later and report back. Thanks. -- Bruce Momjian <bruce@momjian.us> 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 +
On Sun, Jun 4, 2017 at 01:06:25PM -0400, Bruce Momjian wrote: > I have seen a few reports where people are getting this pg_upgrade > error: > > Your installation references loadable libraries that are missing > from the new installation. You can add these libraries to the > new installation, or remove the functions using them from the > old installation. A list of problem libraries is in the file: > > ./loadable_libraries.txt > > and the file contains: > > could not load library "$libdir/pgpool-regclass": > ERROR: could not access file "$libdir/pgpool-regclass": No such file or directory > > The problem is that there is no indication of which database to look in. > Should we adjust the output to suggest the first database that has it, > or update the instructions to mention they have to look in all > databases, and give them some instructions on finding the problem? I have just implemented a fix for this 2017 report by reporting all databases. -- Bruce Momjian <bruce@momjian.us> 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 +