public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Renzo Dani <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Issue with pg_dump due to Schema OID Error
Date: Thu, 19 Dec 2024 08:50:16 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+XOKQDaHqA0eGU15fXVg904Kt-u3TwRrHDpthOEs6JdykQVkQ@mail.gmail.com>
References: <CA+XOKQCwLv_oJ+bFO7hbhzM_fZU7Kvst7uEZfYNNrSnzhT=kxQ@mail.gmail.com>
<CA+XOKQDnx3m6HXCb3--3nhDji1iY7Koms5cD7cmUTJjcERUwMQ@mail.gmail.com>
<[email protected]>
<CA+XOKQBXogzXP1bduZ=ppt2XscWhwc69F-19ua8bpcvebLAQOw@mail.gmail.com>
<[email protected]>
<CA+XOKQDaHqA0eGU15fXVg904Kt-u3TwRrHDpthOEs6JdykQVkQ@mail.gmail.com>
On 12/19/24 08:46, Renzo Dani wrote:
Again.
Reply to list also using Reply All.
Ccing list.
> Hi Adrian,
> here a new version of the script that I just tested produce the same
> problem.
> The script do not rely now on any additional relation than the ones
> defined into the script.
>
> BR
> Renzo
>
> On Thu, Dec 19, 2024 at 5:28 PM Adrian Klaver <[email protected]
> <mailto:[email protected]>> wrote:
>
> On 12/19/24 08:21, Renzo Dani wrote:
>
> Reply to list also.
> Ccing list.
>
> > HI Adrian,
> > you are right, there is a typo, the correct would be to have:
> > MyTestBugSchema2.afunction( u.username )
> >
> > In any case the problem appears in my tests also with that script.
>
> To be clear the test script did not use MyTestBugSchema2.afunction(
> u.username ) but instead MyTestBugSchema.afunction( u.username ).
>
> If that is the case where did MyTestBugSchema come from?
>
> > I think the execution is not really important, I suppose what
> create the
> > problem is the drop cascade of the schema.
>
> It is important if someone wants to replicate the test case.
>
> >
> > BR
> > Renzo
> >
> > On Thu, Dec 19, 2024 at 5:15 PM Adrian Klaver
> <[email protected] <mailto:[email protected]>
> > <mailto:[email protected]
> <mailto:[email protected]>>> wrote:
> >
> > On 12/19/24 01:43, arons wrote:
> > > I forgot to attache the script.
> >
> > In MyTestBugSchema01.baseProc() you meant to have:
> >
> > select MyTestBugSchema.afunction( u.username )
> >
> > instead of
> >
> > select MyTestBugSchema2.afunction( u.username )
> >
> > In other words there is a predefined MyTestBugSchema?
> >
> > >
> > > On Thu, Dec 19, 2024 at 10:41 AM Renzo Dani
> <[email protected] <mailto:[email protected]>
> > <mailto:[email protected] <mailto:[email protected]>>
> > > <mailto:[email protected] <mailto:[email protected]>
> <mailto:[email protected] <mailto:[email protected]>>>> wrote:
> > >
> > > Hi,
> > >
> > >
> > > Recently, I encountered a problem during a database
> export using
> > > pg_dump.
> > >
> > >
> > > Here is the error message:
> > >
> > >
> > > pg_dump: last built-in OID is 16383
> > >
> > > pg_dump: reading extensions
> > >
> > > pg_dump: identifying extension members
> > >
> > > pg_dump: reading schemas
> > >
> > > pg_dump: reading user-defined tables
> > >
> > > pg_dump: reading user-defined functions
> > >
> > > pg_dump: error: schema with OID 41960442 does not exist
> > >
> > >
> > > To investigate the issue, I ran the following query:
> > >
> > >
> > > SELECT * FROM pg_proc WHERE pronamespace = 41960442;
> > >
> > >
> > > The result:
> > >
> > >
> > >
> >
> oid;proname;pronamespace;proowner;prolang;procost;prorows;provariadic;prosupport;prokind;prosecdef;proleakproof;proisstrict;proretset;provolatile;proparallel;pronargs;pronargdefaults;prorettype;proargtypes;proallargtypes;proargmodes;proargnames;proargdefaults;protrftypes;prosrc;probin;prosqlbody;proconfig;proacl
> > >
> > >
> >
> 41966618;remapprotocoltypeids;41960442;19214494;13547;100;0;0;-;f;f;f;f;f;v;u;1;0;25;25;;;{pprotocoltypeids};;;
> > >
> > >
> > > I resolved the issue by removing the problematic
> record (admin
> > > privileges required):
> > >
> > >
> > > DELETE FROM pg_proc WHERE oid = 41966618;
> > >
> > >
> > > This situation seems inconsistent and likely should
> not occur
> > under
> > > normal conditions.
> > >
> > >
> > > While I’m unsure exactly when this issue originated in our
> > > environment, I was able to reproduce it by performing
> concurrent
> > > modifications on the schema.
> > >
> > >
> > > To demonstrate, I wrote a bash script (test_bug.sh) that
> > starts two
> > > threads running in parallel.
> > >
> > > Each thread drops the schema with CASCADE and recreates it
> > using the
> > > SQL script search_bug.sql.
> > >
> > >
> > > To use the script, you’ll need to adapt two variables
> at the
> > > beginning of the script: PGPASSWORD and URL.
> > >
> > >
> > > Using this script, I reproduced the problem on PostgreSQL
> > versions
> > > 16.1 and 17.1.
> > >
> > > It typically takes less than a minute to trigger the
> issue.
> > >
> > > The script terminates automatically as soon as the
> problem is
> > detected.
> > >
> > >
> > > Here are additional references that might be related
> to this
> > issue:
> > >
> > >
> > >
> >
> https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net <https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net; <https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net <https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net>; <https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net <https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net; <https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net <https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net>>;
> > >
> > >
> >
> https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com <https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com; <https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com <https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com>; <https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com <https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com; <https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com <https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com>&...;
> > >
> > >
> > >
> > > Let me know if you need additional information.
> > >
> > >
> > > Best regards
> > >
> > > Renzo
> > >
> >
> > --
> > Adrian Klaver
> > [email protected] <mailto:[email protected]>
> <mailto:[email protected] <mailto:[email protected]>>
> >
>
> --
> Adrian Klaver
> [email protected] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
Attachments:
[application/sql] search_bug.sql (865B, 2-search_bug.sql)
download
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Issue with pg_dump due to Schema OID Error
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox