Обсуждение: 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