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
search_bug.sql
Description: Binary data
test_bug.sh
Description: Binary data
