Postgres Pro
Downloads
Home   >   mailing lists

Thread: Need pg_dump not to dump extension-created triggers

Need pg_dump not to dump extension-created triggers

From
Moshe Jacobson
Date:
I have an extension which, after installation, creates triggers and their associated functions dynamically and adds the functions to the extension so that they will be dropped with the extension.

However, there is no way to add the trigger to the extension, so pg_dump's output still includes the definition of the trigger, even though it does not include the definition of the corresponding function. This is causing pg_restore to emit warnings on the CREATE TRIGGER commands.

The behavior does not change when I manually insert into pg_depend a dependency from the trigger to the extension.

I'd like to know if the following changes can be made, as I think they will fix this issue:
  1. Add command ALTER EXTENSION ... ADD TRIGGER.
  2. Modify pg_dump not to dump triggers that are dependent on an extension.
Thanks.

P.S. I'm here at PGCon and will be speaking on Friday about this very extension. Come say hi!

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle

Re: Need pg_dump not to dump extension-created triggers

From
Shaun Thomas
Date:
On 05/21/2014 10:18 PM, Moshe Jacobson wrote:

> I'd like to know if the following changes can be made, as I think they
> will fix this issue:
>
>  1. Add command ALTER EXTENSION ... ADD TRIGGER.
>  2. Modify pg_dump not to dump triggers that are dependent on an extension.

Haha. I remember this from the Unconference day at PGCon on Saturday.

For anyone who wasn't there, I think the consensus was that the
extension author could "fix" this by creating the triggers as not owned
by the extension, but owned by an extension role. That way, dropping the
role would cascade to the triggers, but the triggers would still be
created normally as dependent objects of the extension, hence no errors.

However, it would be a lot nicer if we had some explicit syntax to avoid
this kind of situation.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email