Re: ERROR: cache lookup failed for type - Mailing list pgsql-sql
From | Adrian Klaver |
---|---|
Subject | Re: ERROR: cache lookup failed for type |
Date | |
Msg-id | 55D13641.20403@aklaver.com Whole thread Raw |
In response to | ERROR: cache lookup failed for type (Stuart <sfbarbee@gmail.com>) |
List | pgsql-sql |
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