Sunday, June 22, 2008

[BUGS] Abnormal termination of PostgreSQL

Tom Lane wrote:

> Bill Thoen <bthoen@gisnet.com> writes:
>
>> Does anyone know what happened and how I can fix it?
>>
> Well, you evidently hit a bug, but there's not nearly enough info here
> to guess whether the bug is in Postgres, PostGIS, GEOS, or GDAL, or
> perhaps in your custom build process for one of these.
>
> ...
>
> Then send the trace to pgsql-bugs. You'll need to provide some details
> about the table and the query, too.
>
Is this what you need to see? (I attached GDB to the running postgres
process and let it continue until it crashed, and then entered 'bt' and
this is what I got.)

Program received signal SIGSEGV, Segmentation fault.
0x08081357 in slot_deform_tuple (slot=0x92d3618, natts=4) at
heaptuple.c:1502
1502 off = att_align_pointer(off,
thisatt->attalign, -1,
(gdb) bt
#0 0x08081357 in slot_deform_tuple (slot=0x92d3618, natts=4) at
heaptuple.c:1502
#1 0x08081739 in slot_getattr (slot=0x92d3618, attnum=4,
isnull=0x92dd753 "") at heaptuple.c:1625
#2 0x08168525 in ExecProject (projInfo=0x92dd470, isDone=0xbfed09e8) at
execQual.c:4601
#3 0x0816e5f6 in ExecScan (node=0x92d3798, accessMtd=0x8179a70
<SeqNext>) at execScan.c:143
#4 0x08179a69 in ExecSeqScan (node=0x92d3798) at nodeSeqscan.c:130
#5 0x08167888 in ExecProcNode (node=0x92d3798) at execProcnode.c:334
#6 0x08165b23 in ExecutorRun (queryDesc=0x92a3b50,
direction=ForwardScanDirection, count=0) at execMain.c:1248
#7 0x082024ab in ProcessQuery (plan=0x929c798, params=<value optimized
out>, dest=0x92be820,
completionTag=0xbfed0c8a "") at pquery.c:179
#8 0x082026ac in PortalRunMulti (portal=0x92ca8b0, isTopLevel=<value
optimized out>, dest=0x92be820,
altdest=0x92be820, completionTag=0xbfed0c8a "") at pquery.c:1242
#9 0x08202e2c in PortalRun (portal=0x92ca8b0, count=2147483647,
isTopLevel=-45 '�', dest=0x92be820,
altdest=0x92be820, completionTag=0xbfed0c8a "") at pquery.c:813
#10 0x081fdcf7 in exec_simple_query (
query_string=0x9272d18 "update corn0715 set
ncs_yield_factor=ncs_yield_factor/10000,
transitional_yield=transitional_yield/100, fsa_yield=fsa_yield/100,
aqpproved_yield=aqpproved_yield/100, prev_approved_yield=prev_approved_"...)
at postgres.c:986
#11 0x081ff44c in PostgresMain (argc=4, argv=0x921b590,
username=0x921b570 "bthoen") at postgres.c:3572
#12 0x081d253f in ServerLoop () at postmaster.c:3207
---Type <return> to continue, or q <return> to quit---
#13 0x081d3492 in PostmasterMain (argc=3, argv=0x9218378) at
postmaster.c:1029
#14 0x08188126 in main (argc=3, argv=0x9218378) at main.c:188
(gdb)

Here's the backstory. The process that's crashing is an SQL update
query on the table (corn0715). There are 104 fields in the table and the
raw record length is about 648 characters. I have just imported it as a
delimited file with COPY and there are about 1.7 million records. There
are no indexes yet, and even though the database has been "spatialized"
by PostGIS this particular table has no spatial component. The database
contains only this table and the two reference tables installed by PostGIS.

Also, this crash occurs not only when running the update query, but
also when trying to create an index or selecting data from all rows or
even when running pg_dump. It takes about 10 minuets to occur, which is
about the same time that it took to SELECT count(*) FROM corn0715;
(which did work, BTW). So I suspect it's an "end-of-table" problem.

Here's the query:
UPDATE corn0715 SET ncs_yield_factor=ncs_yield_factor/10000,
transitional_yield=transitional_yield/100, fsa_yield=fsa_yield/100,
aqpproved_yield=aqpproved_yield/100,
prev_approved_yield=prev_approved_yield/100,
annual_yield_1=annual_yield_1/100,
yield_acres_1=yield_acres_1/100,annual_yield_2=annual_yield_2/100,
yield_acres_2=yield_acres_2/100,annual_yield_3=annual_yield_3/100,
yield_acres_3=yield_acres_3/100, annual_yield_4=annual_yield_4/100,
yield_acres_4=yield_acres_4/100, annual_yield_5=annual_yield_5/100,
yield_acres_5=yield_acres_5/100, annual_yield_6=annual_yield_6/100,
yield_acres_6=yield_acres_6/100, annual_yield_7=annual_yield_7/100,
yield_acres_7=yield_acres_7/100, annual_yield_8=annual_yield_8/100,
yield_acres_8=yield_acres_8/100, annual_yield_9=annual_yield_9/100,
yield_acres_9=yield_acres_9/100, annual_yield_10=annual_yield_10/100,
yield_acres_10=yield_acres_10/100, rate_yield=rate_yield/100,
average_yield=average_yield/100,
perennial_transitional_yield_factor=perennial_transitional_yield_factor/100;

I compiled PostgreSQL 8.3.3, PostGIS 1.3.3, GEOS 3.0.0, Proj4 4.6.0 and
GDAL 1.5.2 from source using gcc 4.3.0 on a newly installed Fedora Core
9 system. All software is the latest version and the disc drive was
wiped clean before I started, so there are no old libraries or
configurations lurking in the background.

I did have some issues compiling GEOS, PostGIS and GDAL. Apparently,
there have been some changes in gcc, particularly in the include files,
and I was getting errors like 'memcpy' not declared, and the like. I
fixed that (I think) with a diff patch, and everything seemed to compile
and install OK.

I don't really understand how to interpret the results from GDB, but if
somebody who knows could take a look at this and give me some
suggestions, I'd appreciate it. If you need samples of the table, or the
SQL scripts I used to load it, I can provide those too. If you want to
see the entire table you'll need at least about 1.1 GB of disc space.

TIA,
- Bill Thoen


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

No comments: