Re: Upgrades and Error Messages

Thangalin <thangalin(at)gmail(dot)com> writes:
> $ tail pg_upgrade_dump_11955.log
> pg_restore: setting owner and privileges for ACL plperlu
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1955; 0 0 ACL plperlu postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: language
> "plperlu" is not trusted
> HINT: Only superusers can use untrusted languages.
> Command was: REVOKE ALL ON LANGUAGE "plperlu" FROM PUBLIC;
> REVOKE ALL ON LANGUAGE "plperlu" FROM "postgres";
> GRANT ALL ON LANGUAGE "plper...

Hmm, that's odd. Maybe you have the language marked as owned by a
non-superuser in the original database? Or had you done this:

> CREATE OR REPLACE TRUSTED LANGUAGE plperlu;
> UPDATE pg_language set lanpltrusted = true where lanname='plperlu';

in the original database beforehand?

If the latter, I'm just going to shrug and say that that's unsupported.
Pretending that a language is trusted when it is not is a recipe for
security holes and worse. By and large, *any* direct update on a system
catalog is a situation where we're going to say that any bad consequences
are your own fault.

(I think this theory does explain the failure, FWIW. pg_dump would have
seen the language marked trusted in the source database, so it would have
dumped suitable GRANT/REVOKE commands for it. But in the new database,
CREATE LANGUAGE would have forced the language to untrusted since it knows
that's what it should be by consulting pg_pltemplate. Then you'd have
gotten this error because GRANT/REVOKE are disallowed on untrusted
languages.)

> Speaking of errors, most of the errors returned from PostgreSQL are
> technically accurate but provide the user with no clue as to how to
> diagnose the problem.

We do what we can. In many cases, it's really impossible for the
code author to anticipate every cause of a possible failure, and even
if he could, listing them all would render error messages unusable.
Surely we could not, for example, mention the possibility of "you made
an ill-advised manual change to the system catalogs" every single place
where that could be a contributing factor.

In the particular case at hand, I think the HINT is a bit off base
--- it'd be more useful to say something like "GRANT and REVOKE are
not allowed on untrusted languages". But it's really hard to see
how the error message could drill down to the ultimate cause.

> pg_upgrade: ERROR 15363: Cannot be run as root
> pg_upgrade: See https://postgresql.org/errors/15363/

This has been proposed before, but I haven't seen any army of
well-informed volunteers ready to populate such a website with
complete and reliable information. A quick count says there
are order-of-10000 distinct errors in our current sources, so
you would need an army if you wanted it done any time soon.

regards, tom lane