pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pf@pfortin.com
To: pgsql-general@lists.postgresql.org
Subject: Re: cache lookup failed for function 0
Date: Sun, 1 Oct 2023 01:57:59 -0400
Message-ID: <20231001015759.4a31b55a@pfortin.com> (raw)
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>
	<f9aa81f1-1977-4e0f-b2f8-341b68e5c05a@aklaver.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>



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
"<local4>" 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.
>
>
>
>

Attachments:

  test_empty.backup (application/octet-stream, 1.2K) download

  test1_empty.backup (application/octet-stream, 1.2K) download

view thread (16+ messages)  latest in thread

Message-ID: <20231001015759.4a31b55a@pfortin.com>
Permalink:  ../20231001015759.4a31b55a@pfortin.com/
Also on:    postgresql.org/message-id/20231001015759.4a31b55a@pfortin.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: pf@pfortin.com
  Subject: Re: cache lookup failed for function 0
  In-Reply-To: <20231001015759.4a31b55a@pfortin.com>
  References: <20230929163739.3bea46e5.pfortin@pfortin.com> <8fd58b4c-ef57-3218-b7e3-f087e15db784@aklaver.com> <20230930100132.44da3c45@pfortin.com> <f9aa81f1-1977-4e0f-b2f8-341b68e5c05a@aklaver.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> <20231001015759.4a31b55a@pfortin.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox