Stefan Schwarzer <stefan.schwarzer@xxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
