Collected from the PG bugs email list.
|Reported by||Nick Barnes|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15310 Logged by: Nick Barnes Email address: (redacted) PostgreSQL version: 10.4 Operating system: CentOS 7 Description: Hi, I have an extension which contains an event trigger. As expected, CREATE EXTENSION adds a pg_depend entry between the trigger and the extension. But after running pg_upgrade, this pg_depend entry is gone (and the extension's CREATE EVENT TRIGGER statement now shows up in the pg_dump output, causing the restore to fail with an "event trigger already exists" error). Reproduced for 9.5->9.6 and 9.6->10 upgrades with the attached script. Did I miss something, or is this a bug? If so, is an ALTER EXTENSION ... ADD EVENT TRIGGER command on the upgraded database enough to work around it? It solves my issue, but I'm not sure if there are other symptoms. Thanks, Nick Barnes export PGDATAOLD=~/9.5/data export PGDATANEW=~/10/data export PGBINOLD=/usr/pgsql-9.5/bin export PGBINNEW=/usr/pgsql-10/bin # Create extension files cat >$PGBINOLD/../share/extension/event_trigger_test.control <<EOF default_version = '1.0' EOF cat >$PGBINOLD/../share/extension/event_trigger_test--1.0.sql <<EOF CREATE FUNCTION t() RETURNS event_trigger LANGUAGE plpgsql AS 'BEGIN END'; CREATE EVENT TRIGGER t ON ddl_command_end EXECUTE PROCEDURE t(); EOF cp $PGBINOLD/../share/extension/event_trigger_test* $PGBINNEW/../share/extension/ # Set up old server $PGBINOLD/initdb -D $PGDATAOLD $PGBINOLD/pg_ctl start -w -D $PGDATAOLD $PGBINOLD/createdb test $PGBINOLD/psql test -c "CREATE EXTENSION event_trigger_test" # Upgrade $PGBINOLD/pg_ctl stop -D $PGDATAOLD $PGBINNEW/initdb -D $PGDATANEW $PGBINNEW/pg_upgrade $PGBINNEW/pg_ctl start -w -D $PGDATANEW # Dump/restore $PGBINNEW/createdb test2 $PGBINNEW/pg_dump test | $PGBINNEW/psql test2 # ERROR: event trigger "t" already exists
|2018-08-07 00:13:58+00||=?utf-8?q?PG_Bug_reporting_form?=||BUG #15310: pg_upgrade dissociates event triggers from extensions|
|2018-08-07 09:40:24+00||Haribabu Kommi||Re: BUG #15310: pg_upgrade dissociates event triggers from extensions|
|2018-08-07 11:29:10+00||Michael Paquier||Re: BUG #15310: pg_upgrade dissociates event triggers from extensions|
|2018-08-07 18:40:13+00||Tom Lane||Re: BUG #15310: pg_upgrade dissociates event triggers from extensions|
|2018-08-07 20:38:03+00||Tom Lane||Re: BUG #15310: pg_upgrade dissociates event triggers from extensions|