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 1tOJet-005XQw-Fy for pgsql-general@arkaria.postgresql.org; Thu, 19 Dec 2024 16:45:56 +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 1tOJds-00F8hz-QR for pgsql-general@arkaria.postgresql.org; Thu, 19 Dec 2024 16:44:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tODBB-00AOPJ-MT for pgsql-general@lists.postgresql.org; Thu, 19 Dec 2024 09:50:49 +0000 Received: from mail-ed1-x52a.google.com ([2a00:1450:4864:20::52a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tODB7-000YsZ-Tr for pgsql-general@postgresql.org; Thu, 19 Dec 2024 09:50:48 +0000 Received: by mail-ed1-x52a.google.com with SMTP id 4fb4d7f45d1cf-5d4e2aa7ea9so938889a12.2 for ; Thu, 19 Dec 2024 01:50:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734601845; x=1735206645; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=B9zzaLAlitYn43lB5/V5OIeHHriNBTuHEA9MWYdLV00=; b=WZ84EziSLzxrSkwFH02vpcOGdwRXgMmRHlasUhkfuCeQHs3AP9RDcA3aodhv9elUF+ m5fSl7r5DOqbfLCWRTA9EKtD0E5FlBdkNNrUSKzmLGDCSJFtTun/PlRqlfQ9rtMbcMmA VI0DGClUSx9pPsOrmgVzwYeWgQKSFlfJGFiKd/SFt2aoIc6y45AOKQNUdYJAMMuqTt6d hWhoWxD2FMv0HnV79ejOizkh5+Lr2gVByJChKSXPOLttUl12ZfmmQHrFheZ1H8qT8tfN U9yQq0Rm4CBHYM2Jf5JP3trbRdW8VQ+B++vSFKVKLBNGZQDysuXCeSTuOKCl1U7rBt9P 4bVQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734601845; x=1735206645; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=B9zzaLAlitYn43lB5/V5OIeHHriNBTuHEA9MWYdLV00=; b=OJG82Zjxq8hYgP9qAP4wO4hMNcOo0d09Y7akSi3z+D8qilAZfXPFn7uu156FbuvNdW i3Lv+A4jBhYVVPZNNMSiiVU5WmNOrjLETsFHHI6qjJIIsmaY3izpK0s8QtbFO8ix04hM OFggP9AWcJAaD0cLnH033iKPutyR90gcZC9Exo3BDLeLqg6mqgvnXNev7scDHQViWc5F CZHPBYAqCicsRsC6UUQfQx+qgZmSXkSFyB5oCde+MtaFKfCSQ88TNAh5Y5jYBxDrNXvv 4GgFh9o0zEpFWGx8bbR/8OG71mZvKpWk7VmcF5/jReSCFkfaqcIgveHRNTdTZXIEKb2f YZrA== X-Gm-Message-State: AOJu0Yz65BIu3QEV2rIAEJQTRjYdq09n+AKcdSeaMExFPNI552ca6oaN Nso13rcohC9y0/NW1IZNFYFJtgUPbC8PKsV9Y7ERO/rbA499cLPUN7lbOpgtZ/jo0ht04Ic8PZE 98b2GK4nPc/96u9ToOCgsZLj3zugw8BIo X-Gm-Gg: ASbGncv0v3WECMmTXKf5/fsnOUcyM0RN70Nn+LZbNNlJTQGPdkVGd9UcFDQLHInZZxo +YCYqsFHkTs8b4SFhyWbeezU7dQJI0RXqWuO0 X-Google-Smtp-Source: AGHT+IF93zZcpaxqBMJIG+tT9Mes8YunuBceaggcuROSJzBfKJU+yJ7qsTCvQ2r17szzCmP31dciJOOOhHUePbU/VQQ= X-Received: by 2002:a05:6402:4584:b0:5d3:cd5b:64a9 with SMTP id 4fb4d7f45d1cf-5d7ee404610mr5533591a12.34.1734601844682; Thu, 19 Dec 2024 01:50:44 -0800 (PST) MIME-Version: 1.0 From: Renzo Dani Date: Thu, 19 Dec 2024 10:50:09 +0100 Message-ID: Subject: Issue with pg_dump due to Schema OID Error To: pgsql-general@postgresql.org Content-Type: multipart/mixed; boundary="000000000000c7b76d06299c76ba" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c7b76d06299c76ba Content-Type: multipart/alternative; boundary="000000000000c7b76b06299c76b8" --000000000000c7b76b06299c76b8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 normal conditions. While I=E2=80=99m unsure exactly when this issue originated in our environm= ent, 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 beginn= ing 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-para= dox.net https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF897487= 04F5%40bandwidth.com Let me know if you need additional information. Best regards Renzo --000000000000c7b76b06299c76b8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi,


Recently, I encountered a probl= em 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 tab= les

pg_dump: reading user-defined functions

pg_dump: error: schema with OID 4= 1960442 does not exist


To investigate the issue, I ran the following query= :

<= br>

SELECT * FROM pg_proc WHERE pronamespace =3D 41960442;


The result:<= /p>


oid;proname;pronamespace;proowner= ;prol= ang;p= rocost;provariadic;prosupport;prokind;prosecdef;proleakproof;proisstrict;proretset;provolatile;proparallel;pronargs= ;pronargdefa= ults;= 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};;;


<= span style=3D"font-family:monospace">I resolved the issue by removing the p= roblematic record (admin privileges required):


DELETE FROM pg_proc WHERE oid =3D 419= 66618;


This situation seems inconsistent and likely should not occur under= normal conditions.


While I=E2=80=99m unsure exactly when this issue origin= ated in our environment, I was able to reproduce it by performing concurren= t modifications on the schema.


To demonstrate, I wrote a bash script (test_b= ug.sh) that starts two threads running in parallel.

Each thread drops the sch= ema with CASCADE and recreates it using the SQL script search_bug.sql.


To us= e the script, you=E2=80=99ll 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 i= ssue:


https://www.postgresql.org/message-id/flat/201= 10209003823.GA93840%40mr-paradox.net

= = https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF897487= 04F5%40bandwidth.com


Let me know if you need additional information.


Best r= egards


Renzo

=
--000000000000c7b76b06299c76b8-- --000000000000c7b76d06299c76ba 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_m4v52vgb0 RFJPUCBTQ0hFTUEgSUYgRVhJU1RTIE15VGVzdEJ1Z1NjaGVtYTAyIENBU0NBREU7DQpDUkVBVEUg U0NIRU1BIE15VGVzdEJ1Z1NjaGVtYTAyOw0KDQpDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiBN eVRlc3RCdWdTY2hlbWEwMi5hZnVuY3Rpb24oIHBJbnB1dCB0ZXh0ICkgDQpyZXR1cm5zIHRleHQN CkFTICQkDQpiZWdpbg0KIHJldHVybiAnYmFzZSc7IA0KRU5EICQkIGxhbmd1YWdlIHBscGdzcWw7 DQoNCg0KDQoNCkRST1AgU0NIRU1BIElGIEVYSVNUUyBNeVRlc3RCdWdTY2hlbWEwMSBDQVNDQURF Ow0KQ1JFQVRFIFNDSEVNQSBNeVRlc3RCdWdTY2hlbWEwMTsNCg0KQ1JFQVRFIE9SIFJFUExBQ0Ug cHJvY2VkdXJlIE15VGVzdEJ1Z1NjaGVtYTAxLmJhc2VQcm9jKCkgDQpBUyAkJA0KZGVjbGFyZSAN CiB2ciB0ZXh0Ow0KYmVnaW4NCiBzZWxlY3QgTXlUZXN0QnVnU2NoZW1hLmFmdW5jdGlvbiggdS51 c2VybmFtZSApIA0KIGludG8gdnINCiBmcm9tIHRidXNlciB1OyANCkVORCAkJCBsYW5ndWFnZSBw bHBnc3FsOw0KDQoNCg0KDQoNCkRST1AgU0NIRU1BIElGIEVYSVNUUyBNeVRlc3RCdWdTY2hlbWEw MiBDQVNDQURFOw0KQ1JFQVRFIFNDSEVNQSBNeVRlc3RCdWdTY2hlbWEwMjsNCg0KQ1JFQVRFIE9S IFJFUExBQ0UgRlVOQ1RJT04gTXlUZXN0QnVnU2NoZW1hMDIuYWZ1bmN0aW9uKCBwSW5wdXQgdGV4 dCApIA0KcmV0dXJucyB0ZXh0DQpBUyAkJA0KYmVnaW4NCiByZXR1cm4gJ292ZXJ3cml0ZSc7IA0K RU5EICQkIGxhbmd1YWdlIHBscGdzcWw7DQoNCg0Kc2VsZWN0ICoNCmZyb20gcGdfcHJvYyBwDQp3 aGVyZSBub3QgZXhpc3RzICgNCiBzZWxlY3QgMSANCiBmcm9tIHBnX2NhdGFsb2cucGdfbmFtZXNw YWNlDQogd2hlcmUgb2lkID0gcC5wcm9uYW1lc3BhY2UNCik7DQo= --000000000000c7b76d06299c76ba 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_m4v52vgl1 IyEvYmluL2Jhc2gKCgpleHBvcnQgUEdQQVNTV09SRD1wYXNzd29yZApleHBvcnQgVVJMPXBvc3Rn cmVzcWw6Ly91c2VyQGhvc3Q6NTQzMi9kYgoKCmV4cG9ydCBTUUxfQ0hFQ0s9InNlbGVjdCAqIGZy b20gcGdfcHJvYyBwIHdoZXJlIG5vdCBleGlzdHMgKCAgc2VsZWN0IDEgIGZyb20gcGdfY2F0YWxv Zy5wZ19uYW1lc3BhY2Ugd2hlcmUgb2lkID0gcC5wcm9uYW1lc3BhY2UgKTsiCgojIEZ1bmN0aW9u IHRvIHJ1biB0aGUgU1FMIHNjcmlwdCBhbmQgY2hlY2sgdGhlIHJlc3VsdApydW5fdGhyZWFkKCkg ewogIHRocmVhZF9uYW1lPSQxCgogIGVjaG8gIlN0YXJ0aW5nICR0aHJlYWRfbmFtZSIKCiAgd2hp bGUgOjsgZG8KICAKICAgIGVjaG8gIlskdGhyZWFkX25hbWVdICQoZGF0ZSkiCiAgICBlY2hvICJb JHRocmVhZF9uYW1lXSBSdW5uaW5nIGluc3RhbGwgc2NyaXB0IgogICAgcHNxbCAtZiBzZWFyY2hf YnVnLnNxbCAkVVJMCgogICAgZWNobyAiWyR0aHJlYWRfbmFtZV0gQ2hlY2tpbmcgcmVzdWx0IG9m IGNoZWNrX3NxbCIKICAgIHJlc3VsdD0kKHBzcWwgLXQgLWMgIiRTUUxfQ0hFQ0siICRVUkwgfCB4 YXJncykKCiAgICBpZiBbWyAtbiAiJHJlc3VsdCIgXV07IHRoZW4KICAgICAgZWNobyAiWyR0aHJl YWRfbmFtZV0gRm91bmQgcHJvYmxlbSwgc3RvcHBpbmcgdGhyZWFkLiIKICAgICAgYnJlYWsKICAg IGZpCgogICAgZWNobyAiWyR0aHJlYWRfbmFtZV0gUmVzdGFydGluZyBsb29wLiIKICAgIHNsZWVw IDEgIyBPcHRpb25hbDogUHJldmVudCB0aWdodCBsb29waW5nCiAgICAKICAgICMgR2VuZXJhdGUg YSByYW5kb20gZGVsYXkgdXNpbmcgJFJBTkRPTSBhbmQgYXdrCiAgICByYW5kb21fZGVsYXk9JChh d2sgLXYgdnJhbmQ9IiRSQU5ET00iICdCRUdJTiB7IHByaW50ICh2cmFuZCAlIDkwMCArIDEwMCkg LyAxMDAwIH0nKQogICAgZWNobyAiWyR0aHJlYWRfbmFtZV0gU2xlZXBpbmcgZm9yICRyYW5kb21f ZGVsYXkgc2Vjb25kcy4iCiAgICBzbGVlcCAiJHJhbmRvbV9kZWxheSIKICAgIAogIGRvbmUKfQoK CiMgU3RhcnQgdGhyZWFkcyBpbiB0aGUgYmFja2dyb3VuZApydW5fdGhyZWFkICJUaHJlYWQxIiAg JgpwaWQxPSQhCgpydW5fdGhyZWFkICJUaHJlYWQyIiAgJgpwaWQyPSQhCgojIFdhaXQgZm9yIGJv dGggdGhyZWFkcyB0byBjb21wbGV0ZQp3YWl0ICRwaWQxCndhaXQgJHBpZDIKCmVjaG8gIkJvdGgg dGhyZWFkcyBoYXZlIGNvbXBsZXRlZC4iCg== --000000000000c7b76d06299c76ba--