Search Postgresql Archives

Re: Getting "cache lookup failed for aggregate" error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 






On Tue, Jun 24, 2014 at 4:25 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Patrick Krecker <patrick@xxxxxxxxxxxx> writes:
> Hello everyone -- We received a strange error today on our production write
> master. During a routine maintenance script, we got the following error:

> "ERROR:  cache lookup failed for aggregate 5953992"

> I think I found the culprit. We have a cron script that (among other
> things) recreates the aggregate function array_cat_aggregate()once every
> minute.

Um.  Why's it do that?

Well, it's admittedly a hack. The cron script uses the function just after creating it. It was an easy way of guaranteeing that the database will have the function when it's necessary. However, I suppose that assumption is wrong because you have confirmed that aggregate functions are not part of the snapshot provided by beginning a transaction.
 

> My guess is that a) transactions do not guarantee a snapshot of
> custom functions and b) we got unlucky and the aggregate was deleted during
> the execution of the UPDATE.

Yeah, there's no guarantee that dropping the aggregate wouldn't leave a
window for this type of failure.

9.4 might be a little better about this because it doesn't use SnapshotNow
for catalog fetches anymore, but I think you'd still be at some risk.

> Here is the query that produced the error:

It'd be more interesting to see what the cron script was doing to the
aggregate definition.

FWIW the SQL is 

DROP AGGREGATE IF EXISTS array_cat_aggregate(anyarray);
CREATE AGGREGATE array_cat_aggregate(anyarray)  (
    SFUNC     = array_cat,
    STYPE     = anyarray,
    INITCOND  = '{}'
);

Followed by the other statement given in my previous email. But, I think you've thoroughly answered by question. Thanks!
 

                        regards, tom lane



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux