Feedback-ID: i76984098:Fastmail
Message-ID: <c7544d8e-3374-4c27-838f-f982d4722d04@aklaver.com>
Date: Thu, 19 Dec 2024 08:28:04 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Issue with pg_dump due to Schema OID Error
To: Renzo Dani <arons7@gmail.com>
References: <CA+XOKQCwLv_oJ+bFO7hbhzM_fZU7Kvst7uEZfYNNrSnzhT=kxQ@mail.gmail.com>
 <CA+XOKQDnx3m6HXCb3--3nhDji1iY7Koms5cD7cmUTJjcERUwMQ@mail.gmail.com>
 <a4f362fe-7035-4871-a080-07a0309b7ab0@aklaver.com>
 <CA+XOKQBXogzXP1bduZ=ppt2XscWhwc69F-19ua8bpcvebLAQOw@mail.gmail.com>
Content-Language: en-US
Cc: pgsql-general <pgsql-general@postgresql.org>
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CA+XOKQBXogzXP1bduZ=ppt2XscWhwc69F-19ua8bpcvebLAQOw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/c7544d8e-3374-4c27-838f-f982d4722d04%40aklaver.com>
Precedence: bulk

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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.dani@gmail.com
>     <mailto:renzo.dani@gmail.com>
>      > <mailto:renzo.dani@gmail.com <mailto:renzo.dani@gmail.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com