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 1tOJjE-005Xv8-0a for pgsql-general@arkaria.postgresql.org; Thu, 19 Dec 2024 16:50:24 +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 1tOJjD-00FF8V-9o for pgsql-general@arkaria.postgresql.org; Thu, 19 Dec 2024 16:50:23 +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 1tOJjC-00FF8M-Qp for pgsql-general@lists.postgresql.org; Thu, 19 Dec 2024 16:50:22 +0000 Received: from fout-b3-smtp.messagingengine.com ([202.12.124.146]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tOJj9-000PZn-As for pgsql-general@postgresql.org; Thu, 19 Dec 2024 16:50:21 +0000 Received: from phl-compute-12.internal (phl-compute-12.phl.internal [10.202.2.52]) by mailfout.stl.internal (Postfix) with ESMTP id 9244F1140149; Thu, 19 Dec 2024 11:50:18 -0500 (EST) Received: from phl-mailfrontend-02 ([10.202.2.163]) by phl-compute-12.internal (MEProxy); Thu, 19 Dec 2024 11:50:18 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm3; t=1734627018; x=1734713418; bh=dOXaqOmASc WscMRJwW+ea1P8fSfoBO9mOAfu5fXqj/g=; b=GM+DsvVQKXSw8t0XH2W418Bl1N lArTaq2rdmwcqtxUEQjvsEz32sqgAKIbN2UiGf/f8sfm/C7sUVtNPli+Dgyt3Yk3 dr7ocwMJwYjFS7THFNBxpk+n7VKNjrOuUGn7CQMEpgNZbY28EUE3H0HZMQLccwLY dg2cQuMcmTWvtHTYNLZulelSGMda/P8Fts64LbDqLkq2ukXM/meOK6G+0ktCYuIM 38J34xV+1NX/By5kGl1IwAT0EmaDpGlEOCdcyypnZPUMk/2PDI/W1Su4xbW6nVTj 7wxnSXfxHCkwhsQ7TrgYcXGiqza7+8cdRntu6l6fpgGvq7ofNxZ45hu6vjXw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1734627018; x=1734713418; bh=dOXaqOmAScWscMRJwW+ea1P8fSfoBO9mOAf u5fXqj/g=; b=BIp7r3x3icDLuwH7k1YIUKNjd2CQ3yz4vp312vo4yOMAXkyHAdw HS6V7nsRtYowXk3z2F7vEqi35iTODogD9Y+KC59ZJCLWLNv/jAqvv+Pv80Lr5/+Q airakHwNqnS2B8EUOLBFXXiIsvaHqy9EmLKPUHxdw0FyxWaoklmhuX09L12vEsIF DHE8nquAckCDImT6sFnRQTHMUZCy/QfujCK8nPfTmIm1iRqajlL2MxnFXzb7AKXb i+IriyTrWJN1eMmrNeWZOoQ+VgKZpgVpWyELTsyrwg+bgUcHwPCzIaTTug+FSQ+R aLBOEWvpbSSkfw/z44K86ZpJz6pmrmpcq5A== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefuddruddttddgleduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdggtfgfnhhsuhgsshgtrhhisggvpdfu rfetoffkrfgpnffqhgenuceurghilhhouhhtmecufedttdenucenucfjughrpegtkfffgg gfuffvfhevhfgjsehmtderredtvdejnecuhfhrohhmpeetughrihgrnhcumfhlrghvvghr uceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomheqnecuggftrfgrth htvghrnhephfdtieffgfetieffuddvhefhgefgjeegkefgueetieffvdefvdejtdfggeef gfejnecuffhomhgrihhnpehsqhhlrdhtohdpphhoshhtghhrvghsqhhlrdhorhhgnecuve hluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghn rdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomhdpnhgspghrtghpthhtohepvddpmhhoug gvpehsmhhtphhouhhtpdhrtghpthhtoheprghrohhnshejsehgmhgrihhlrdgtohhmpdhr tghpthhtohepphhgshhqlhdqghgvnhgvrhgrlhesphhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 19 Dec 2024 11:50:17 -0500 (EST) Content-Type: multipart/mixed; boundary="------------e9ayUr4InJBYUurHHYBVWotV" Message-ID: <95d1dc90-42f0-4026-aec1-8e21c5f2a205@aklaver.com> Date: Thu, 19 Dec 2024 08:50:16 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Issue with pg_dump due to Schema OID Error To: Renzo Dani References: Content-Language: en-US Cc: pgsql-general From: Adrian Klaver In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------e9ayUr4InJBYUurHHYBVWotV Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. > > BR > Renzo > > On Thu, Dec 19, 2024 at 5:28 PM Adrian Klaver > wrote: > > On 12/19/24 08:21, Renzo Dani wrote: > > Reply to list also. > Ccing list. > > > HI Adrian, > > you are right, there is a typo, the correct would be to have: > >   MyTestBugSchema2.afunction( u.username ) > > > > In any case the problem appears in my tests also with that script. > > To be clear the test script did not use MyTestBugSchema2.afunction( > u.username ) but instead MyTestBugSchema.afunction( u.username ). > > If that is the case where did MyTestBugSchema come from? > > > I think the execution is not really important, I suppose what > create the > > problem is the drop cascade of the schema. > > It is important if someone wants to replicate the test case. > > > > > BR > > Renzo > > > > On Thu, Dec 19, 2024 at 5:15 PM Adrian Klaver > > > >> wrote: > > > >     On 12/19/24 01:43, arons wrote: > >      > I forgot to attache the script. > > > >     In MyTestBugSchema01.baseProc() you meant to have: > > > >     select MyTestBugSchema.afunction( u.username ) > > > >     instead of > > > >     select MyTestBugSchema2.afunction( u.username ) > > > >     In other words there is a predefined MyTestBugSchema? > > > >      > > >      > On Thu, Dec 19, 2024 at 10:41 AM 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 = 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 > >      > > > > >     -- > >     Adrian Klaver > > adrian.klaver@aklaver.com > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com --------------e9ayUr4InJBYUurHHYBVWotV Content-Type: application/sql; name="search_bug.sql" Content-Disposition: attachment; filename="search_bug.sql" Content-Transfer-Encoding: base64 RFJPUCBTQ0hFTUEgSUYgRVhJU1RTIE15VGVzdEJ1Z1NjaGVtYTAyIENBU0NBREU7DQpDUkVB VEUgU0NIRU1BIE15VGVzdEJ1Z1NjaGVtYTAyOw0KDQpDUkVBVEUgT1IgUkVQTEFDRSBGVU5D VElPTiBNeVRlc3RCdWdTY2hlbWEwMi5hZnVuY3Rpb24oIHBJbnB1dCB0ZXh0ICkgDQpyZXR1 cm5zIHRleHQNCkFTICQkDQpiZWdpbg0KIHJldHVybiAnYmFzZSc7IA0KRU5EICQkIGxhbmd1 YWdlIHBscGdzcWw7DQoNCg0KDQoNCkRST1AgU0NIRU1BIElGIEVYSVNUUyBNeVRlc3RCdWdT Y2hlbWEwMSBDQVNDQURFOw0KQ1JFQVRFIFNDSEVNQSBNeVRlc3RCdWdTY2hlbWEwMTsNCg0K Q1JFQVRFIE9SIFJFUExBQ0UgcHJvY2VkdXJlIE15VGVzdEJ1Z1NjaGVtYTAxLmJhc2VQcm9j KCkgDQpBUyAkJA0KZGVjbGFyZSANCiB2ciB0ZXh0Ow0KYmVnaW4NCiB2ciA6PSBNeVRlc3RC dWdTY2hlbWEwMi5hZnVuY3Rpb24oICd0ZXN0JyApICA7IA0KRU5EICQkIGxhbmd1YWdlIHBs cGdzcWw7DQoNCg0KDQoNCg0KRFJPUCBTQ0hFTUEgSUYgRVhJU1RTIE15VGVzdEJ1Z1NjaGVt YTAyIENBU0NBREU7DQpDUkVBVEUgU0NIRU1BIE15VGVzdEJ1Z1NjaGVtYTAyOw0KDQpDUkVB VEUgT1IgUkVQTEFDRSBGVU5DVElPTiBNeVRlc3RCdWdTY2hlbWEwMi5hZnVuY3Rpb24oIHBJ bnB1dCB0ZXh0ICkgDQpyZXR1cm5zIHRleHQNCkFTICQkDQpiZWdpbg0KIHJldHVybiAnb3Zl cndyaXRlJzsgDQpFTkQgJCQgbGFuZ3VhZ2UgcGxwZ3NxbDsNCg0KDQpzZWxlY3QgKg0KZnJv bSBwZ19wcm9jIHANCndoZXJlIG5vdCBleGlzdHMgKA0KIHNlbGVjdCAxIA0KIGZyb20gcGdf Y2F0YWxvZy5wZ19uYW1lc3BhY2UNCiB3aGVyZSBvaWQgPSBwLnByb25hbWVzcGFjZQ0KKTsN Cg== --------------e9ayUr4InJBYUurHHYBVWotV--