Re: A trigger in an extension

On 20.02.2025 22:30, Tom Lane wrote:
> Olleg Samoylov <splarv(at)ya(dot)ru> writes:
>> I have the extension pgpro_scheduler. In the exception there are two
>> tables and the trigger on one of them that write to the other. I was
>> surprised but when I load dump created by pg_dump this trigger is
>> created in the pre-data stage (automatically by create extension) early
>> and thus has wrong behavior when uploaded data in the data stage (lead
>> to duplication of primary key).
>
> pg_dump does not like to editorialize on the contents of extensions.
> It just does CREATE EXTENSION and doesn't inquire into what's in
> them. I'd argue that if you need triggers like this, maybe you
> should rethink your data model.
>
> regards, tom lane

Okey, as I see
https://www.postgresql.org/docs/17/extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES
Cite:
More complicated situations, such as initially-provided rows that
might be modified by users, can be handled by creating triggers on the
configuration table to ensure that modified rows are marked correctly.

So a trigger is permitted inside an extension. But usually trigger must
not be fired when pg_dump load data. May be it is hard for pg_dump to
reorder statements inside an extension. May be better just set
session_replication_role=replica by pg_dump and pg_restore? (To disable
triggers).

--
Olleg