Am Montag, 23. Januar 2006 20:30 schrieb Tom Lane:
> Janning Vygen <vygen@xxxxxx> writes:
> > Ok, i got the reffilnode from pg_class and compiled pg_filedump. result
> > of ./pg_filedump -i -f -R 3397
> > /home/postgres8/data/base/12934120/12934361 > filedump.txt is attached
>
> OK, what's the schema of this table exactly? It looks like there are
> a couple of text or varchar columns to start, but I'm not sure about the
> last three columns.
kicktipp.de=> \d spieletipps
Tabelle »public.spieletipps«
Spalte | Typ | Attribute
---------------+----------+-----------
tr_kurzname | text | not null
mg_name | text | not null
sp_id | integer | not null
stip_heimtore | smallint | not null
stip_gasttore | smallint | not null
Indexe:
»pk_spieletipps« PRIMARY KEY, btree (tr_kurzname, mg_name, sp_id)
»ix_stip_fk_spiele« btree (tr_kurzname, sp_id) CLUSTER
Fremdschlüssel-Constraints:
»fk_mitglieder« FOREIGN KEY (tr_kurzname, mg_name) REFERENCES
mitglieder(tr_kurzname, mg_name) ON UPDATE CASCADE ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED
»fk_tippspieltage2spiele« FOREIGN KEY (tr_kurzname, sp_id) REFERENCES
tippspieltage2spiele(tr_kurzname, sp_id) ON UPDATE CASCADE ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED
Regeln:
cache_stip_delete AS
ON DELETE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2
FROM tippspieltage2spiele tspt2sp, spiele sp
WHERE tsptcache.tr_kurzname = old.tr_kurzname AND tspt2sp.tr_kurzname =
old.tr_kurzname AND tspt2sp.sp_id = old.sp_id AND tspt2sp.sp_id = sp.sp_id
AND sp.sp_abpfiff = true AND tsptcache.tspt_sort >= tspt2sp.tspt_sort AND
sign((old.stip_heimtore - old.stip_gasttore)::double precision) =
sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND
tsptcache.tc_cache <> -2
cache_stip_insert AS
ON INSERT TO spieletipps DO UPDATE tsptcache SET tc_cache = -2
FROM tippspieltage2spiele tspt2sp, spiele sp
WHERE tsptcache.tr_kurzname = new.tr_kurzname AND tspt2sp.tr_kurzname =
new.tr_kurzname AND tspt2sp.sp_id = new.sp_id AND tspt2sp.sp_id = sp.sp_id
AND sp.sp_abpfiff = true AND tsptcache.tspt_sort >= tspt2sp.tspt_sort AND
sign((new.stip_heimtore - new.stip_gasttore)::double precision) =
sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND
tsptcache.tc_cache <> -2
cache_stip_update AS
ON UPDATE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2
FROM tippspieltage2spiele tspt2sp, spiele sp
WHERE tsptcache.tr_kurzname = new.tr_kurzname AND tspt2sp.tr_kurzname =
new.tr_kurzname AND tspt2sp.sp_id = new.sp_id AND tspt2sp.sp_id = sp.sp_id
AND sp.sp_abpfiff = true AND tsptcache.tspt_sort >= tspt2sp.tspt_sort AND
(sign((new.stip_heimtore - new.stip_gasttore)::double precision) =
sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) OR
sign((old.stip_heimtore - old.stip_gasttore)::double precision) =
sign((sp.sp_heimtore - sp.sp_gasttore)::double precision)) AND
tsptcache.tc_cache <> -2
> > but i guess its item 49 which makes trouble
> > 1258: 01000000 616c7465 68656964 65000000 ....alteheide...
> >
> > But it doesn't look very diffrent to item 48:
> > 12a0: 0d000000 616c7465 68656964 65000000 ....alteheide...
>
> If these are both supposed to be strings 'alteheide', then the problem
> is the bogus length word on the first one: instead of starting with
> 01000000 it should start with 0d000000, like the second one does.
yes, they should both be "alteheide". Is it possible to open the file and just
fix the bit?
> It's conceivable that this stems from a software problem, but I'm
> wondering about hardware problems causing dropped bits, myself.
I have no clue, why it happens. But i changed my schema a few month ago to use
a materialized view (You see all the rules in this schema above). i need some
complicated ranking algorithm to calculate the materialzed view. everything
is implemented inside postgresql with rules and functions (pgperl and
plpgsql). One more aspect are temp tables to me. I use lots of them for a
specific tasks (reusing the calculating algorithm mentioned above for a
different data view). With lots of temp tables i got problems with pg_type
where some old temp values reside and i got to delete some of them manually a
few times per month. After all my "feeling" is that i encouter problems like
this one too often to believe in hardware problems. But this time it seems to
be a new one and i have no clue if hardware or software related. At this time
i just want to fix it. But if you want to take a close look at it, i will
send you all you need.
> Another point is that AFAICS this tuple could not pose a problem for
> DELETE all by itself, because it doesn't have any toasted fields.
> Perhaps there is more corruption elsewhere. Could you get a stack
> trace from the crashed DELETE, rather than a crashed SELECT?
Maybe the rule is a problem?
here you are. I did:
select ctid from spieletipps limit 1 offset 387439;
ctid
-----------
(3397,49)
(1 Zeile)
kicktipp.de=> delete from spieletipps where ctid = '(3397,49)';
Server beendete die Verbindung unerwartet
Das heißt wahrscheinlich, daß der Server abnormal beendete
bevor oder während die Anweisung bearbeitet wurde.
Die Verbindung zum Server wurde verloren. Versuche Reset: Fehlgeschlagen.
!> \q
gdb output:
Loaded symbols for /usr/lib64/libkrb5support.so.0
#0 0x00000000004373f0 in nocachegetattr ()
(gdb) bt
#0 0x00000000004373f0 in nocachegetattr ()
#1 0x00000000004d614d in ExecInitExprInitPlan ()
#2 0x00000000004d3c1d in ExecProject ()
#3 0x00000000004d7c38 in ExecScan ()
#4 0x00000000004d33ad in ExecProcNode ()
#5 0x00000000004dfde1 in ExecNestLoop ()
#6 0x00000000004d337d in ExecProcNode ()
#7 0x00000000004dfde1 in ExecNestLoop ()
#8 0x00000000004d337d in ExecProcNode ()
#9 0x00000000004dfde1 in ExecNestLoop ()
#10 0x00000000004d337d in ExecProcNode ()
#11 0x00000000004d1e9c in ExecutorRun ()
#12 0x0000000000549b32 in CreateQueryDesc ()
#13 0x000000000054a10a in PortalRun ()
#14 0x0000000000546382 in pg_parse_query ()
#15 0x0000000000547eba in PostgresMain ()
#16 0x000000000051ef44 in ClosePostmasterPorts ()
#17 0x000000000051fce1 in PostmasterMain ()
#18 0x00000000004ef5c3 in main ()
(gdb) q
kind regards,
janning
