Issue with pg_dump due to Schema OID Error
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
Re: Issue with pg_dump due to Schema OID Error
HI Adrian, I did additional tests and I can reproduce the problem also without the extensions. I did the test multiple time with a script that I put as attachment (wholetest.sh). I added also the file log_different_run.txt that contains the results of some test and the times it takes to reproduce. BR Renzo On Thu, Dec 19, 2024 at 9:42 PM Renzo Dani wrote: > Hi Adrian, > Thanks for the test, I installed also version 17.2 and test with a fresh > newly created db. > At the beginning I cannot reproduce the problem as well. > Than I start adding the extension we use, one after one, and testing in > between. > > I found out that the problem appear immediately as soon as I add the > extension pg_stat_statements: > > create extension pg_stat_statements WITH SCHEMA public; > > alter system set shared_preload_libraries = 'pg_stat_statements'; > > Can you maybe test as well with this extension? > the other that I installed before that are: pgcrypto and pg_prewarm. > > here the current installed one: > > dbtest=# \dx > List of installed extensions > Name| Version | Schema | > Description > > +-++ > pg_prewarm | 1.2 | public | prewarm relation data > pg_stat_statements | 1.11| public | track planning and execution > statistics of all SQL statements executed > pgcrypto | 1.3 | public | cryptographic functions > plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural language > (4 rows) > > dbtest=# select version(); > version > > > -- > PostgreSQL 17.2 reda build on x86_64-pc-linux-gnu, compiled by gcc > (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit > (1 row) > > > I'll do some additional tests. > > BR > Renzo > > > > On Thu, Dec 19, 2024 at 6:21 PM Adrian Klaver > wrote: > >> On 12/19/24 08:50, Adrian Klaver wrote: >> > On 12/19/24 08:46, Renzo Dani wrote: >> > >> > Again. >> > >> > Reply to list also using Reply All. >> > Ccing list. >> > >> >> Hi Adrian, >> >> here a new version of the script that I just tested produce the same >> >> problem. >> >> The script do not rely now on any additional relation than the ones >> >> defined into the script. >> >> I could not replicate using Ubuntu 22.04 and Postgres 16.6. >> >> From your original post: >> >> "I reproduced the problem on PostgreSQL versions 16.1 and 17.1." >> >> The latest bug fixes of each are 16.6 and 17.2. I would upgrade to those >> and then try again. >> >> >> >> >> BR >> >> Renzo >> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >> ... [Thread1] Restarting loop. [Thread2] Sleeping for 0.609 seconds. [Thread1] Sleeping for 0.443 seconds. [Thread1] Thu Dec 19 10:01:36 PM CET 2024 [Thread1] Running install script psql:search_bug.sql:1: NOTICE: drop cascades to function mytestbugschema02.afunction(text) DROP SCHEMA [Thread2] Thu Dec 19 10:01:36 PM CET 2024 [Thread2] Running install script CREATE SCHEMA CREATE FUNCTION psql:search_bug.sql:14: NOTICE: drop cascades to function mytestbugschema01.baseproc() psql:search_bug.sql:1: NOTICE: drop cascades to function mytestbugschema02.afunction(text) DROP SCHEMA DROP SCHEMA CREATE SCHEMA CREATE SCHEMA CREATE PROCEDURE CREATE FUNCTION psql:search_bug.sql:14: NOTICE: drop cascades to function mytestbugschema01.baseproc() DROP SCHEMA DROP SCHEMA CREATE SCHEMA CREATE SCHEMA CREATE FUNCTION 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 ---+---+--+--+-+-+-+-++-+---+--+-+---+-+-+--+-++-++-+-++-+
Re: Issue with pg_dump due to Schema OID Error
Hi Adrian, Thanks for the test, I installed also version 17.2 and test with a fresh newly created db. At the beginning I cannot reproduce the problem as well. Than I start adding the extension we use, one after one, and testing in between. I found out that the problem appear immediately as soon as I add the extension pg_stat_statements: create extension pg_stat_statements WITH SCHEMA public; alter system set shared_preload_libraries = 'pg_stat_statements'; Can you maybe test as well with this extension? the other that I installed before that are: pgcrypto and pg_prewarm. here the current installed one: dbtest=# \dx List of installed extensions Name| Version | Schema | Description +-++ pg_prewarm | 1.2 | public | prewarm relation data pg_stat_statements | 1.11| public | track planning and execution statistics of all SQL statements executed pgcrypto | 1.3 | public | cryptographic functions plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural language (4 rows) dbtest=# select version(); version -- PostgreSQL 17.2 reda build on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit (1 row) I'll do some additional tests. BR Renzo On Thu, Dec 19, 2024 at 6:21 PM Adrian Klaver wrote: > On 12/19/24 08:50, Adrian Klaver wrote: > > On 12/19/24 08:46, Renzo Dani wrote: > > > > Again. > > > > Reply to list also using Reply All. > > Ccing list. > > > >> Hi Adrian, > >> here a new version of the script that I just tested produce the same > >> problem. > >> The script do not rely now on any additional relation than the ones > >> defined into the script. > > I could not replicate using Ubuntu 22.04 and Postgres 16.6. > > From your original post: > > "I reproduced the problem on PostgreSQL versions 16.1 and 17.1." > > The latest bug fixes of each are 16.6 and 17.2. I would upgrade to those > and then try again. > > >> > >> BR > >> Renzo > >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
