To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: Stuart <sfbarbee@gmail.com>
Subject: ERROR: cache lookup failed for type
Message-ID: <55D131B0.6010500@gmail.com>
Date: Mon, 17 Aug 2015 04:58:24 +0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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 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


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