Thread: Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

Moshe Jacobson wrote:

> Even though I mark the trigger *functions *as owned by my extension, the
> trigger definitions themselves are still dumped by pg_dump and restored by
> pg_restore. This is a problem when pg_restore is using parallelism (-j),
> and one thread starts issuing CREATE TRIGGER commands before the other
> thread has finished populating the config table.

ISTM that the usual locution for this is ALTER EXTENSION .. ADD.  You
could test whether this is going to work by manually inserting rows in
pg_depend.

It seems strange to me that the created trigger is part of the extension,
however.  Maybe it's the right fix, but hmm.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Moshe Jacobson wrote:
>> Even though I mark the trigger *functions *as owned by my extension, the
>> trigger definitions themselves are still dumped by pg_dump and restored by
>> pg_restore. This is a problem when pg_restore is using parallelism (-j),
>> and one thread starts issuing CREATE TRIGGER commands before the other
>> thread has finished populating the config table.

> ISTM that the usual locution for this is ALTER EXTENSION .. ADD.  You
> could test whether this is going to work by manually inserting rows in
> pg_depend.

> It seems strange to me that the created trigger is part of the extension,
> however.  Maybe it's the right fix, but hmm.

I'm suspicious that the problem is exactly lack of pg_depend records ---
pg_dump/pg_restore relies on those for correct ordering of parallel
operations.  What method are you using to create these triggers (not
the functions, the per-table pg_trigger records)?

            regards, tom lane

Moshe Jacobson <moshe@neadwerx.com> writes:
> I've added the requisite rows to pg_depend, and I know it was correct
> because \dx+ cyanaudit (my extension) now shows all of the triggers.

> However, pg_dump still dumps them, and pg_restore still restores them,
> causing the same errors as I had before.

I don't think pg_dump believes that pg_trigger rows can belong to an
extension; and I'm dubious of the concept too.  What I was wondering about
was whether those rows had proper dependencies on (a) the functions and
(b) their owning tables.

Basically what you need to end up with is

* trigger function has a membership dependency on the extension

* pg_trigger row has a normal dependency on the trigger function it uses

* pg_trigger row has an auto dependency on the table it's for

If you're using SQL commands to create the trigger then I'd expect the
latter two to be handled automatically; but it sorta sounds like you're
doing something pretty low-level and perhaps omitting these steps.

Note: depending on what it is you're trying to accomplish, it might be
saner for the pg_trigger rows to have auto dependencies on their trigger
functions.  Depends whether you'd like DROP EXTENSION to complain or just
shut up and drop the triggers.

            regards, tom lane