Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
> Here is a SQL dump for the table. One can just neglect the JOIN with
> the countries table (which just replaces the country id with the
> country name):
> http://geodata.grid.unep.ch/download/sql_agri_area.sql.zip
> But when re-doing the query now without the JOIN, it works (almost):
OK, after poking at it, it seems that crosstab() isn't prepared for
null rowids. I can reproduce the crash without any data:
contrib_regression=# select * from crosstab(
'SELECT null::text as name, 10 as year, 42 as value', 3)
as ct(name text, year int, value int);
server closed the connection unexpectedly
Backtrace looks like
#0 0xc008774c in ?? () from /usr/lib/libc.1
#1 0x3eb0bc in MemoryContextStrdup (context=0x40167048, string=0x0)
at mcxt.c:662
#2 0xc0a5f2e4 in crosstab (fcinfo=0x7b03b858) at tablefunc.c:539
#3 0x239e24 in ExecMakeTableFunctionResult (funcexpr=0x401615e8,
econtext=0x401611f0, expectedDesc=0x401613a0, returnDesc=0x7b03b7d8)
at execQual.c:1566
#4 0x24d264 in FunctionNext (node=0x40161160) at nodeFunctionscan.c:68
#5 0x23ed8c in ExecScan (node=0x40161160,
accessMtd=0x400170b2 <DINFINITY+3218>) at execScan.c:68
#6 0x24d2c4 in ExecFunctionScan (node=0x40167048) at nodeFunctionscan.c:109
so it's trying to pstrdup a null result from SPI_getvalue.
Obviously it shouldn't crash, but I'm not sure what it *should* do in
this case. Joe?
In the meantime, it appears that you want to not use a LEFT JOIN here,
or else maybe COALESCE(c.name, '') so that a null isn't returned to
crosstab.
regards, tom lane
