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