Disabling triggers on tables dumped with pg_extension_config_dump()

From: Michel Pelletier <pelletier(dot)michel(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Disabling triggers on tables dumped with pg_extension_config_dump()
Date: 2023-01-11 19:40:49
Message-ID: CACxu=v+XNc3vVK88upwR9W_JXptG-4wq=j6Ft0FXphT64fCAoA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have an extension that contains a configuration table with a before
insert trigger that I setup to dump with pg_extension_config_dump().
Because the table and trigger are setup during CREATE EXTENSION time
emitted by the dump, and the dumped table is then COPY'd outside of that,
the triggers get re-fired. This doesn't happen for non-extension tables,
which don't run CREATE TRIGGER until after the COPY.

Trying to find the best way to solve this problem in general for extension
users so that they don't have to use --disable-triggers on the dump or
restore as this seems like a rare edge case. Can anyone suggest a better
way?

I'm wondering, would it make sense to extend pg_extension_config_dump()
with a disable_triggers argument, that defaults to false, and if set to
true, pg_dump will emit DISABLE/ENABLE guards around the config table's
COPY statement? Just a wild idea.

Thanks!

-Michel

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2023-01-11 21:00:38 Changing displayed time zone in RAISE NOTICE output?
Previous Message Fred Habash 2023-01-11 17:46:59 Re: Autovacuum Hung Due to Bufferpin