Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tOD4c-004qdL-QH for pgsql-general@arkaria.postgresql.org; Thu, 19 Dec 2024 09:44:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tOD4b-00AGEP-8F for pgsql-general@arkaria.postgresql.org; Thu, 19 Dec 2024 09:44:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tOD4a-00AGEE-LF for pgsql-general@lists.postgresql.org; Thu, 19 Dec 2024 09:44:00 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tOD4X-000MH9-Fa for pgsql-general@postgresql.org; Thu, 19 Dec 2024 09:43:59 +0000 Received: by mail-ed1-x52b.google.com with SMTP id 4fb4d7f45d1cf-5d4e2aa7ea9so925709a12.2 for ; Thu, 19 Dec 2024 01:43:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734601436; x=1735206236; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=CddWtTAsTxttH79TTyPwgnXprX1zJ11/3EBLu4fkWJA=; b=NdN1qy5EYx5NZsWk45yGurG6YnFNetVs0nspwEfaaKBK88sypt9iAZ6YRdmfFha2Hf La8TrDgzFYN7Uio+Dr4Qf3E0J80AZDloC1noqtHpY3fZsoAFKF22OP1d6NmsZbq+GxvY httRhFZ66OPflTawfd1EUWtKroID9W7Vzs9De7cjlKMJkgcFo0bpQTtRw60HNQ6mBXd7 gBBusVYfXFr+FHI50vezz0NHfb4zlNdOxMUX8pqQXnDsu+hHaal09m0Ael4/aeitvUvJ UIGxjJbDOaqbFPOTBRvctGXpwYF2DAPxRmKrusLwiLEno1Fp6Adb1OXixR6wC4DPKoM3 uUgg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734601436; x=1735206236; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=CddWtTAsTxttH79TTyPwgnXprX1zJ11/3EBLu4fkWJA=; b=Njf7CgZViwwXAZsvZHDDwANqLRqZqArZipnA2nn22MvU9zf0HnFDwhFwCiCVmd9+fl UR+bIovt5B1y4Zs3ZOv8kRIjXlEaqEY4q95YKC4baXtwbUJgch+gK9tLkGuzL1Qq4+cM 1whcKhTVjajfAGiQ9YA+xOrQAkmuvspZSOFSHbehcgU5Qwm1FAdT96BzL691zg2911Ol yI7p+bx+IxSLamB2ML2nvimtYV3NvBvDUN+gQRJSNmp3f6uuYXS3F+Sf/IJSNSU9QhRw tY7K0lovHIvJf5lfccwH62LlnRnKuCu6hCKuDJsExBpaKSQam4Yk0KqllNvvOo9JA4Tu mG9Q== X-Gm-Message-State: AOJu0YyTgfzBPWq2Dr2hw0edg2w+6KBS4/CLF0TPDb4Eptz9Va76jT4Z Yv9omcaSK0ocYEpnEZkTbH4BvgbUYi1fV9qniK4VYpccu5V9OkX8aqfnQAMC1h30VQF7IQeoUF6 etl9/X5IJMtRKBF9JvWJr8IsWZWPfNg== X-Gm-Gg: ASbGnct4KK4t3/Q2J0GrK/PQROFDEVOICSdToFnQg2MWlebO7QqUHI3Xq7U2/jYLrww vLEWPr+CFv0f4Bcfm61K/GuBTf6sVYlL27HpZ X-Google-Smtp-Source: AGHT+IE6ftnxSKnpTWtTUFAvgjmv+eEL59nempJFeciPU5Gm2DJ13rNYRJybFrlpp8Ky6O9H5KGNXQOqoprivrDS2DQ= X-Received: by 2002:a05:6402:5418:b0:5d1:2631:b897 with SMTP id 4fb4d7f45d1cf-5d7ee390c42mr4842910a12.14.1734601435758; Thu, 19 Dec 2024 01:43:55 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: arons Date: Thu, 19 Dec 2024 10:43:20 +0100 Message-ID: Subject: Re: Issue with pg_dump due to Schema OID Error To: pgsql-general@postgresql.org Content-Type: multipart/mixed; boundary="0000000000006800b406299c5e51" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006800b406299c5e51 Content-Type: multipart/alternative; boundary="0000000000006800af06299c5e4f" --0000000000006800af06299c5e4f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I forgot to attache the script. On Thu, Dec 19, 2024 at 10:41=E2=80=AFAM Renzo Dani = 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 =3D 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 =3D 41966618; > > > This situation seems inconsistent and likely should not occur under norma= l > conditions. > > > While I=E2=80=99m unsure exactly when this issue originated in our enviro= nment, 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=E2=80=99ll need to adapt two variables at the begi= nning 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-pa= radox.net > > > https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF8974= 8704F5%40bandwidth.com > > > > Let me know if you need additional information. > > > Best regards > > Renzo > --0000000000006800af06299c5e4f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I forgot to attache the script.

On Thu, Dec 19, 2024 at 10:41=E2=80=AFAM Renzo Dani <renzo.dani@gmail.com> wrote:

Hi,


Recently, I encountered a problem during a dat= abase 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 m= embers

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 =3D 41960442;


The result:


oid;proname;pronamespace;proowner;prolang;procost= ;prorows;provaria= dic;p= rosupport;prokind;prosecdef;proleakproof;proisstrict;proretset;provolatile;proparallel;pronargs;pronargdefaults;= prorettype;proarg= types;proallargtypes;proargmodes;proargnames;proargdefaults;protrftypes;prosrc;probin;prosqlbody;proconfig;proacl

419= 66618;remapp= rotocoltypeids;41960442;19214494;13547;100;0;0;-;f;f;f;f;f;v;u;1;0;2= 5;25;;;{ppro= tocoltypeids};;;


I resolved the issue by removing the problematic= record (adm= in privileges required):


DELETE FROM pg_proc WHERE oid =3D 41966618;


=

Th= is situation seems inconsistent and likely should not occur under normal co= nditions.


While I=E2=80=99m unsure exactly when this issue originated in ou= r environment, I was able to reproduce it by performing concurrent modifica= tions on the schema.


To demonstrate, I wrote a bash script (test_bug.sh) tha= t starts two threads running in parallel.

Each thread drops the schema with C= ASCADE and recreates it using the SQL script search_bug.sql.


To use the scri= pt, you=E2=80=99ll need to adapt two variables at the beginning of the script: P= GPASSWORD 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 detecte= d.

=

Here are additional references that might be related to this issue:


https://www.postgresql.org/message-id/f= lat/20110209003823.GA93840%40mr-paradox.net

https://www.postgresql.org/message-id/flat/BB8AF37= F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com



Let me know if you need = additional information.


Best regards

Renzo

=
--0000000000006800af06299c5e4f-- --0000000000006800b406299c5e51 Content-Type: application/octet-stream; name="search_bug.sql" Content-Disposition: attachment; filename="search_bug.sql" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_m4v4vvzx0 RFJPUCBTQ0hFTUEgSUYgRVhJU1RTIE15VGVzdEJ1Z1NjaGVtYTAyIENBU0NBREU7DQpDUkVBVEUg U0NIRU1BIE15VGVzdEJ1Z1NjaGVtYTAyOw0KDQpDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiBN eVRlc3RCdWdTY2hlbWEwMi5hZnVuY3Rpb24oIHBJbnB1dCB0ZXh0ICkgDQpyZXR1cm5zIHRleHQN CkFTICQkDQpiZWdpbg0KIHJldHVybiAnYmFzZSc7IA0KRU5EICQkIGxhbmd1YWdlIHBscGdzcWw7 DQoNCg0KDQoNCkRST1AgU0NIRU1BIElGIEVYSVNUUyBNeVRlc3RCdWdTY2hlbWEwMSBDQVNDQURF Ow0KQ1JFQVRFIFNDSEVNQSBNeVRlc3RCdWdTY2hlbWEwMTsNCg0KQ1JFQVRFIE9SIFJFUExBQ0Ug cHJvY2VkdXJlIE15VGVzdEJ1Z1NjaGVtYTAxLmJhc2VQcm9jKCkgDQpBUyAkJA0KZGVjbGFyZSAN CiB2ciB0ZXh0Ow0KYmVnaW4NCiBzZWxlY3QgTXlUZXN0QnVnU2NoZW1hLmFmdW5jdGlvbiggdS51 c2VybmFtZSApIA0KIGludG8gdnINCiBmcm9tIHRidXNlciB1OyANCkVORCAkJCBsYW5ndWFnZSBw bHBnc3FsOw0KDQoNCg0KDQoNCkRST1AgU0NIRU1BIElGIEVYSVNUUyBNeVRlc3RCdWdTY2hlbWEw MiBDQVNDQURFOw0KQ1JFQVRFIFNDSEVNQSBNeVRlc3RCdWdTY2hlbWEwMjsNCg0KQ1JFQVRFIE9S IFJFUExBQ0UgRlVOQ1RJT04gTXlUZXN0QnVnU2NoZW1hMDIuYWZ1bmN0aW9uKCBwSW5wdXQgdGV4 dCApIA0KcmV0dXJucyB0ZXh0DQpBUyAkJA0KYmVnaW4NCiByZXR1cm4gJ292ZXJ3cml0ZSc7IA0K RU5EICQkIGxhbmd1YWdlIHBscGdzcWw7DQoNCg0Kc2VsZWN0ICoNCmZyb20gcGdfcHJvYyBwDQp3 aGVyZSBub3QgZXhpc3RzICgNCiBzZWxlY3QgMSANCiBmcm9tIHBnX2NhdGFsb2cucGdfbmFtZXNw YWNlDQogd2hlcmUgb2lkID0gcC5wcm9uYW1lc3BhY2UNCik7DQo= --0000000000006800b406299c5e51 Content-Type: application/octet-stream; name="test_bug.sh" Content-Disposition: attachment; filename="test_bug.sh" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_m4v4vw031 IyEvYmluL2Jhc2gKCgpleHBvcnQgUEdQQVNTV09SRD1wYXNzd29yZApleHBvcnQgVVJMPXBvc3Rn cmVzcWw6Ly91c2VyQGhvc3Q6NTQzMi9kYgoKCmV4cG9ydCBTUUxfQ0hFQ0s9InNlbGVjdCAqIGZy b20gcGdfcHJvYyBwIHdoZXJlIG5vdCBleGlzdHMgKCAgc2VsZWN0IDEgIGZyb20gcGdfY2F0YWxv Zy5wZ19uYW1lc3BhY2Ugd2hlcmUgb2lkID0gcC5wcm9uYW1lc3BhY2UgKTsiCgojIEZ1bmN0aW9u IHRvIHJ1biB0aGUgU1FMIHNjcmlwdCBhbmQgY2hlY2sgdGhlIHJlc3VsdApydW5fdGhyZWFkKCkg ewogIHRocmVhZF9uYW1lPSQxCgogIGVjaG8gIlN0YXJ0aW5nICR0aHJlYWRfbmFtZSIKCiAgd2hp bGUgOjsgZG8KICAKICAgIGVjaG8gIlskdGhyZWFkX25hbWVdICQoZGF0ZSkiCiAgICBlY2hvICJb JHRocmVhZF9uYW1lXSBSdW5uaW5nIGluc3RhbGwgc2NyaXB0IgogICAgcHNxbCAtZiBzZWFyY2hf YnVnLnNxbCAkVVJMCgogICAgZWNobyAiWyR0aHJlYWRfbmFtZV0gQ2hlY2tpbmcgcmVzdWx0IG9m IGNoZWNrX3NxbCIKICAgIHJlc3VsdD0kKHBzcWwgLXQgLWMgIiRTUUxfQ0hFQ0siICRVUkwgfCB4 YXJncykKCiAgICBpZiBbWyAtbiAiJHJlc3VsdCIgXV07IHRoZW4KICAgICAgZWNobyAiWyR0aHJl YWRfbmFtZV0gRm91bmQgcHJvYmxlbSwgc3RvcHBpbmcgdGhyZWFkLiIKICAgICAgYnJlYWsKICAg IGZpCgogICAgZWNobyAiWyR0aHJlYWRfbmFtZV0gUmVzdGFydGluZyBsb29wLiIKICAgIHNsZWVw IDEgIyBPcHRpb25hbDogUHJldmVudCB0aWdodCBsb29waW5nCiAgICAKICAgICMgR2VuZXJhdGUg YSByYW5kb20gZGVsYXkgdXNpbmcgJFJBTkRPTSBhbmQgYXdrCiAgICByYW5kb21fZGVsYXk9JChh d2sgLXYgdnJhbmQ9IiRSQU5ET00iICdCRUdJTiB7IHByaW50ICh2cmFuZCAlIDkwMCArIDEwMCkg LyAxMDAwIH0nKQogICAgZWNobyAiWyR0aHJlYWRfbmFtZV0gU2xlZXBpbmcgZm9yICRyYW5kb21f ZGVsYXkgc2Vjb25kcy4iCiAgICBzbGVlcCAiJHJhbmRvbV9kZWxheSIKICAgIAogIGRvbmUKfQoK CiMgU3RhcnQgdGhyZWFkcyBpbiB0aGUgYmFja2dyb3VuZApydW5fdGhyZWFkICJUaHJlYWQxIiAg JgpwaWQxPSQhCgpydW5fdGhyZWFkICJUaHJlYWQyIiAgJgpwaWQyPSQhCgojIFdhaXQgZm9yIGJv dGggdGhyZWFkcyB0byBjb21wbGV0ZQp3YWl0ICRwaWQxCndhaXQgJHBpZDIKCmVjaG8gIkJvdGgg dGhyZWFkcyBoYXZlIGNvbXBsZXRlZC4iCg== --0000000000006800b406299c5e51--