agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedA trigger in an extension
4+ messages / 3 participants
[nested] [flat]
* A trigger in an extension
@ 2025-02-20 19:23 Olleg Samoylov <splarv@ya.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Olleg Samoylov @ 2025-02-20 19:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A trigger in an extension
@ 2025-02-20 19:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Olleg Samoylov <splarv@ya.ru>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2025-02-20 19:30 UTC (permalink / raw)
To: Olleg Samoylov <splarv@ya.ru>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A trigger in an extension
@ 2025-02-20 19:43 Олег Самойлов <splarv@ya.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Олег Самойлов @ 2025-02-20 19:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
<div>Olleg Samoylov <<a href="mailto:splarv@ya.ru" rel="noopener noreferrer">splarv@ya.ru</a>> writes:</div><blockquote><blockquote> I have the extension pgpro_scheduler. In the exception there are two<br /> tables and the trigger on one of them that write to the other. I was<br /> surprised but when I load dump created by pg_dump this trigger is<br /> created in the pre-data stage (automatically by create extension) early<br /> and thus has wrong behavior when uploaded data in the data stage (lead<br /> to duplication of primary key).</blockquote><p><br />pg_dump does not like to editorialize on the contents of extensions.<br />It just does CREATE EXTENSION and doesn't inquire into what's in<br />them.</p></blockquote><div> </div><div>I saw this.</div><div> </div><blockquote><p>I'd argue that if you need triggers like this, maybe you should rethink your data model.<br /> regards, tom lane</p></blockquote><div> </div><div>This is not my extension. This is foreign extension that emulate sheduler or cron behaviour similar to oracle. Two tables: the second is history table where logged changes of the first cron table.</div><div> </div><div>The question is: is this somehow violate documented rules for extensions, so I can point the extension developers to the documentation? May be triggers is explicitly forbidden for extensions? Or may be need to document this?</div><blockquote> </blockquote>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A trigger in an extension
@ 2025-02-21 06:13 Olleg Samoylov <splarv@ya.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Olleg Samoylov @ 2025-02-21 06:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-02-21 06:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-20 19:23 A trigger in an extension Olleg Samoylov <splarv@ya.ru>
2025-02-20 19:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-20 19:43 ` Олег Самойлов <splarv@ya.ru>
2025-02-21 06:13 ` Olleg Samoylov <splarv@ya.ru>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox