Wanted: ALTER TRIGGER ... OWNED BY EXTENSION
An extension <http://pgxn.org/dist/cyanaudit> I'm working on dynamically
creates trigger functions and installs them on tables in public. The
triggers are automatically created when one of the extension's config
tables is populated.
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.
What ends up happening is pg_restore throws a whole lot of errors saying
that the function referenced by the CREATE TRIGGER command does not exist.
However, the function and trigger are actually created later on once the
config table is populated.
Using pg_restore without -j is a workaround, but I'd really like to be able
to mark my extension's triggers as owned by the extension, so that these
errors will not show up when restoring.
Is there a better workaround that I'm not aware of?
Thanks.
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Mar 31, 2014 at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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)?
There is a trigger function on the extension's config table that creates
the trigger functions and installs them (both from the same function). I am
about to try playing with pg_depend to see if it has the desired effect.
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
On Mon, Mar 31, 2014 at 4:32 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
There is a trigger function on the extension's config table that creates
the trigger functions and installs them (both from the same function). I am
about to try playing with pg_depend to see if it has the desired effect.
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.
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Mar 31, 2014 at 5:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Basically what you need to end up with is
* trigger function has a membership dependency on the extension
Yes, the dependency is set up when the trigger function is dynamically
created by using ALTER EXTENSION ... ADD FUNCTION
* pg_trigger row has a normal dependency on the trigger function it uses
Aha, this is not present. I think it is due to some migration magic I did a
while ago.
Is there a way to clean up the dependencies, or at least list out the
dependencies that seem suspicious?
* pg_trigger row has an auto dependency on the table it's for
This is present.
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.
I would love for DROP EXTENSION to shut up and drop the triggers, but I'm
not sure how to accomplish that without manually manipulating pg_depend
(and even then I'm not sure if that'll do it). Suggestions welcome.
Thanks.
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle