MerkwürdigeFehlermeldung
am 09.01.2011 14:52:32 von Andreas TilleHallo,
ich versuche eine Abfrage an die Ultimate Debian Database zu optimieren.
Es sollen Beschreibungsübersetzungen zu einem als Textfeld übergebene=
n
Satz von Paketen in bestimmten Sprachen abgefragt werden. Es soll
diejenige jeweils diejenige Übersetzung gefunden werden, die zur
höchsten Version gehört und zum letzten Debian Release. Die Details
werden wohl nur klar, wenn man die UDD kennt, aber das Problem ist
möglicherweise auch ohne diese Details erkennbar (die benötigten Abfr=
agen
stehen daher unten, um wenigstens die Struktur der Abfragen zu erkennen):
explain analyse
SELECT * FROM blends_metapackage_translations (
'{"med-bio","med-bio-dev","med-cms","med-data","med-dental", "med-epi","me=
d-his","med-imaging","med-imaging-dev","med-laboratory","med -pharmacy","m=
ed-physics","med-practice","med-psychology","med
-statistics","med-tools","med-typesetting"}'
) AS (
package text,
description_cs text, long_description_cs text,
description_da text, long_description_da text,
description_de text, long_description_de text,
description_es text, long_description_es text,
description_fi text, long_description_fi text,
description_fr text, long_description_fr text,
description_hu text, long_description_hu text,
description_it text, long_description_it text,
description_ja text, long_description_ja text,
description_ko text, long_description_ko text,
description_nl text, long_description_nl text,
description_pl text, long_description_pl text,
"description_pt_BR" text, "long_description_pt_BR" text,
description_ru text, long_description_ru text,
description_sk text, long_description_sk text,
description_sv text, long_description_sv text,
description_uk text, long_description_uk text,
"description_zh_CN" text, "long_description_zh_CN" text,
"description_zh_TW" text, "long_description_zh_TW" text
);
FEHLER: konnte Segment 1 der Relation base/27126/161049 nicht öffnen (=
Zielblock 1644167168): Datei oder Verzeichnis nicht gefunden
KONTEXT: SQL-Funktion =BBblends_metapackage_translations=AB Anweisung 1
Was konkret bedeutet diese Fehlermeldung? Ich verwende auf
verschiedenen Rechnern PostgreSQL 8.4.5 und lokale Kopien der UDD. Das
Problem tritt nur bei genau einem dieser Rechner auf. Wasfür eine Date=
i
wird da nicht gefunden? Hängt es irgendwie mit der Konfiguration des
Servers zusammen? Ich habe das Paket von backports.org auf einer
virtuellen Maschine mit Lenny installiert und keine Einstellungen der
Konfiguration vorgenommen.
Vielen Dank für hilfreiche Hinweise
Andreas.
CREATE OR REPLACE FUNCTION ddtp_unique(text, text[]) RETURNS SETOF RECORD=
AS $$
SELECT DISTINCT d.package, d.description, d.long_description FROM ddtp =
d
JOIN (
SELECT dr.package, dr.version, (SELECT release FROM releases WHERE =
sort =3D MAX(r.sort)) AS release FROM ddtp dr
JOIN (
SELECT package, MAX(version) AS version FROM ddtp WHERE languag=
e =3D $1 AND package =3D ANY ($2) GROUP BY package
) duv ON duv.package =3D dr.package AND duv.version =3D dr.versio=
n
JOIN releases r ON dr.release =3D r.release
WHERE language =3D $1 AND dr.package =3D ANY ($2)
GROUP BY dr.package, dr.version
-- sometimes there are different translations of the same package ver=
sion in different releases
-- because translators moved on working inbetween releases but we nee=
d to select only one of these
-- (the last one)
) duvr ON duvr.package =3D d.package AND duvr.version =3D d.version A=
ND duvr.release =3D d.release
WHERE language =3D $1 AND d.package =3D ANY ($2)
$$ LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION blends_metapackage_translations (text[]) RETUR=
NS SETOF RECORD AS $$
SELECT
p.package,
cs.description_cs, cs.long_description_cs,
da.description_da, da.long_description_da,
de.description_de, de.long_description_de,
es.description_es, es.long_description_es,
fi.description_fi, fi.long_description_fi,
fr.description_fr, fr.long_description_fr,
hu.description_hu, hu.long_description_hu,
it.description_it, it.long_description_it,
ja.description_ja, ja.long_description_ja,
ko.description_ko, ko.long_description_ko,
nl.description_nl, nl.long_description_nl,
pl.description_pl, pl.long_description_pl,
pt_BR.description_pt_BR, pt_BR.long_description_pt_BR,
ru.description_ru, ru.long_description_ru,
sk.description_sk, sk.long_description_sk,
sv.description_sv, sv.long_description_sv,
uk.description_uk, uk.long_description_uk,
zh_CN.description_zh_CN, zh_CN.long_description_zh_CN,
zh_TW.description_zh_TW, zh_TW.long_description_zh_TW
FROM packages p
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('cs', $1) AS (package text=
, description_cs text, long_description_cs text)) cs ON cs.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('da', $1) AS (package text=
, description_da text, long_description_da text)) da ON da.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('de', $1) AS (package text=
, description_de text, long_description_de text)) de ON de.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('es', $1) AS (package text=
, description_es text, long_description_es text)) es ON es.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('fi', $1) AS (package text=
, description_fi text, long_description_fi text)) fi ON fi.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('fr', $1) AS (package text=
, description_fr text, long_description_fr text)) fr ON fr.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('hu', $1) AS (package text=
, description_hu text, long_description_hu text)) hu ON hu.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('it', $1) AS (package text=
, description_it text, long_description_it text)) it ON it.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('ja', $1) AS (package text=
, description_ja text, long_description_ja text)) ja ON ja.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('ko', $1) AS (package text=
, description_ko text, long_description_ko text)) ko ON ko.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('nl', $1) AS (package text=
, description_nl text, long_description_nl text)) nl ON nl.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('pl', $1) AS (package text=
, description_pl text, long_description_pl text)) pl ON pl.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('pt_BR', $1) AS (package t=
ext, description_pt_BR text, long_description_pt_BR text)) pt_BR ON pt_BR=
..package =3D p.package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('ru', $1) AS (package text=
, description_ru text, long_description_ru text)) ru ON ru.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('sk', $1) AS (package text=
, description_sk text, long_description_sk text)) sk ON sk.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('sv', $1) AS (package text=
, description_sv text, long_description_sv text)) sv ON sv.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('uk', $1) AS (package text=
, description_uk text, long_description_uk text)) uk ON uk.package =3D p.=
package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('zh_CN', $1) AS (package t=
ext, description_zh_CN text, long_description_zh_CN text)) zh_CN ON zh_CN=
..package =3D p.package
LEFT OUTER JOIN (SELECT * FROM ddtp_unique('zh_TW', $1) AS (package t=
ext, description_zh_TW text, long_description_zh_TW text)) zh_TW ON zh_TW=
..package =3D p.package
WHERE p.package =3D ANY ($1)
$$ LANGUAGE 'SQL';
--=20
http://fam-tille.de
--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein