agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedERROR: cache lookup failed for type
9+ messages / 3 participants
[nested] [flat]
* ERROR: cache lookup failed for type
@ 2015-08-17 00:58 Stuart <sfbarbee@gmail.com>
2015-08-17 01:17 ` Re: ERROR: cache lookup failed for type Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 03:16 ` Re: ERROR: cache lookup failed for type Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Stuart @ 2015-08-17 00:58 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: cache lookup failed for type
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
@ 2015-08-17 01:17 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2015-08-17 01:17 UTC (permalink / raw)
To: Stuart <sfbarbee@gmail.com>; 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: cache lookup failed for type
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
@ 2015-08-17 03:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-08-17 03:52 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2015-08-17 03:16 UTC (permalink / raw)
To: Stuart <sfbarbee@gmail.com>; +Cc: pgsql-sql
Stuart <sfbarbee@gmail.com> writes:
> 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
That's odd.
> I dropped the function and can now not add it back to the database.
What happens when you try, exactly?
I assume the error was persistent across multiple sessions? Have you
changed the schema (rowtype) of table documents_statistics lately?
Does reindexing pg_type make the error go away? If so, what platform
and filesystem is this on?
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: cache lookup failed for type
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 03:16 ` Re: ERROR: cache lookup failed for type Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-08-17 03:52 ` Stuart <sfbarbee@gmail.com>
2015-08-17 03:56 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 04:08 ` Re: ERROR: cache lookup failed for type Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 9+ messages in thread
From: Stuart @ 2015-08-17 03:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; adrian.klaver@aklaver.com; +Cc: pgsql-sql
Adrian, Tom,
I reloaded the database and the problem doesn't happen anymore.
Thanks for the suggestion.
Tom - to answer your questions,
On 08/17/2015 07:16 AM, Tom Lane wrote:
>> I dropped the function and can now not add it back to the database.
>
> What happens when you try, exactly?
The same error occurred
> I assume the error was persistent across multiple sessions? Have you
> changed the schema (rowtype) of table documents_statistics lately?
No there were no changes to the table schema. All dbase objects were
loaded via
pg_dumpall > file.sql
upgrade postgres
psql template1 -f file.sql
Yes the problem was persistent across multiple sessions
> Does reindexing pg_type make the error go away? If so, what platform
> and filesystem is this on?
No, I didn't try reindexing pg_type
The filesystem is XFS
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: cache lookup failed for type
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 03:16 ` Re: ERROR: cache lookup failed for type Tom Lane <tgl@sss.pgh.pa.us>
2015-08-17 03:52 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
@ 2015-08-17 03:56 ` Stuart <sfbarbee@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Stuart @ 2015-08-17 03:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom, forgot to include the rest of the platform info.
This on openSuSE Linux 13.2 x86_64, kernel 4.1.4
On 08/17/2015 07:52 AM, Stuart wrote:
>
>> Does reindexing pg_type make the error go away? If so, what platform
>> and filesystem is this on?
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: cache lookup failed for type
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 03:16 ` Re: ERROR: cache lookup failed for type Tom Lane <tgl@sss.pgh.pa.us>
2015-08-17 03:52 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
@ 2015-08-17 04:08 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 04:46 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2015-08-17 04:08 UTC (permalink / raw)
To: Stuart <sfbarbee@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 08/16/2015 08:52 PM, Stuart wrote:
> Adrian, Tom,
>
> I reloaded the database and the problem doesn't happen anymore.
> Thanks for the suggestion.
>
> Tom - to answer your questions,
>
> On 08/17/2015 07:16 AM, Tom Lane wrote:
>>> I dropped the function and can now not add it back to the database.
>>
>> What happens when you try, exactly?
>
> The same error occurred
>
>> I assume the error was persistent across multiple sessions? Have you
>> changed the schema (rowtype) of table documents_statistics lately?
>
> No there were no changes to the table schema. All dbase objects were
> loaded via
>
> pg_dumpall > file.sql
>
> upgrade postgres
>
> psql template1 -f file.sql
So this is what you did when you started with the Alpha 1 database, correct?
When you went to Alpha 2 you just installed the new program over the
existing Alpha 1, but left the data directory as is and then ran into
the error, correct?
You then did a dump of the Alpha 1 or other(?) existing database and
then a restore into the Alpha 2(the reload above) at which point the
error went away, correct?
>
> Yes the problem was persistent across multiple sessions
>
>> Does reindexing pg_type make the error go away? If so, what platform
>> and filesystem is this on?
>
> No, I didn't try reindexing pg_type
>
> The filesystem is XFS
>
>
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: cache lookup failed for type
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 03:16 ` Re: ERROR: cache lookup failed for type Tom Lane <tgl@sss.pgh.pa.us>
2015-08-17 03:52 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 04:08 ` Re: ERROR: cache lookup failed for type Adrian Klaver <adrian.klaver@aklaver.com>
@ 2015-08-17 04:46 ` Stuart <sfbarbee@gmail.com>
2015-08-17 14:11 ` Re: ERROR: cache lookup failed for type Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 9+ messages in thread
From: Stuart @ 2015-08-17 04:46 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 08/17/2015 08:08 AM, Adrian Klaver wrote:
> So this is what you did when you started with the Alpha 1 database,
> correct?
>
> When you went to Alpha 2 you just installed the new program over the
> existing Alpha 1, but left the data directory as is and then ran into
> the error, correct?
>
> You then did a dump of the Alpha 1 or other(?) existing database and
> then a restore into the Alpha 2(the reload above) at which point the
> error went away, correct?
Adrian, that is correct. More precisely, the steps taken were the
following which I now see where I may have potentially introduced the error:
upgrade from prostgres 9.4.4 to 9.5alpha1
pg_dumpall > file.sql
pg_ctl stop
upgrade postgres 9.5alpha1
rm -r /pgdir/*
initdb -D /pgdir/
pg_ctl start -D /pgdir/
psql template1 -f file.sql
upgrade from postgres 9.5alpha1 to 9.5alpha2
upgrade postgres 9.5alpha1
pg_ctl stop
pg_ctl start -D /pgdir/
Now I see that not stopping the database prior to the upgrade may have
introduced the problem eventhough I don't understand the internals. I
did do another pg_ctl stop/start after upgrade just to see if that would
fix but it didn't.
I just did the following steps, and now no error:
pg_dumpall > file.sql
pg_ctl stop
rm -r /pgdir/*
initdb -D /pgdir/
pg_ctl start -D /pgdir/
psql template1 -f file.sql
logged into db and recreated the function
psql db
create function ds_stats...
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: cache lookup failed for type
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 03:16 ` Re: ERROR: cache lookup failed for type Tom Lane <tgl@sss.pgh.pa.us>
2015-08-17 03:52 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 04:08 ` Re: ERROR: cache lookup failed for type Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 04:46 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
@ 2015-08-17 14:11 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 14:13 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2015-08-17 14:11 UTC (permalink / raw)
To: Stuart <sfbarbee@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 08/16/2015 09:46 PM, Stuart wrote:
> On 08/17/2015 08:08 AM, Adrian Klaver wrote:
>> So this is what you did when you started with the Alpha 1 database,
>> correct?
>>
>> When you went to Alpha 2 you just installed the new program over the
>> existing Alpha 1, but left the data directory as is and then ran into
>> the error, correct?
>>
>> You then did a dump of the Alpha 1 or other(?) existing database and
>> then a restore into the Alpha 2(the reload above) at which point the
>> error went away, correct?
>
> Adrian, that is correct. More precisely, the steps taken were the
> following which I now see where I may have potentially introduced the error:
>
> upgrade from prostgres 9.4.4 to 9.5alpha1
>
> pg_dumpall > file.sql
> pg_ctl stop
> upgrade postgres 9.5alpha1
> rm -r /pgdir/*
> initdb -D /pgdir/
> pg_ctl start -D /pgdir/
> psql template1 -f file.sql
>
>
> upgrade from postgres 9.5alpha1 to 9.5alpha2
>
> upgrade postgres 9.5alpha1
> pg_ctl stop
> pg_ctl start -D /pgdir/
>
>
> Now I see that not stopping the database prior to the upgrade may have
> introduced the problem eventhough I don't understand the internals. I
> did do another pg_ctl stop/start after upgrade just to see if that would
> fix but it didn't.
Yeah, I would say all bets are off when overwriting a running database.
How are you doing the upgrade, from a package or source?
I now the .deb packages allow for running multiple versions concurrently
and I believe that yum can work that way also. If building from source
you can do something like --prefix=/usr/local/pgsql94 in configure to
separate versions. Then you just have to change the port in
postgresql.conf to have multiple versions on a machine. Somewhat less
dangerous then deleting $DATA.
>
> I just did the following steps, and now no error:
>
> pg_dumpall > file.sql
> pg_ctl stop
> rm -r /pgdir/*
> initdb -D /pgdir/
> pg_ctl start -D /pgdir/
> psql template1 -f file.sql
>
> logged into db and recreated the function
>
> psql db
> create function ds_stats...
>
>
>
>
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: cache lookup failed for type
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 03:16 ` Re: ERROR: cache lookup failed for type Tom Lane <tgl@sss.pgh.pa.us>
2015-08-17 03:52 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 04:08 ` Re: ERROR: cache lookup failed for type Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 04:46 ` Re: ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 14:11 ` Re: ERROR: cache lookup failed for type Adrian Klaver <adrian.klaver@aklaver.com>
@ 2015-08-17 14:13 ` Stuart <sfbarbee@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Stuart @ 2015-08-17 14:13 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql; Tom Lane <tgl@sss.pgh.pa.us>
Adrian,
Doing upgrade from source.
Thanks,
Stuart
On Aug 17, 2015 6:11 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
> On 08/16/2015 09:46 PM, Stuart wrote:
>
>> On 08/17/2015 08:08 AM, Adrian Klaver wrote:
>>
>>> So this is what you did when you started with the Alpha 1 database,
>>> correct?
>>>
>>> When you went to Alpha 2 you just installed the new program over the
>>> existing Alpha 1, but left the data directory as is and then ran into
>>> the error, correct?
>>>
>>> You then did a dump of the Alpha 1 or other(?) existing database and
>>> then a restore into the Alpha 2(the reload above) at which point the
>>> error went away, correct?
>>>
>>
>> Adrian, that is correct. More precisely, the steps taken were the
>> following which I now see where I may have potentially introduced the
>> error:
>>
>> upgrade from prostgres 9.4.4 to 9.5alpha1
>>
>> pg_dumpall > file.sql
>> pg_ctl stop
>> upgrade postgres 9.5alpha1
>> rm -r /pgdir/*
>> initdb -D /pgdir/
>> pg_ctl start -D /pgdir/
>> psql template1 -f file.sql
>>
>>
>> upgrade from postgres 9.5alpha1 to 9.5alpha2
>>
>> upgrade postgres 9.5alpha1
>> pg_ctl stop
>> pg_ctl start -D /pgdir/
>>
>>
>> Now I see that not stopping the database prior to the upgrade may have
>> introduced the problem eventhough I don't understand the internals. I
>> did do another pg_ctl stop/start after upgrade just to see if that would
>> fix but it didn't.
>>
>
> Yeah, I would say all bets are off when overwriting a running database.
>
> How are you doing the upgrade, from a package or source?
>
> I now the .deb packages allow for running multiple versions concurrently
> and I believe that yum can work that way also. If building from source you
> can do something like --prefix=/usr/local/pgsql94 in configure to separate
> versions. Then you just have to change the port in postgresql.conf to have
> multiple versions on a machine. Somewhat less dangerous then deleting $DATA.
>
>
>
>> I just did the following steps, and now no error:
>>
>> pg_dumpall > file.sql
>> pg_ctl stop
>> rm -r /pgdir/*
>> initdb -D /pgdir/
>> pg_ctl start -D /pgdir/
>> psql template1 -f file.sql
>>
>> logged into db and recreated the function
>>
>> psql db
>> create function ds_stats...
>>
>>
>>
>>
>> Thanks,
>>
>> Stuart
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2015-08-17 14:13 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-17 00:58 ERROR: cache lookup failed for type Stuart <sfbarbee@gmail.com>
2015-08-17 01:17 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 03:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-08-17 03:52 ` Stuart <sfbarbee@gmail.com>
2015-08-17 03:56 ` Stuart <sfbarbee@gmail.com>
2015-08-17 04:08 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 04:46 ` Stuart <sfbarbee@gmail.com>
2015-08-17 14:11 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 14:13 ` Stuart <sfbarbee@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox