Re: invalid memory alloc request size - Mailing list pgsql-general

Am Montag, 23. Januar 2006 20:30 schrieb Tom Lane:
> Janning Vygen <vygen@gmx.de> 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