agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Stuart <sfbarbee@gmail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: ERROR: cache lookup failed for type
Date: Mon, 17 Aug 2015 04:58:24 +0400
Message-ID: <55D131B0.6010500@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (9+ messages)  latest in thread

Message-ID: <55D131B0.6010500@gmail.com>
Permalink:  ../55D131B0.6010500@gmail.com/
Also on:    postgresql.org/message-id/55D131B0.6010500@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sfbarbee@gmail.com
  Subject: Re: ERROR: cache lookup failed for type
  In-Reply-To: <55D131B0.6010500@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox