Hello,
We have done more tests about this important bug.
It is reproducible on version 8.3.9 updated from 8.3.6, 8.3.7 and
8.3.8 on linux 2.6.22-vserver-bigmem and linux 2.6.22-bigmem.
If we made a downgrade, the database still corrumpted. If we purge all
datas from postgres (apt-get remove --purge postgresql-8.3) and a
downgrade, it works. If we install a new server from scratch (with the
latest postgresql version), it works.
This is logs from postgres and linux-vserver kernel when we meet the trouble.
host:~# tail -f /var/log/postgresql.log
2010-02-02 09:18:10 CET ATTENTION: temporary file leak: File 51 still
referenced
2010-02-02 09:18:10 CET CONTEXTE : PL/pgSQL function
"getinfosproduits" line 7 during statement block exit
2010-02-02 09:18:13 CET ERREUR: unexpected end of data
2010-02-02 09:18:13 CET INSTRUCTION : select distinct i.numero,
pc.nom, pc.idparcelleculturale as idparcelle, (not
st_isempty(pc.geom)) as iscarto, c.libelleusage as culture,
(select * from
getallvarieteparcelle(pc.idparcelleculturale) ) AS variete,
pc.surfacesaisie, e.surfacetravaillee,
to_char(e.datefin, 'dd/mm/yy') as datefin,
to_char(e.datedebut, 'dd/mm/yy') as datedebutevt,
e.idevenement,
coalesce(ieref.libelle, te.libelle) as intervention,
prod.libelle as produit,
cible.libelle as cible,
round((coalesce(qte_fourra.valeur::numeric,iee.quantitetotale::numeric)
/ e.surfacetravaillee)::numeric,3) as dose,
prod.unite, e.datedebut
from tparcellesculturales pc
join tilots i using (idilot) join (select idculture, libelleusage
from tcultures where idculture = 35) as c using(idculture) join
tappartenancetypeculture using (idculture) join
tevenement e using (idparcelleculturale)
join ttypeevenement te using (idtypeevenement)
left join tintrantextrantevenement iee using (idevenement)
left join tintrantextrantref ieref using (idintrantextrantref)
left join getinfosproduits() prod on (iee.idintrantextrant =
prod.identifiant
and prod.idintrantextrantref = iee.idintrantextrantref)
left join tcible cible using (idcible)
left join (select * from tattributintrantextrant where idattribut
= 1001) as qte_fourra using (idintrantextrantevenement)
where pc.millesime = 2010
and pc.idexploitation = 5464
and e.etat = 1 order by e.datedebut, e.idevenement, intervention
*** glibc detected *** postgres: sigauser bdsol2
192.168.101.228(57669) SELECT: free(): invalid pointer: 0xb57e4008 ***
=
host:~# dmesg
[3742541.064651] postgres[14451]: segfault at b5700000 ip b7a9b6bc sp
bfc05870 error 4 in libc-2.7.so[b7a2f000+138000]
[3742590.824223] postgres[14485]: segfault at b5700000 ip b7a9b6bc sp
bfc05870 error 4 in libc-2.7.so[b7a2f000+138000]
[3742610.855664] postgres[14494]: segfault at b5989000 ip b7aa1bbf sp
bfc052d0 error 7 in libc-2.7.so[b7a2f000+138000]
[3742633.514759] postgres[14506]: segfault at f35af3c ip 0821e6df sp
bfc05200 error 4 in postgres[8048000+37e000]
[3742701.579126] postgres[14539]: segfault at b5700000 ip b7a9b6bc sp
bfc05870 error 4 in libc-2.7.so[b7a2f000+138000]
The function wich could provoque the bug :
--
-- Name: getinfosproduits(); Type: FUNCTION; Schema: public; Owner: sigauser
--
CREATE FUNCTION getinfosproduits() RETURNS SETOF infos_produit
AS $$
DECLARE
r record;
r2 record;
BEGIN
for r2 in select nomidtableref, nomtableref, idintrantextrantref
from tintrantextrantref
loop
BEGIN
if r2.nomtableref = 'vexpsemence'
then
FOR r in execute 'select distinct
idexploitation, libelle, ' || quote_ident(r2.nomidtableref) || ', ' ||
r2.idintrantextrantref
|| ',unite from vexpsemence join
tsemenceculture using (idsemence)
join tcultures using (idculture) join tappartenancetypeculture
using (idculture) where (idtypeculture = 1 or idtypeculture = 2)'
Loop
RETURN NEXT r;
END LOOP;
FOR r in execute 'select distinct
idexploitation, (libelle || '' ('' || qualsemence || '')'')::character
varying as libelle, ' || quote_ident(r2.nomidtableref) || ', ' ||
r2.idintrantextrantref
|| ',unite from vexpsemence left join
tsemenceculture using (idsemence)
left join tcultures using (idculture) left join tappartenancetypeculture
using (idculture) where (idtypeculture != 1 and idtypeculture != 2) or
idtypeculture is null'
Loop
RETURN NEXT r;
END LOOP;
else
FOR r in execute 'select idexploitation,
libelle, ' || quote_ident(r2.nomidtableref) || ', ' ||
r2.idintrantextrantref
|| ',unite from ' || quote_ident(r2.nomtableref)
Loop
RETURN NEXT r;
END LOOP;
end if;
EXCEPTION
when OTHERS then
END;
end loop;
RETURN;
END
$$
LANGUAGE plpgsql;
ALTER FUNCTION public.getinfosproduits() OWNER TO sigauser;
I can give the dump and datas from postgresql, I will sent the link if
you contact me in private.
--
To UNSUBSCRIBE, email to [email protected]
with a subject of "unsubscribe". Trouble? Contact [email protected]
