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 1qmpTV-00941M-Na for pgsql-general@arkaria.postgresql.org; Sun, 01 Oct 2023 05:58:42 +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 1qmpTS-009VdS-GI for pgsql-general@arkaria.postgresql.org; Sun, 01 Oct 2023 05:58:38 +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 1qmpTR-009VXw-Mn for pgsql-general@lists.postgresql.org; Sun, 01 Oct 2023 05:58:38 +0000 Received: from rs6162.luxsci.com ([162.142.76.255]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qmpTO-007Z1f-AW for pgsql-general@lists.postgresql.org; Sun, 01 Oct 2023 05:58:35 +0000 Received: from pfortin.com (h128.249.89.75.dynamic.ip.windstream.net [75.89.249.128]) (authenticated bits=0) by rs6162.luxsci.com (8.15.2/8.14.9) with ESMTPSA id 3915vxCt2047156 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT) for ; Sun, 1 Oct 2023 05:58:00 GMT Date: Sun, 1 Oct 2023 01:57:59 -0400 From: pf@pfortin.com To: pgsql-general@lists.postgresql.org Subject: Re: cache lookup failed for function 0 Message-ID: <20231001015759.4a31b55a@pfortin.com> In-Reply-To: <4455c6d7-29d5-4776-8153-8db67c05645a@aklaver.com> References: <20230929163739.3bea46e5.pfortin@pfortin.com> <8fd58b4c-ef57-3218-b7e3-f087e15db784@aklaver.com> <20230930100132.44da3c45@pfortin.com> <20230930123527.4efb81e4@pfortin.com> <1561293.1696094414@sss.pgh.pa.us> <20230930143255.0d278307@pfortin.com> <1aa75625-219e-45f1-bcfc-d2c9ee118cb6@aklaver.com> <20230930175414.7cf0dc2f@pfortin.com> <4455c6d7-29d5-4776-8153-8db67c05645a@aklaver.com> X-Mailer: Claws Mail 4.1.1git67 (GTK 3.24.38; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="MP_/bLRF0HnXfw2gYHBcfzrDMQt" X-Lux-Comment: Message 3915vxCt2047156 sent by user #22429 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pfortin.com; h=date:from:to:subject:message-id:in-reply-to:references:mime-version:content-type; s=dkim; bh=oyKyjf+glm5YsuWfsoZqtO58PXz20Ieid5ekgZAFblo=; b=XHwQpwbZtXs3ZpBcp2r/vY86zmaL6K3jNXAVpilIfcn84Dwny6xVQekjTB7CxFSKTXNoCZ/FCwlFnuubRFjt8H1S3axJX1cJ74kjjXqYECTNmnKR4FfvEP3bTNuy6FoLxEApg8pWftj9byivf5Xy5tSMUcY9gewYdeX7vcYy17w= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --MP_/bLRF0HnXfw2gYHBcfzrDMQt Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Adrian & Tom, On Sat, 30 Sep 2023 15:57:32 -0700 Adrian Klaver wrote: >On 9/30/23 14:54, pf@pfortin.com wrote: >> On Sat, 30 Sep 2023 13:40:37 -0700 Adrian Klaver wrote: >> >>> On 9/30/23 11:32, pf@pfortin.com wrote: > >> >> As I told Tom, the "test" DB has this issue; the production and test1 DBs >> are fine; I should have thought to check those first... Sorry for the >> noise. > >Still there was an issue with a database. Did you track down what was >wrong with "test"? Background: it's one of 18 databases on: /dev/nvme1n1p1 3.6T 1.3T 2.2T 38% /mnt/work Tried using a never before used table name; same error. I wondered if an old SEQUENCE might be the cause; nope. OK.. after: * back up a table * try import -- fails * drop backed-up table rinse and repeat until the test database is empty.. Still fails. Backed up the empty db (attached). I see the string "SET client_encoding = 'WIN1252';" in the dump -- some files come from a remote colleague; but this has never been an issue before... Next, I was going to connect to test1 with SQL-workbench/J when I saw this: "Cannot invoke "workbench.storage.RowData.getValue(int)" because "" is null" on the SQL Source tab. Emptied test1. Imported the table just fine. Dropped it. Dumped the db (attached) which is 2 bytes smaller. "test1" appears 4 times, so this dump should be 2 bytes larger, not smaller; it also says 'UTF8' instead of 'WIN1252'. Lots of other diffs... Hope there's something in there you guys can make sense of... Regards, Pierre >> We were working with a python script (using INSERT) someone wrote a couple >> years ago; with my version using COPY[1], imports have gone from 2+ hours >> to under a minute each for 8M and 33M row files. If the above links help >> even more that will be amazing!! THANK YOU!! > >The move to COPY will have accounted for the speed up. Using the >psycopg2 COPY methods probably won't make a material difference, I just >find them easier to use. Also in the new psycopg(3) there are more ways >to use them per: > > https://www.psycopg.org/psycopg3/docs/basic/copy.html. > > > > --MP_/bLRF0HnXfw2gYHBcfzrDMQt Content-Type: application/octet-stream; name=test_empty.backup Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename=test_empty.backup UEdETVABDgAECAEBAQAAAAAjAAAAABUAAAAAAQAAAAABAAAAAAkAAAAAewAAAAABAAAAAAQAAAB0 ZXN0AAQAAAAxNS40AAQAAAAxNS40AAYAAAAARQoAAAAAAAAAAAEAAAAwAAEAAAAwAAgAAABFTkNP RElORwAIAAAARU5DT0RJTkcAAgAAAAAhAAAAU0VUIGNsaWVudF9lbmNvZGluZyA9ICdXSU4xMjUy JzsKAQEAAAABAQAAAAEBAAAAAQEAAAABAQAAAAEBAAAAAAUAAABmYWxzZQEBAAAAAwAAAAAAAAAA AEYKAAAAAAAAAAABAAAAMAABAAAAMAAKAAAAU1REU1RSSU5HUwAKAAAAU1REU1RSSU5HUwACAAAA ACgAAABTRVQgc3RhbmRhcmRfY29uZm9ybWluZ19zdHJpbmdzID0gJ29uJzsKAQEAAAABAQAAAAEB AAAAAQEAAAABAQAAAAEBAAAAAAUAAABmYWxzZQEBAAAAAwAAAAAAAAAAAEcKAAAAAAAAAAABAAAA MAABAAAAMAAKAAAAU0VBUkNIUEFUSAAKAAAAU0VBUkNIUEFUSAACAAAAADgAAABTRUxFQ1QgcGdf Y2F0YWxvZy5zZXRfY29uZmlnKCdzZWFyY2hfcGF0aCcsICcnLCBmYWxzZSk7CgEBAAAAAQEAAAAB AQAAAAEBAAAAAQEAAAABAQAAAAAFAAAAZmFsc2UBAQAAAAMAAAAAAAAAAABICgAAAAAAAAAABAAA ADEyNjIABQAAADM0MjQzAAQAAAB0ZXN0AAgAAABEQVRBQkFTRQACAAAAAGkAAABDUkVBVEUgREFU QUJBU0UgdGVzdCBXSVRIIFRFTVBMQVRFID0gdGVtcGxhdGUwIEVOQ09ESU5HID0gJ1dJTjEyNTIn IExPQ0FMRV9QUk9WSURFUiA9IGxpYmMgTE9DQUxFID0gJ0MnOwoAFAAAAERST1AgREFUQUJBU0Ug dGVzdDsKAQEAAAABAQAAAAEBAAAAAQEAAAAACAAAAHBvc3RncmVzAAUAAABmYWxzZQEBAAAAAwAA AAAAAAAAAAUAAAAAAAAAAAAEAAAAMjYxNQAEAAAAMjIwMAAGAAAAcHVibGljAAYAAABTQ0hFTUEA AgAAAAAyAAAALS0gKm5vdCogY3JlYXRpbmcgc2NoZW1hLCBzaW5jZSBpbml0ZGIgY3JlYXRlcyBp dAoAMgAAAC0tICpub3QqIGRyb3BwaW5nIHNjaGVtYSwgc2luY2UgaW5pdGRiIGNyZWF0ZXMgaXQK AQEAAAABAQAAAAEBAAAAAQEAAAAACAAAAHBvc3RncmVzAAUAAABmYWxzZQEBAAAAAwAAAAAAAAAA AEkKAAAAAAAAAAABAAAAMAABAAAAMAANAAAAU0NIRU1BIHB1YmxpYwADAAAAQUNMAAEAAAAAUQAA AFJFVk9LRSBVU0FHRSBPTiBTQ0hFTUEgcHVibGljIEZST00gUFVCTElDOwpHUkFOVCBBTEwgT04g U0NIRU1BIHB1YmxpYyBUTyBQVUJMSUM7CgEBAAAAAQEAAAABAQAAAAEBAAAAAQEAAAAACAAAAHBv c3RncmVzAAUAAABmYWxzZQABAAAANQEBAAAAAwAAAAAAAAAA --MP_/bLRF0HnXfw2gYHBcfzrDMQt Content-Type: application/octet-stream; name=test1_empty.backup Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename=test1_empty.backup UEdETVABDgAECAEBAQAAAAAiAAAAACkAAAAAAQAAAAABAAAAAAkAAAAAewAAAAABAAAAAAUAAAB0 ZXN0MQAEAAAAMTUuNAAEAAAAMTUuNAAGAAAAAEUKAAAAAAAAAAABAAAAMAABAAAAMAAIAAAARU5D T0RJTkcACAAAAEVOQ09ESU5HAAIAAAAAHgAAAFNFVCBjbGllbnRfZW5jb2RpbmcgPSAnVVRGOCc7 CgEBAAAAAQEAAAABAQAAAAEBAAAAAQEAAAABAQAAAAAFAAAAZmFsc2UBAQAAAAMAAAAAAAAAAABG CgAAAAAAAAAAAQAAADAAAQAAADAACgAAAFNURFNUUklOR1MACgAAAFNURFNUUklOR1MAAgAAAAAo AAAAU0VUIHN0YW5kYXJkX2NvbmZvcm1pbmdfc3RyaW5ncyA9ICdvbic7CgEBAAAAAQEAAAABAQAA AAEBAAAAAQEAAAABAQAAAAAFAAAAZmFsc2UBAQAAAAMAAAAAAAAAAABHCgAAAAAAAAAAAQAAADAA AQAAADAACgAAAFNFQVJDSFBBVEgACgAAAFNFQVJDSFBBVEgAAgAAAAA4AAAAU0VMRUNUIHBnX2Nh dGFsb2cuc2V0X2NvbmZpZygnc2VhcmNoX3BhdGgnLCAnJywgZmFsc2UpOwoBAQAAAAEBAAAAAQEA AAABAQAAAAEBAAAAAQEAAAAABQAAAGZhbHNlAQEAAAADAAAAAAAAAAAASAoAAAAAAAAAAAQAAAAx MjYyAAUAAAA0OTM5MgAFAAAAdGVzdDEACAAAAERBVEFCQVNFAAIAAAAAZwAAAENSRUFURSBEQVRB QkFTRSB0ZXN0MSBXSVRIIFRFTVBMQVRFID0gdGVtcGxhdGUwIEVOQ09ESU5HID0gJ1VURjgnIExP Q0FMRV9QUk9WSURFUiA9IGxpYmMgTE9DQUxFID0gJ0MnOwoAFQAAAERST1AgREFUQUJBU0UgdGVz dDE7CgEBAAAAAQEAAAABAQAAAAEBAAAAAAgAAABwb3N0Z3JlcwAFAAAAZmFsc2UBAQAAAAMAAAAA AAAAAAAFAAAAAAAAAAAABAAAADI2MTUABAAAADIyMDAABgAAAHB1YmxpYwAGAAAAU0NIRU1BAAIA AAAAMgAAAC0tICpub3QqIGNyZWF0aW5nIHNjaGVtYSwgc2luY2UgaW5pdGRiIGNyZWF0ZXMgaXQK ADIAAAAtLSAqbm90KiBkcm9wcGluZyBzY2hlbWEsIHNpbmNlIGluaXRkYiBjcmVhdGVzIGl0CgEB AAAAAQEAAAABAQAAAAEBAAAAAAgAAABwb3N0Z3JlcwAFAAAAZmFsc2UBAQAAAAMAAAAAAAAAAABJ CgAAAAAAAAAAAQAAADAAAQAAADAADQAAAFNDSEVNQSBwdWJsaWMAAwAAAEFDTAABAAAAAFEAAABS RVZPS0UgVVNBR0UgT04gU0NIRU1BIHB1YmxpYyBGUk9NIFBVQkxJQzsKR1JBTlQgQUxMIE9OIFND SEVNQSBwdWJsaWMgVE8gUFVCTElDOwoBAQAAAAEBAAAAAQEAAAABAQAAAAEBAAAAAAgAAABwb3N0 Z3JlcwAFAAAAZmFsc2UAAQAAADUBAQAAAAMAAAAAAAAAAA== --MP_/bLRF0HnXfw2gYHBcfzrDMQt--