Pavel Popov <pavel.popov@gmail.com> writes: > The problem occurs when we make backup of a db on server version 11 or 12 > and restore on the same server version. When we restore on server version > 12 a backup made on server version 9.6 this problem DOES NOT occur.
I think it would happen if you tried to dump/restore with a *current* 9.6 release. The problem looks to be that this function is not safe against search_path changes:
> CREATE FUNCTION chk_f_part_pricetar(_customer integer, _pricetar integer) > RETURNS boolean > LANGUAGE plpgsql > AS $$ > BEGIN > RETURN EXISTS(SELECT * FROM customers c JOIN nom.firms f ON c.invoiced_by = f.id > JOIN prices.pricetar t ON f.id = t.firm > WHERE c.id = _customer AND t.id = _pricetar) OR _pricetar IS NULL; > END;$$;
and recent releases of pg_dump insist on running the script with a restrictive search_path for security reasons.
It might be enough to schema-qualify the function's reference to "customers", although a safer solution would be to attach a "SET search_path" clause to the function to enforce the search path it's expecting.
