Janning Vygen <vygen@gmx.de> writes:
>> OK, what's the schema of this table exactly?
> ...
> 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
Oh, I should have thought of that: the bare DELETE operation doesn't
care what's in the tuple, but this ON DELETE rule sure does. That's
why the delete crashes, it's trying to extract the field contents so
it can execute the rule.
> yes, they should both be "alteheide". Is it possible to open the file and just
> fix the bit?
Yeah, if you have a suitable hex editor. You'll probably need to shut
down the postmaster first, as it may have a cached copy of the page.
> 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.
Hmm ... the one part of that that jumps out at me is plperl. We already
know that plperl can screw up the locale settings; I wonder whether
there are other bugs. Anyway, if you are using plperl I *strongly*
recommend updating to the latest PG release ASAP (8.0.6 in your case).
If you cannot, at least make sure the postmaster is launched with the
same LC_XXX settings in its environment as are embedded in the database.
regards, tom lane