Thread: ERROR: cache lookup failed for type
Hello all,
I have been using a particular function for years without issue but
recently tried the Alpha releases of PostGreSQL. I loaded the database
into 9.5 Alpha1 release and did not have problems. After upgrading to
Alpha2, I started getting this error on executing the function. I didn't
reload the database this time as it should not be required.
ERROR: cache lookup failed for type 1082 CONTEXT: compilation of PL/pgSQL function "ds_stats" near line 1
I queried the data type 1082 references and found it is the "date" data
type.
# select oid,typowner,typname from pg_type where oid = 1082 ;oid | typowner | typname
------+----------+---------1082 | 10 | date
(1 row)
The function is simple with the following definition:
# CREATE FUNCTION ds_stats( date, text) RETURNS integer LANGUAGE plpgsql AS $_$
DECLARE -- inserts new statistics into doc_stats -- table calculated from the documents table -- 1st argument is
thepublished date -- 2nd argument is the source pub_date ALIAS FOR $1; pub_source ALIAS FOR $2; new_stat
documents_statistics%ROWTYPE;
BEGIN select into new_stat published, count(*), split_part(filename,'/', 5) from documents where published =
pub_dateand split_part(filename,'/', 5) = pub_source group by published, split_part(filename,'/', 5) ; IF found
then delete from documents_statistics where published = pub_date and
source = pub_source;
insert into documents_statistics ( published, articles, source ) values ( new_stat.published,
new_stat.articles,new_stat.source ); return new_stat.articles; else delete from documents_statistics where
published= pub_date and
source = pub_source; return 0; END IF;
END;
$_$;
The table documents_statistics has definition:
CREATE TABLE documents_statistics ( published date, articles bigint, source text
);
I use the function in queries like:
select ds_stats('2015-08-10'::date, 'wp_news') ;
I dropped the function and can now not add it back to the database. Also
doing a simple query on the table filtering on the published field does
not present any problems. I was going to submit this as a bug against
the new 9.5alpha2 release but thought I would run this by this group
before doing so. Any thoughts?
Thanks,
Stuart
On 08/16/2015 05:58 PM, Stuart wrote:
> Hello all,
>
> I have been using a particular function for years without issue but
> recently tried the Alpha releases of PostGreSQL. I loaded the database
> into 9.5 Alpha1 release and did not have problems. After upgrading to
> Alpha2, I started getting this error on executing the function. I didn't
> reload the database this time as it should not be required.
I do not see anything in the release notes about dump/restore, but this
is an alpha so I would at least try dumping from the Alpha 1 and
restoring to the Alpha 2. If nothing else it will provide another data
point.
>
> ERROR: cache lookup failed for type 1082
> CONTEXT: compilation of PL/pgSQL function "ds_stats" near line 1
>
> I queried the data type 1082 references and found it is the "date" data
> type.
>
> # select oid,typowner,typname from pg_type where oid = 1082 ;
> oid | typowner | typname
> ------+----------+---------
> 1082 | 10 | date
> (1 row)
>
>
> The function is simple with the following definition:
>
> # CREATE FUNCTION ds_stats( date, text) RETURNS integer
> LANGUAGE plpgsql
> AS $_$
> DECLARE
> -- inserts new statistics into doc_stats
> -- table calculated from the documents table
> -- 1st argument is the published date
> -- 2nd argument is the source
> pub_date ALIAS FOR $1;
> pub_source ALIAS FOR $2;
> new_stat documents_statistics%ROWTYPE;
> BEGIN
> select into new_stat
> published, count(*), split_part(filename,'/', 5)
> from documents
> where published = pub_date and
> split_part(filename,'/', 5) = pub_source
> group by published, split_part(filename,'/', 5) ;
> IF found then
> delete from documents_statistics where published = pub_date and
> source = pub_source;
>
> insert into documents_statistics ( published, articles, source )
> values ( new_stat.published, new_stat.articles, new_stat.source );
> return new_stat.articles;
> else
> delete from documents_statistics where published = pub_date and
> source = pub_source;
> return 0;
> END IF;
>
> END;
> $_$;
>
> The table documents_statistics has definition:
>
> CREATE TABLE documents_statistics (
> published date,
> articles bigint,
> source text
> );
>
>
> I use the function in queries like:
>
> select ds_stats('2015-08-10'::date, 'wp_news') ;
>
>
> I dropped the function and can now not add it back to the database. Also
> doing a simple query on the table filtering on the published field does
> not present any problems. I was going to submit this as a bug against
> the new 9.5alpha2 release but thought I would run this by this group
> before doing so. Any thoughts?
>
>
>
> Thanks,
>
> Stuart
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Stuart <sfbarbee@gmail.com> writes:
> I have been using a particular function for years without issue but
> recently tried the Alpha releases of PostGreSQL. I loaded the database
> into 9.5 Alpha1 release and did not have problems. After upgrading to
> Alpha2, I started getting this error on executing the function. I didn't
> reload the database this time as it should not be required.
> ERROR: cache lookup failed for type 1082
> CONTEXT: compilation of PL/pgSQL function "ds_stats" near line 1
That's odd.
> I dropped the function and can now not add it back to the database.
What happens when you try, exactly?
I assume the error was persistent across multiple sessions? Have you
changed the schema (rowtype) of table documents_statistics lately?
Does reindexing pg_type make the error go away? If so, what platform
and filesystem is this on?
regards, tom lane
Adrian, Tom, I reloaded the database and the problem doesn't happen anymore. Thanks for the suggestion. Tom - to answer your questions, On 08/17/2015 07:16 AM, Tom Lane wrote: >> I dropped the function and can now not add it back to the database. > > What happens when you try, exactly? The same error occurred > I assume the error was persistent across multiple sessions? Have you > changed the schema (rowtype) of table documents_statistics lately? No there were no changes to the table schema. All dbase objects were loaded via pg_dumpall > file.sql upgrade postgres psql template1 -f file.sql Yes the problem was persistent across multiple sessions > Does reindexing pg_type make the error go away? If so, what platform > and filesystem is this on? No, I didn't try reindexing pg_type The filesystem is XFS Thanks, Stuart
Tom, forgot to include the rest of the platform info. This on openSuSE Linux 13.2 x86_64, kernel 4.1.4 On 08/17/2015 07:52 AM, Stuart wrote: > >> Does reindexing pg_type make the error go away? If so, what platform >> and filesystem is this on? Thanks, Stuart
On 08/16/2015 08:52 PM, Stuart wrote: > Adrian, Tom, > > I reloaded the database and the problem doesn't happen anymore. > Thanks for the suggestion. > > Tom - to answer your questions, > > On 08/17/2015 07:16 AM, Tom Lane wrote: >>> I dropped the function and can now not add it back to the database. >> >> What happens when you try, exactly? > > The same error occurred > >> I assume the error was persistent across multiple sessions? Have you >> changed the schema (rowtype) of table documents_statistics lately? > > No there were no changes to the table schema. All dbase objects were > loaded via > > pg_dumpall > file.sql > > upgrade postgres > > psql template1 -f file.sql So this is what you did when you started with the Alpha 1 database, correct? When you went to Alpha 2 you just installed the new program over the existing Alpha 1, but left the data directory as is and then ran into the error, correct? You then did a dump of the Alpha 1 or other(?) existing database and then a restore into the Alpha 2(the reload above) at which point the error went away, correct? > > Yes the problem was persistent across multiple sessions > >> Does reindexing pg_type make the error go away? If so, what platform >> and filesystem is this on? > > No, I didn't try reindexing pg_type > > The filesystem is XFS > > > Thanks, > > Stuart > -- Adrian Klaver adrian.klaver@aklaver.com
On 08/17/2015 08:08 AM, Adrian Klaver wrote: > So this is what you did when you started with the Alpha 1 database, > correct? > > When you went to Alpha 2 you just installed the new program over the > existing Alpha 1, but left the data directory as is and then ran into > the error, correct? > > You then did a dump of the Alpha 1 or other(?) existing database and > then a restore into the Alpha 2(the reload above) at which point the > error went away, correct? Adrian, that is correct. More precisely, the steps taken were the following which I now see where I may have potentially introduced the error: upgrade from prostgres 9.4.4 to 9.5alpha1 pg_dumpall > file.sql pg_ctl stop upgrade postgres 9.5alpha1 rm -r /pgdir/* initdb -D /pgdir/ pg_ctl start -D /pgdir/ psql template1 -f file.sql upgrade from postgres 9.5alpha1 to 9.5alpha2 upgrade postgres 9.5alpha1 pg_ctl stop pg_ctl start -D /pgdir/ Now I see that not stopping the database prior to the upgrade may have introduced the problem eventhough I don't understand the internals. I did do another pg_ctl stop/start after upgrade just to see if that would fix but it didn't. I just did the following steps, and now no error: pg_dumpall > file.sql pg_ctl stop rm -r /pgdir/* initdb -D /pgdir/ pg_ctl start -D /pgdir/ psql template1 -f file.sql logged into db and recreated the function psql db create function ds_stats... Thanks, Stuart
On 08/16/2015 09:46 PM, Stuart wrote: > On 08/17/2015 08:08 AM, Adrian Klaver wrote: >> So this is what you did when you started with the Alpha 1 database, >> correct? >> >> When you went to Alpha 2 you just installed the new program over the >> existing Alpha 1, but left the data directory as is and then ran into >> the error, correct? >> >> You then did a dump of the Alpha 1 or other(?) existing database and >> then a restore into the Alpha 2(the reload above) at which point the >> error went away, correct? > > Adrian, that is correct. More precisely, the steps taken were the > following which I now see where I may have potentially introduced the error: > > upgrade from prostgres 9.4.4 to 9.5alpha1 > > pg_dumpall > file.sql > pg_ctl stop > upgrade postgres 9.5alpha1 > rm -r /pgdir/* > initdb -D /pgdir/ > pg_ctl start -D /pgdir/ > psql template1 -f file.sql > > > upgrade from postgres 9.5alpha1 to 9.5alpha2 > > upgrade postgres 9.5alpha1 > pg_ctl stop > pg_ctl start -D /pgdir/ > > > Now I see that not stopping the database prior to the upgrade may have > introduced the problem eventhough I don't understand the internals. I > did do another pg_ctl stop/start after upgrade just to see if that would > fix but it didn't. Yeah, I would say all bets are off when overwriting a running database. How are you doing the upgrade, from a package or source? I now the .deb packages allow for running multiple versions concurrently and I believe that yum can work that way also. If building from source you can do something like --prefix=/usr/local/pgsql94 in configure to separate versions. Then you just have to change the port in postgresql.conf to have multiple versions on a machine. Somewhat less dangerous then deleting $DATA. > > I just did the following steps, and now no error: > > pg_dumpall > file.sql > pg_ctl stop > rm -r /pgdir/* > initdb -D /pgdir/ > pg_ctl start -D /pgdir/ > psql template1 -f file.sql > > logged into db and recreated the function > > psql db > create function ds_stats... > > > > > Thanks, > > Stuart > > -- Adrian Klaver adrian.klaver@aklaver.com
<p dir="ltr">Adrian, <p dir="ltr">Doing upgrade from source. <p dir="ltr">Thanks, <p dir="ltr">Stuart<div class="gmail_quote">OnAug 17, 2015 6:11 PM, "Adrian Klaver" <<a href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>>wrote:<br type="attribution" /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 08/16/2015 09:46 PM, Stuart wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> On 08/17/201508:08 AM, Adrian Klaver wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #cccsolid;padding-left:1ex"> So this is what you did when you started with the Alpha 1 database,<br /> correct?<br /><br/> When you went to Alpha 2 you just installed the new program over the<br /> existing Alpha 1, but left the data directoryas is and then ran into<br /> the error, correct?<br /><br /> You then did a dump of the Alpha 1 or other(?) existingdatabase and<br /> then a restore into the Alpha 2(the reload above) at which point the<br /> error went away, correct?<br/></blockquote><br /> Adrian, that is correct. More precisely, the steps taken were the<br /> following whichI now see where I may have potentially introduced the error:<br /><br /> upgrade from prostgres 9.4.4 to 9.5alpha1<br/><br /> pg_dumpall > file.sql<br /> pg_ctl stop<br /> upgrade postgres 9.5alpha1<br /> rm -r /pgdir/*<br/> initdb -D /pgdir/<br /> pg_ctl start -D /pgdir/<br /> psql template1 -f file.sql<br /><br /><br /> upgradefrom postgres 9.5alpha1 to 9.5alpha2<br /><br /> upgrade postgres 9.5alpha1<br /> pg_ctl stop<br /> pg_ctl start -D/pgdir/<br /><br /><br /> Now I see that not stopping the database prior to the upgrade may have<br /> introduced the problemeventhough I don't understand the internals. I<br /> did do another pg_ctl stop/start after upgrade just to see ifthat would<br /> fix but it didn't.<br /></blockquote><br /> Yeah, I would say all bets are off when overwriting a runningdatabase.<br /><br /> How are you doing the upgrade, from a package or source?<br /><br /> I now the .deb packagesallow for running multiple versions concurrently and I believe that yum can work that way also. If building fromsource you can do something like --prefix=/usr/local/pgsql94 in configure to separate versions. Then you just have tochange the port in postgresql.conf to have multiple versions on a machine. Somewhat less dangerous then deleting $DATA.<br/><br /><br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br/> I just did the following steps, and now no error:<br /><br /> pg_dumpall > file.sql<br />pg_ctl stop<br /> rm -r /pgdir/*<br /> initdb -D /pgdir/<br /> pg_ctl start -D /pgdir/<br /> psql template1 -f file.sql<br/><br /> logged into db and recreated the function<br /><br /> psql db<br /> create function ds_stats...<br /><br/><br /><br /><br /> Thanks,<br /><br /> Stuart<br /><br /><br /></blockquote><br /><br /> -- <br /> Adrian Klaver<br/><a href="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a><br /></blockquote></div>