Re: ERROR: cache lookup failed for type

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(dot)klaver(at)aklaver(dot)com