Обсуждение: pg_dump: WARNING: could not find operator
Hello! I'm in the process of migrating some 10.23 database instances to 15.3, and I'm getting these warnings when doing apg_dumpall on one of the servers: pg_dump: WARNING: could not find operator with OID 2234078 pg_dump: WARNING: could not find operator with OID 2234199 Is this indicative of a problem that I should fix before running pg_upgrade? If so, how? And how do I find out what these OIDs are and what's referencing them? Thanks, Ed
Ed Sabol <edwardjsabol@gmail.com> writes:
> Hello! I'm in the process of migrating some 10.23 database instances to 15.3, and I'm getting these warnings when
doinga pg_dumpall on one of the servers:
> pg_dump: WARNING: could not find operator with OID 2234078
> pg_dump: WARNING: could not find operator with OID 2234199
> Is this indicative of a problem that I should fix before running pg_upgrade? If so, how?
> And how do I find out what these OIDs are and what's referencing them?
From a quick look at the pg_dump source code, this is indicative of
dangling links in either pg_operator.oprcom, pg_operator.oprnegate,
or pg_aggregate.aggsortop. With no context it's difficult to guess
how they got there. While you could clean it up manually, pg_dump
is just going to omit those clauses from its output, so there's no
real need to do anything if that outcome is sufficient.
regards, tom lane
On Jul 11, 2023, at 6:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > While you could clean it up manually, pg_dump > is just going to omit those clauses from its output, so there's no > real need to do anything if that outcome is sufficient. I'm mainly concerned about the pg_upgrade going smoothly. I'd like to minimize downtime as much as possible. I was planningto use "pg_upgrade --link", if it matters.
On Tue, 2023-07-11 at 18:30 -0400, Ed Sabol wrote: > On Jul 11, 2023, at 6:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > While you could clean it up manually, pg_dump > > is just going to omit those clauses from its output, so there's no > > real need to do anything if that outcome is sufficient. > > I'm mainly concerned about the pg_upgrade going smoothly. I'd like to minimize > downtime as much as possible. I was planning to use "pg_upgrade --link", if it matters. Then you should test the upgrade first. To find out which operators are causing the trouble, you can run queries like SELECT * FROM pg_operator WHERE oprcom IN (2234078, 2234199) OR oprnegate IN (2234078, 2234199); and test everything that uses these broken operators particularly well. Yours, Laurenz Albe
On Jul 12, 2023, at 2:26 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > To find out which operators are causing the trouble, you can run queries like > > SELECT * FROM pg_operator > WHERE oprcom IN (2234078, 2234199) > OR oprnegate IN (2234078, 2234199); > > and test everything that uses these broken operators particularly well. OK, I found them in one of our databases. Based on the oprcode values, they seem to be related to an extension we have installed(pgsphere, in case anyone is curious). Probably from an old version of the extension, I'm guessing, as the currentpgsphere passes all regression testing. So how do I clean up these dangling operators? Is it as easy as the following? DELETE FROM pg_operator WHERE oprcom IN (2234078, 2234199) OR oprnegate IN (2234078, 2234199); Thanks, Ed
On 7/12/23 22:04, Ed Sabol wrote: > On Jul 12, 2023, at 2:26 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote: >> To find out which operators are causing the trouble, you can run queries like >> >> SELECT * FROM pg_operator >> WHERE oprcom IN (2234078, 2234199) >> OR oprnegate IN (2234078, 2234199); >> >> and test everything that uses these broken operators particularly well. > OK, I found them in one of our databases. Based on the oprcode values, they seem to be related to an extension we haveinstalled (pgsphere, in case anyone is curious). Probably from an old version of the extension, I'm guessing, as thecurrent pgsphere passes all regression testing. > > So how do I clean up these dangling operators? Is it as easy as the following? > > DELETE FROM pg_operator > WHERE oprcom IN (2234078, 2234199) > OR oprnegate IN (2234078, 2234199); Will they automagically disappear when you restore the pg_dump? -- Born in Arizona, moved to Babylonia.
On Jul 12, 2023, at 11:08 PM, Ron <ronljohnsonjr@gmail.com> wrote: > Will they automagically disappear when you restore the pg_dump? Yeah, that's what Tom Lane said, but I'm not planning to restore the pg_dump (unless something catastrophic happens). I'mplanning to use "pg_upgrade --link". Thanks, Ed
"Edward J. Sabol" <edwardjsabol@gmail.com> writes:
> On Jul 12, 2023, at 11:08 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>> Will they automagically disappear when you restore the pg_dump?
> Yeah, that's what Tom Lane said, but I'm not planning to restore the pg_dump (unless something catastrophic happens).
I'mplanning to use "pg_upgrade --link".
As far as catalog data is concerned, pg_upgrade is the same as
pg_dumpall + restore.
regards, tom lane