Message-ID: <f7db41f4-80c5-4e4d-8380-446101ec684e@ya.ru>
Date: Fri, 21 Feb 2025 09:13:26 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: A trigger in an extension
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org
References: <40129c90-138c-4331-a3d6-c04158cb91e0@ya.ru>
 <2790099.1740079810@sss.pgh.pa.us>
Content-Language: ru, en-US
From: Olleg Samoylov <splarv@ya.ru>
In-Reply-To: <2790099.1740079810@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/f7db41f4-80c5-4e4d-8380-446101ec684e%40ya.ru>
Precedence: bulk

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