agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Olleg Samoylov <splarv@ya.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: A trigger in an extension
Date: Fri, 21 Feb 2025 09:13:26 +0300
Message-ID: <f7db41f4-80c5-4e4d-8380-446101ec684e@ya.ru> (raw)
In-Reply-To: <2790099.1740079810@sss.pgh.pa.us>
References: <40129c90-138c-4331-a3d6-c04158cb91e0@ya.ru>
<2790099.1740079810@sss.pgh.pa.us>
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
view thread (4+ messages)
Message-ID: <f7db41f4-80c5-4e4d-8380-446101ec684e@ya.ru>
Permalink: ../f7db41f4-80c5-4e4d-8380-446101ec684e@ya.ru/
Also on: postgresql.org/message-id/f7db41f4-80c5-4e4d-8380-446101ec684e@ya.ru
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: splarv@ya.ru, tgl@sss.pgh.pa.us, pgsql-admin@lists.postgresql.org
Subject: Re: A trigger in an extension
In-Reply-To: <f7db41f4-80c5-4e4d-8380-446101ec684e@ya.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox