Thread: A trigger in an extension

Hi all.

Server version is 16.6.

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

I do workaround by creating superuser with 
session_replication_role=replica. But what was wrong? Is this bug inside 
pg_dump or pg_restore or extension pgpro_scheduler? Or something other?
-- 
Olleg

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

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