retitle 928942 udd: cannot import current dump due to matview referencing function without schema done
Hi, Am Montag, den 13.05.2019, 15:44 -0300 schrieb Leandro Doctors: > Package: qa.debian.org > Severity: grave > Justification: causes non-serious data loss> not sure whether that is warranted; a materialized view is by definition no new data that could be lost. > 6) Finally, I import the dump > sudo -u postgres pg_restore -j 8 --no-owner -v -C -d postgres > udd.dump.2019.05.06.dump > > > After step 6, I get the following error: > > pg_restore: launching item 4101 MATERIALIZED VIEW DATA tmp > pg_restore: creating MATERIALIZED VIEW DATA "public.tmp" > pg_restore: [archiver (db)] Error from TOC entry 4101; 0 91852869 > MATERIALIZED VIEW DATA tmp udd > pg_restore: [archiver (db)] could not execute query: ERROR: > function release_name(text) does not exist > LINE 1: SELECT release_name($1) > ^ > HINT: No function matches the given name and argument types. You > might need to add explicit type casts. > QUERY: SELECT release_name($1) > CONTEXT: PL/pgSQL function public.bugs_rt_affects_dist(text) line > 4 during statement block local variable initialization > Command was: REFRESH MATERIALIZED VIEW public.tmp; The problem is that the UDD schema does not schema-qualify functions when using them. As it seems to use the public schema, that was fine until a while ago, but a recent PostgreSQL patch release has reset the schema search path for security reasons. So that means functions referenced in materialized views need to be schema-qualified now or else the restore will fail. I had a look at the schema.sql at https://salsa.debian.org/qa/udd/blob/m aster/sql/udd-schema.sql and this one does not have any materialized views, so public.tmp is apparently a hack in production (hence the name). The actual problem appears to be in the bugs_rt_affect_dist function that calls release_name($1) without schema-qualifiying it: https://salsa.debian.org/qa/udd/blob/master/sql/udd-schema.sql#L553 cheers, Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.ba...@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz
