public inbox for [email protected]  

help / color / mirror / Atom feed

From: Renzo Dani <[email protected]>
To: [email protected]
Subject: Issue with pg_dump due to Schema OID Error
Date: Thu, 19 Dec 2024 10:50:09 +0100
Message-ID: <CA+XOKQAPT1cH8poTHjNVT5DCpsDG3x2O5rOEJQW116YNSfDbdg@mail.gmail.com> (raw)

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+XOKQAPT1cH8poTHjNVT5DCpsDG3x2O5rOEJQW116YNSfDbdg@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