Subject: Re: ERROR: cache lookup failed for type
To: Stuart <sfbarbee@gmail.com>,
 "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
References: <55D131B0.6010500@gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <55D13641.20403@aklaver.com>
Date: Sun, 16 Aug 2015 18:17:53 -0700
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <55D131B0.6010500@gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql