pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump: WARNING: could not find operator
8+ messages / 5 participants
[nested] [flat]
* pg_dump: WARNING: could not find operator
@ 2023-07-11 22:15 Ed Sabol <edwardjsabol@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ed Sabol @ 2023-07-11 22:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hello! I'm in the process of migrating some 10.23 database instances to 15.3, and I'm getting these warnings when doing a 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?
Thanks,
Ed
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump: WARNING: could not find operator
@ 2023-07-11 22:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ed Sabol <edwardjsabol@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-07-11 22:24 UTC (permalink / raw)
To: Ed Sabol <edwardjsabol@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 doing a 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump: WARNING: could not find operator
@ 2023-07-11 22:30 Ed Sabol <edwardjsabol@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Ed Sabol @ 2023-07-11 22:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump: WARNING: could not find operator
@ 2023-07-12 06:26 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ed Sabol <edwardjsabol@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2023-07-12 06:26 UTC (permalink / raw)
To: Ed Sabol <edwardjsabol@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump: WARNING: could not find operator
@ 2023-07-13 03:04 Ed Sabol <edwardjsabol@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: Ed Sabol @ 2023-07-13 03:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 current 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);
Thanks,
Ed
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump: WARNING: could not find operator
@ 2023-07-13 03:08 Ron <ronljohnsonjr@gmail.com>
parent: Ed Sabol <edwardjsabol@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2023-07-13 03:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 have installed (pgsphere, in case anyone is curious). Probably from an old version of the extension, I'm guessing, as the current 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump: WARNING: could not find operator
@ 2023-07-13 04:08 Edward J. Sabol <edwardjsabol@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Edward J. Sabol @ 2023-07-13 04:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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'm planning to use "pg_upgrade --link".
Thanks,
Ed
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump: WARNING: could not find operator
@ 2023-07-13 04:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Edward J. Sabol <edwardjsabol@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2023-07-13 04:26 UTC (permalink / raw)
To: Edward J. Sabol <edwardjsabol@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
"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'm planning to use "pg_upgrade --link".
As far as catalog data is concerned, pg_upgrade is the same as
pg_dumpall + restore.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-07-13 04:26 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-11 22:15 pg_dump: WARNING: could not find operator Ed Sabol <edwardjsabol@gmail.com>
2023-07-11 22:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-11 22:30 ` Ed Sabol <edwardjsabol@gmail.com>
2023-07-12 06:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-07-13 03:04 ` Ed Sabol <edwardjsabol@gmail.com>
2023-07-13 03:08 ` Ron <ronljohnsonjr@gmail.com>
2023-07-13 04:08 ` Edward J. Sabol <edwardjsabol@gmail.com>
2023-07-13 04:26 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox