Re: Issue with pg_dump due to Schema OID Error

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 <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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 <renzo(dot)dani(at)gmail(dot)com
> <mailto:renzo(dot)dani(at)gmail(dot)com>
> > <mailto:renzo(dot)dani(at)gmail(dot)com <mailto:renzo(dot)dani(at)gmail(dot)com>>> 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/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
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com