Thread: 9.6beta2: query failure with 'cache lookup failed for type 0'
Hello, re-testing our application Openbravo on 9.6beta2 i found the following query failing to run with ERROR: cache lookup failed for type 0 Tested on 9.6beta2 Specifically debian package version '9.6~beta2-1.pgdg+1' from apt.postgresql.org SELECT SUM(C_ORDERLINE.LINENETAMT), C_CURRENCY_SYMBOL2 (SUM(C_ORDERLINE.LINENETAMT)) FROMC_ORDER, C_ORDERLINE WHERE C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID GROUP BY C_ORDER.DOCUMENTNOORDER BY C_ORDER.DOCUMENTNO; Note: query is slimmed down manually which still reproduces the issue (cut down from biggger query), which explain the kindof (now)(now) useless pl-function. To reproduce in new empty database: CREATE OR REPLACE FUNCTION public.c_currency_symbol2(p_amount numeric) RETURNS character varying AS $BODY$ DECLARE BEGIN RETURN p_amount; END ; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; create table c_order (c_order_id varchar(32) primary key, documentno varchar(60)); create table c_orderline (c_orderline_id varchar(32) primary key, c_order_id varchar(32), linenetamt numeric, c_currency_idvarchar(32)); Deleting any more out of the query seems to no longer trigger the problem. Also changing the 'c_orderline' create table statement to not have the last column 'c_currency_id' (which is not even referencedin the query) also makes the issue no longer reproducible. Regards, Stefan
Stefan Huehner <stefan@huehner.org> writes: > re-testing our application Openbravo on 9.6beta2 i found the following query failing to run with > ERROR: cache lookup failed for type 0 Hmm, I can't reproduce this on HEAD, but it doesn't seem to look like anything we've fixed post-beta2. Do you need to put any particular data into the tables? Are you running with any nondefault configuration parameters? regards, tom lane
On Sat, Jul 02, 2016 at 11:35:52AM -0400, Tom Lane wrote: > Stefan Huehner <stefan@huehner.org> writes: > > re-testing our application Openbravo on 9.6beta2 i found the following query failing to run with > > ERROR: cache lookup failed for type 0 > > Hmm, I can't reproduce this on HEAD, but it doesn't seem to look like > anything we've fixed post-beta2. Do you need to put any particular data > into the tables? Are you running with any nondefault configuration > parameters? No data at all needed in table. In fact just create database + create 3 those objects is enough to reproduce it. Also i did a test-built of HEAD (commit: b54f7a9ac9646845138f6851fdf3097e22daa383) An get to same failure also. ./configure --prefix=/home/huehner/oss/postgresql/git/install make -j8 install bin/initdb -D pg_data bin/postmaster -D pg_data -p 5555 So question is what in my env is triggering it? Running intel 64bit debian/unstable here. Stefan
Re: Stefan Huehner 2016-07-02 <20160702160042.GA11659@huehner.biz> > No data at all needed in table. > In fact just create database + create 3 those objects is enough to reproduce it. Confirmed here on Debian unstable amd64, beta2. FEHLER: XX000: cache lookup failed for type 0 ORT: get_typlenbyval, lsyscache.c:1976 Christoph
Stefan Huehner <stefan@huehner.org> writes: > On Sat, Jul 02, 2016 at 11:35:52AM -0400, Tom Lane wrote: >> Hmm, I can't reproduce this on HEAD, but it doesn't seem to look like >> anything we've fixed post-beta2. Do you need to put any particular data >> into the tables? Are you running with any nondefault configuration >> parameters? > No data at all needed in table. Ah, I found it: need to ANALYZE the tables. Then I get TRAP: FailedAssertion("!(((bool) ((aggtranstype) != ((Oid) 0))))", File: "nodeAgg.c", Line: 2698) Looks like planner is somehow forgetting to assign aggtranstype for the aggregate. Will trace it down, thanks for the report! regards, tom lane