Re: BUG #15310: pg_upgrade dissociates event triggers from extensions

On Tue, Aug 7, 2018 at 10:14 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> 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?

Yes, I feel it is a bug. During the dump of the event trigger during
upgrade, it lost
the dependency on extension.

> 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.
>

Yes, the above command can solve your problem.

While checking this issue, why it is missed to dump the extension
dependency,
I observed that there is call to the
function binary_upgrade_extension_member()
to add extension dependency for particular types in the pg_dump.

DO_EVENT_TRIGGER

The above type as per the bug is losing the dependency, by adding a
function call in dumpEventTrigger() function the dependency issue is
resolved.
Patch attached.

DO_SHELL_TYPE

I am not able to generate a scenario for the above type where it can loss
the
extension dependency.

The other types that don't create extension dependency are as follows,

DO_INDEX, DO_STATSEXT, DO_RULE, DO_TRIGGER,
DO_POLICY, DO_PUBLICATION, DO_SUBSCRIPTION

As per the above, a trigger is not depends on an extension, but an event
trigger
does. Do we need to support the same for trigger also?

Regards,
Haribabu Kommi
Fujitsu Australia