Re: [HACKERS] pg_upgrade and missing loadable libraries
On Sun, Jun 4, 2017 at 02:30:58PM -0400, Tom Lane wrote: > Bruce Momjian 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 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 and missing loadable libraries
Bruce Momjian 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 -- 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 and missing loadable libraries
On Sun, Jun 4, 2017 at 02:04:37PM -0400, Tom Lane wrote: > Bruce Momjian 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 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 and missing loadable libraries
Bruce Momjian 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 -- 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 and missing loadable libraries
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 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 and missing loadable libraries
Bruce Momjian 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 -- 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 and missing loadable libraries
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 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 and missing loadable libraries
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 Herrerahttps://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 and missing loadable libraries
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
