Thread: cache lookup failed for function 0
Hi,
select version();
PostgreSQL 15.4 on x86_64-mageia-linux-gnu,
compiled by gcc (Mageia 12.3.0-3.mga9) 12.3.0, 64-bit
As a test, rather than use INSERT, I recently wrote a python test script
to import some 8M & 33M record files with COPY instead. These worked with
last weekend's data dump. Next, I wanted to look into importing a subset
of columns using the below logic; but I'm getting "ERROR: cache lookup
failed for function 0". Re-running the same full imports that worked
Saturday, I now get the same error.
Could something in the DB cause this "function" error?
Simplified statements; just trying to import a subset of columns:
DROP TABLE IF EXISTS t;
CREATE TABLE IF NOT EXISTS t (
f1 text, f2 text, f3 text, f4 text, f5 text );
COPY t ( -- import only a subset of columns
f1, f3, f5 ) FROM '/tmp/foo.txt'
WITH (FORMAT CSV, HEADER, DELIMITER ' ', ENCODING 'ISO-8859-1') ;
ERROR: cache lookup failed for function 0
Where: COPY t, line 1
1 statement failed.
"function"? Is this referring to an implied/internal function? Searching
has not provided any clue, yet...
There are no user functions in the database:
ostgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
It feels like something changed since the previously working script no
longer works...
Clues?
Thanks,
Pierre
On 9/29/23 1:37 PM, pf@pfortin.com wrote: > Hi, > > select version(); > PostgreSQL 15.4 on x86_64-mageia-linux-gnu, > compiled by gcc (Mageia 12.3.0-3.mga9) 12.3.0, 64-bit > > As a test, rather than use INSERT, I recently wrote a python test script > to import some 8M & 33M record files with COPY instead. These worked with > last weekend's data dump. Next, I wanted to look into importing a subset > of columns using the below logic; but I'm getting "ERROR: cache lookup > failed for function 0". Re-running the same full imports that worked > Saturday, I now get the same error. > > Could something in the DB cause this "function" error? > > > Simplified statements; just trying to import a subset of columns: > > DROP TABLE IF EXISTS t; > > CREATE TABLE IF NOT EXISTS t ( > f1 text, f2 text, f3 text, f4 text, f5 text ); > > COPY t ( -- import only a subset of columns I'm going to say it is the ( -- import only a subset of columns I suspect the -- comment is the issue. You need to show the actual Python code for a more complete answer. > f1, f3, f5 ) FROM '/tmp/foo.txt' > WITH (FORMAT CSV, HEADER, DELIMITER ' ', ENCODING 'ISO-8859-1') ; > > ERROR: cache lookup failed for function 0 > Where: COPY t, line 1 > 1 statement failed. > > "function"? Is this referring to an implied/internal function? Searching > has not provided any clue, yet... > > There are no user functions in the database: > ostgres=# \df > List of functions > Schema | Name | Result data type | Argument data types | Type > --------+------+------------------+---------------------+------ > (0 rows) > > It feels like something changed since the previously working script no > longer works... > > Clues? > > Thanks, > Pierre > > >
pf@pfortin.com writes:
> As a test, rather than use INSERT, I recently wrote a python test script
> to import some 8M & 33M record files with COPY instead. These worked with
> last weekend's data dump. Next, I wanted to look into importing a subset
> of columns using the below logic; but I'm getting "ERROR: cache lookup
> failed for function 0". Re-running the same full imports that worked
> Saturday, I now get the same error.
> Could something in the DB cause this "function" error?
"cache lookup failed" certainly smells like a server internal error,
but we'd have heard about it if the trivial case you show could reach
such a problem. I'm thinking there's things you haven't told us.
What extensions do you have installed? Maybe an event trigger?
Also, the reference to ENCODING 'ISO-8859-1' makes me wonder what
encoding conversion is being performed.
regards, tom lane
Hi Adrian,On Fri, 29 Sep 2023 14:27:48 -0700 Adrian Klaver wrote: >On 9/29/23 1:37 PM, pf@pfortin.com wrote: >> Hi, >> >> select version(); >> PostgreSQL 15.4 on x86_64-mageia-linux-gnu, >> compiled by gcc (Mageia 12.3.0-3.mga9) 12.3.0, 64-bit >> >> As a test, rather than use INSERT, I recently wrote a python test script >> to import some 8M & 33M record files with COPY instead. These worked with >> last weekend's data dump. Next, I wanted to look into importing a subset >> of columns using the below logic; but I'm getting "ERROR: cache lookup >> failed for function 0". Re-running the same full imports that worked >> Saturday, I now get the same error. >> >> Could something in the DB cause this "function" error? >> >> >> Simplified statements; just trying to import a subset of columns: >> >> DROP TABLE IF EXISTS t; >> >> CREATE TABLE IF NOT EXISTS t ( >> f1 text, f2 text, f3 text, f4 text, f5 text ); >> >> COPY t ( -- import only a subset of columns > >I'm going to say it is the > >( -- import only a subset of columns > >I suspect the -- comment is the issue. I wish it was that easy. It's not in the code; I added that as a clarification within the email only. >You need to show the actual Python code for a more complete answer. Attached... Source file (44,530 records): https://s3.amazonaws.com/dl.ncsbe.gov/data/ncvoter53.zip Attached is a 10 row (incl. header) sample file. $ dbcopy_voter2 /tmp/ncvoter53_10.txt test ncv53 <db_password> >> f1, f3, f5 ) FROM '/tmp/foo.txt' >> WITH (FORMAT CSV, HEADER, DELIMITER ' ', ENCODING 'ISO-8859-1') ; >> >> ERROR: cache lookup failed for function 0 >> Where: COPY t, line 1 >> 1 statement failed. >> >> "function"? Is this referring to an implied/internal function? Searching >> has not provided any clue, yet... >> >> There are no user functions in the database: >> ostgres=# \df >> List of functions >> Schema | Name | Result data type | Argument data types | Type >> --------+------+------------------+---------------------+------ >> (0 rows) >> >> It feels like something changed since the previously working script no >> longer works... >> >> Clues? >> >> Thanks, >> Pierre >> >> >> > >
Ignore this message; I wondered where it went to -- Looks like I accidentally hit Ctrl+Enter; just did that to another... SIGH... On Sat, 30 Sep 2023 09:30:08 -0400 pf@pfortin.com wrote: >On Fri, 29 Sep 2023 14:27:48 -0700 Adrian Klaver wrote:
On 9/30/23 07:01, pf@pfortin.com wrote: > Hi Adrian,On Fri, 29 Sep 2023 14:27:48 -0700 Adrian Klaver wrote: > >> On 9/29/23 1:37 PM, pf@pfortin.com wrote: >>> Hi, >> I'm going to say it is the >> >> ( -- import only a subset of columns >> >> I suspect the -- comment is the issue. > > I wish it was that easy. It's not in the code; I added that as a > clarification within the email only. > >> You need to show the actual Python code for a more complete answer. > > Attached... > > Source file (44,530 records): > https://s3.amazonaws.com/dl.ncsbe.gov/data/ncvoter53.zip > Attached is a 10 row (incl. header) sample file. I am not seeing the sample file. > > $ dbcopy_voter2 /tmp/ncvoter53_10.txt test ncv53 <db_password> Not sure what the above is supposed to be doing? What I was looking for is the Python code snippet where you actually run the COPY. Also per Tom's post information on whether there are extensions installed or if there is an event trigger running? > > >>> f1, f3, f5 ) FROM '/tmp/foo.txt' -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, 29 Sep 2023 18:21:02 -0400 Tom Lane wrote: >pf@pfortin.com writes: >> As a test, rather than use INSERT, I recently wrote a python test script >> to import some 8M & 33M record files with COPY instead. These worked with >> last weekend's data dump. Next, I wanted to look into importing a subset >> of columns using the below logic; but I'm getting "ERROR: cache lookup >> failed for function 0". Re-running the same full imports that worked >> Saturday, I now get the same error. > >> Could something in the DB cause this "function" error? > >"cache lookup failed" certainly smells like a server internal error, >but we'd have heard about it if the trivial case you show could reach >such a problem. I'm thinking there's things you haven't told us. >What extensions do you have installed? Maybe an event trigger? I have one production DB with fuzzystrmatch installed; but it's not in any other DB. I'm trying to import into a test DB, and not yet at the point of understanding or using triggers. This is a very simple setup, other than the volume of data. The production DB has many tables, mostly in the range of 8M-33M rows. >Also, the reference to ENCODING 'ISO-8859-1' makes me wonder what >encoding conversion is being performed. The source files are mostly UTF-8; some files have the 1/2 (0xbd) character in street addresses, hence the ISO... > regards, tom lane Thanks, Pierre
On Sat, 30 Sep 2023 08:50:45 -0700 Adrian Klaver wrote: >On 9/30/23 07:01, pf@pfortin.com wrote: >> Hi Adrian,On Fri, 29 Sep 2023 14:27:48 -0700 Adrian Klaver wrote: >> >>> On 9/29/23 1:37 PM, pf@pfortin.com wrote: >>>> Hi, > >>> I'm going to say it is the >>> >>> ( -- import only a subset of columns >>> >>> I suspect the -- comment is the issue. >> >> I wish it was that easy. It's not in the code; I added that as a >> clarification within the email only. >> >>> You need to show the actual Python code for a more complete answer. >> >> Attached... >> >> Source file (44,530 records): >> https://s3.amazonaws.com/dl.ncsbe.gov/data/ncvoter53.zip >> Attached is a 10 row (incl. header) sample file. > >I am not seeing the sample file. Sorry; I think I hit CTRL+Enter which sent that message before I was done. Then, I was outside with the HVAC guy repairing my heat pump... Python script and sample file attached... Invoke it with: "dbcopy_voter2 <source> <db> <table> <db_pw>" e.g.: >> $ dbcopy_voter2 /tmp/ncvoter53_10.txt test ncv53 <db_password> > >Not sure what the above is supposed to be doing? Importing the data via a COPY. >What I was looking for is the Python code snippet where you actually run >the COPY. Attached this time... Again, sorry about the incomplete message earlier. >Also per Tom's post information on whether there are extensions >installed or if there is an event trigger running? Replied to Tom. Thanks, Pierre
Attachment
pf@pfortin.com writes:
> Python script and sample file attached...
This runs fine for me, both in HEAD and 15.4.
(Well, it fails at the last GRANT, seemingly because you wrote
"{table}" not "{TABLE}". But the COPY goes through fine.)
Assuming that you verified that this specific test case fails
for you, we're down to just a couple of theories:
1. There's some relevant info you didn't supply yet (is it
really a completely plain-vanilla installation? Maybe some
odd choice of locale?)
2. There's something wrong with your Postgres installation.
It's hard to get much further than that with the info that
we have.
regards, tom lane
On Sat, 30 Sep 2023 13:20:14 -0400 Tom Lane wrote:
>pf@pfortin.com writes:
>> Python script and sample file attached...
>
>This runs fine for me, both in HEAD and 15.4.
>
>(Well, it fails at the last GRANT, seemingly because you wrote
>"{table}" not "{TABLE}". But the COPY goes through fine.)
Sorry, I ripped out some other test code which would have been
unnecessarily confusing; but couldn't check the remaining bits due to the
prior failure...
>Assuming that you verified that this specific test case fails
>for you, we're down to just a couple of theories:
>
>1. There's some relevant info you didn't supply yet (is it
>really a completely plain-vanilla installation? Maybe some
>odd choice of locale?)
As vanilla as it gets... Standard locale (C). The only odd thing that
happened: a system update the other day installed and started something
called tracker-miners which I was not happy with:
https://bugs.mageia.org/show_bug.cgi?id=32340
I was quite upset that the distro guys would install/run what they thought
could be a "cool" tool (as you'll see in the above link).
>2. There's something wrong with your Postgres installation.
Scary; but that's what I'm suspecting too now...
>It's hard to get much further than that with the info that
>we have.
Understood; I wasn't sure which rabbit hole to go down; but this is
starting to smell like a DB issue... I'll see about re-installing the
server and pray the DB is sane... If the above tracker-miners found my
postgres installation and added something to it; then I will be EXTREMELY
upset...
Thanks for confirming my simple SQL runs...
> regards, tom lane
Thanks Tom!
On 9/30/23 11:32, pf@pfortin.com wrote: > On Sat, 30 Sep 2023 13:20:14 -0400 Tom Lane wrote: > > As vanilla as it gets... Standard locale (C). The only odd thing that > happened: a system update the other day installed and started something > called tracker-miners which I was not happy with: > https://bugs.mageia.org/show_bug.cgi?id=32340 > I was quite upset that the distro guys would install/run what they thought > could be a "cool" tool (as you'll see in the above link). > >> 2. There's something wrong with your Postgres installation. > > Scary; but that's what I'm suspecting too now... The script ran on my installation also. It would seem it is something on your end. Has there been any issues with your machine and/or database e.g. crashes or other odd behavior(not counting the tracker-miners update)? FYI, since you are using psycopg2 you might want to use the built in tools for: a) Dynamic SQL https://www.psycopg.org/docs/sql.html b) COPY https://www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from > > Understood; I wasn't sure which rabbit hole to go down; but this is > starting to smell like a DB issue... I'll see about re-installing the > server and pray the DB is sane... If the above tracker-miners found my > postgres installation and added something to it; then I will be EXTREMELY > upset... From here: https://github.com/GNOME/tracker-miners it looks like the software use SQLite for data storage. > > Thanks for confirming my simple SQL runs... > >> regards, tom lane > > Thanks Tom! > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 30 Sep 2023 13:40:37 -0700 Adrian Klaver wrote: >On 9/30/23 11:32, pf@pfortin.com wrote: >> On Sat, 30 Sep 2023 13:20:14 -0400 Tom Lane wrote: >> > >> As vanilla as it gets... Standard locale (C). The only odd thing that >> happened: a system update the other day installed and started something >> called tracker-miners which I was not happy with: >> https://bugs.mageia.org/show_bug.cgi?id=32340 >> I was quite upset that the distro guys would install/run what they thought >> could be a "cool" tool (as you'll see in the above link). >> >>> 2. There's something wrong with your Postgres installation. >> >> Scary; but that's what I'm suspecting too now... > >The script ran on my installation also. It would seem it is something on >your end. Has there been any issues with your machine and/or database >e.g. crashes or other odd behavior(not counting the tracker-miners update)? 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. >FYI, since you are using psycopg2 you might want to use the built in >tools for: > > a) Dynamic SQL > https://www.psycopg.org/docs/sql.html > > b) COPY > https://www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from Thanks for these links... They look very interesting. Someone on this list suggested Practical SQL by Anthony DeBarros which we're working through... He should see sales from all over the US now.... :) 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!! [1] the script I sent is just a test script I threw together to check out COPY features -- only started; but well impressed. >> Understood; I wasn't sure which rabbit hole to go down; but this is >> starting to smell like a DB issue... I'll see about re-installing the >> server and pray the DB is sane... If the above tracker-miners found my >> postgres installation and added something to it; then I will be EXTREMELY >> upset... > > From here: > >https://github.com/GNOME/tracker-miners > >it looks like the software use SQLite for data storage. > >> >> Thanks for confirming my simple SQL runs... >> >>> regards, tom lane >> >> Thanks Tom! >> >> >
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"?
> 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.
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 9/30/23 14:54, 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"?
Yeah, it'd be interesting to try to figure out what's wrong.
One idea is to pg_dump both test and test1 and compare the dump
scripts, assuming you expect identical contents in both.
(If you expect not-quite-identical contents, I think there's
a DB comparison tool that's been recommended around here.)
regards, tom lane
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. > > > >
Attachment
pf@pfortin.com writes:
> 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...
Hah! If I create the test database with encoding WIN1252 then your
test fails as described. It *should* be complaining that it doesn't
know how to convert from ISO-8859-1 to WIN1252, but BeginCopyFrom
is neglecting to check for failure of FindDefaultConversionProc.
Will fix that, thanks for the report.
In the meantime, if use of WIN1252 was intentional then you'll need
to find another way to transcode the data that was in that encoding.
Probably, running your database in UTF8 is the best choice -- we
can convert most anything to that.
regards, tom lane