public inbox for [email protected]
help / color / mirror / Atom feedFrom: arons <[email protected]>
To: [email protected]
Subject: Re: Issue with pg_dump due to Schema OID Error
Date: Thu, 19 Dec 2024 10:43:20 +0100
Message-ID: <CA+XOKQDnx3m6HXCb3--3nhDji1iY7Koms5cD7cmUTJjcERUwMQ@mail.gmail.com> (raw)
In-Reply-To: <CA+XOKQCwLv_oJ+bFO7hbhzM_fZU7Kvst7uEZfYNNrSnzhT=kxQ@mail.gmail.com>
References: <CA+XOKQCwLv_oJ+bFO7hbhzM_fZU7Kvst7uEZfYNNrSnzhT=kxQ@mail.gmail.com>
I forgot to attache the script.
On Thu, Dec 19, 2024 at 10:41 AM Renzo Dani <[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/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com
>
>
>
> Let me know if you need additional information.
>
>
> Best regards
>
> Renzo
>
Attachments:
[application/octet-stream] search_bug.sql (893B, 3-search_bug.sql)
download
[application/octet-stream] test_bug.sh (1.2K, 4-test_bug.sh)
download
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]
Subject: Re: Issue with pg_dump due to Schema OID Error
In-Reply-To: <CA+XOKQDnx3m6HXCb3--3nhDji1iY7Koms5cD7cmUTJjcERUwMQ@mail.gmail.com>
* 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