Re: A trigger in an extension

On 20.02.2025 22:30, Tom Lane wrote:
> Olleg Samoylov <splarv@ya.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