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