Re: BUG #16346: pg_upgrade fails on a trigger with a comment

As you have mentioned, I have verified that indeed commit 4c40b27b broke
this.

In this particular commit moves restoration of materialized views and event
triggers to the last phase. Perhaps, comments should also be moved to this
phase as there may comments on either of these types of objects.

Attached is a patch that resolves this issue. I've verified that it resolve
the upgrade (and restore issue) introduced by 4c40b27b. I'll test this
patch in a little more detail tomorrow.

On Mon, Apr 6, 2020 at 8:26 PM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16346
> Logged by: Alexander Lakhin
> Email address: exclusion(at)gmail(dot)com
> PostgreSQL version: 12.2
> Operating system: Ubuntu 18.04
> Description:
>
> When using pg_upgrade on a database with the following contents:
> CREATE FUNCTION public.test_event_trigger() RETURNS event_trigger
> LANGUAGE plpgsql
> AS $$
> BEGIN
> RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
> END
> $$;
>
> CREATE EVENT TRIGGER regress_event_trigger3 ON ddl_command_start
> EXECUTE PROCEDURE public.test_event_trigger();
>
> COMMENT ON EVENT TRIGGER regress_event_trigger3 IS 'test comment';
>
> I get:
> Restoring global objects in the new cluster ok
> Restoring database schemas in the new cluster
> postgres
> *failure*
>
> Consult the last few lines of "pg_upgrade_dump_14174.log" for
> the probable cause of the failure.
> Failure, exiting
>
> pg_upgrade_dump_14174.log contains:
> command: "/src/postgres/tmp_install/usr/local/pgsql/bin/pg_restore" --host
> /src/postgres --port 50432 --username postgres --clean --create
> --exit-on-error --verbose --dbname template1 "pg_upgrade_dump_14174.custom"
> >> "pg_upgrade_dump_14174.log" 2>&1
> pg_restore: connecting to database for restore
> pg_restore: dropping DATABASE PROPERTIES postgres
> pg_restore: dropping DATABASE postgres
> pg_restore: creating DATABASE "postgres"
> pg_restore: connecting to new database "postgres"
> pg_restore: connecting to database "postgres" as user "postgres"
> pg_restore: creating COMMENT "DATABASE "postgres""
> pg_restore: creating DATABASE PROPERTIES "postgres"
> pg_restore: connecting to new database "postgres"
> pg_restore: connecting to database "postgres" as user "postgres"
> pg_restore: creating pg_largeobject "pg_largeobject"
> pg_restore: creating FUNCTION "public.test_event_trigger()"
> pg_restore: creating COMMENT "EVENT TRIGGER "regress_event_trigger3""
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 3705; 0 0 COMMENT EVENT TRIGGER
> "regress_event_trigger3" postgres
> pg_restore: error: could not execute query: ERROR: event trigger
> "regress_event_trigger3" does not exist
> Command was: COMMENT ON EVENT TRIGGER "regress_event_trigger3" IS 'test
> comment';
>
> It looks like the commit 4c40b27b broke this.
>
>

--
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
CELL：+923335449950 EMAIL: mailto:hamid(dot)akhtar(at)highgo(dot)ca
SKYPE: engineeredvirus