Обсуждение: How can I tell if pg_restore is running?
My extension has a config table that is dumped by pg_dump and populated by pg_restore.
However, this table has triggers on it that I would like not to do anything if the table is being populated by pg_restore. I want the triggers to operate only if the user is manipulating the table directly after the database has been restored and is running.
Is there a way for my extension's trigger functions to return immediately when triggered by pg_restore?
Thanks.
Moshe Jacobson
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
"Quality is not an act, it is a habit." -- Aristotle
On Tue, Jun 10, 2014 at 10:02 AM, Moshe Jacobson <moshe@neadwerx.com> wrote:
My extension has a config table that is dumped by pg_dump and populated by pg_restore.However, this table has triggers on it that I would like not to do anything if the table is being populated by pg_restore. I want the triggers to operate only if the user is manipulating the table directly after the database has been restored and is running.Is there a way for my extension's trigger functions to return immediately when triggered by pg_restore?Thanks.
Moshe JacobsonManager of Systems Engineering, Nead Werx Inc.2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
I'd look into advisory locks.
http://www.postgresql.org/docs/9.3/static/explicit-locking.html#ADVISORY-LOCKS
http://www.postgresql.org/docs/9.3/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
You'd probably have to wrap your pg_dump/restore in some sort of script that can call the advisory locks, but I don't see why it wouldn't work. Then you'd have your triggers check if the advisory lock is held and skip whatever they do if so.http://www.postgresql.org/docs/9.3/static/explicit-locking.html#ADVISORY-LOCKS
http://www.postgresql.org/docs/9.3/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
> My extension has a config table that is dumped by pg_dump and
> populated by pg_restore.
> However, this table has triggers on it that I would like not to do
> anything if the table is being populated by pg_restore. I want the
> triggers to operate only if the user is manipulating the table
> directly after the database has been restored and is running.
>
> Is there a way for my extension's trigger functions to return
> immediately when triggered by pg_restore?
>
>
If this is a data only restore, and you are running as a superuser, you can not simply specify pg_restore --disable-triggers ?
=============================================
Romax Technology Limited
A limited company registered in England and Wales.
Registered office:
Romax Technology Centre
University of Nottingham Innovation Park
Triumph Road
Nottingham
NG7 2TU
United Kingdom
Registration Number: 2345696
VAT Number: 526 246 746
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================
================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
> populated by pg_restore.
> However, this table has triggers on it that I would like not to do
> anything if the table is being populated by pg_restore. I want the
> triggers to operate only if the user is manipulating the table
> directly after the database has been restored and is running.
>
> Is there a way for my extension's trigger functions to return
> immediately when triggered by pg_restore?
>
>
If this is a data only restore, and you are running as a superuser, you can not simply specify pg_restore --disable-triggers ?
=============================================
Romax Technology Limited
A limited company registered in England and Wales.
Registered office:
Romax Technology Centre
University of Nottingham Innovation Park
Triumph Road
Nottingham
NG7 2TU
United Kingdom
Registration Number: 2345696
VAT Number: 526 246 746
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================
================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
Hi Moshe: On Tue, Jun 10, 2014 at 4:02 PM, Moshe Jacobson <moshe@neadwerx.com> wrote: > My extension has a config table that is dumped by pg_dump and populated by > pg_restore. .... > Is there a way for my extension's trigger functions to return immediately > when triggered by pg_restore? Is there any reason to avoid pg_restore --disable-triggers ? Francisco Olarte.
On Tue, Jun 10, 2014 at 10:57 AM, Francisco Olarte <folarte@peoplecall.com> wrote:
Is there any reason to avoid pg_restore --disable-triggers ?
Yes, this extension needs to work with everyone's normal restore process. Otherwise I would have to ask everyone who used my extension to modify their pg_restore command.
This also means that I can't rely on a wrapper to pg_restore, as Keith suggested earlier.
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
"Quality is not an act, it is a habit." -- Aristotle
If you can't use a wrapper script and you can't use that flag, I'm not sure how you're going to manage to keep this a process that's simple for a typical end user to manage. From what I know, there's nothing internally on the data loading process that pg_restore uses that would guarantee you that what is loading the data is pg_restore. Might need to rethink your strategy here.
On Wed, Jun 11, 2014 at 2:18 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
Yes, this extension needs to work with everyone's normal restore process. Otherwise I would have to ask everyone who used my extension to modify their pg_restore command.On Tue, Jun 10, 2014 at 10:57 AM, Francisco Olarte <folarte@peoplecall.com> wrote:Is there any reason to avoid pg_restore --disable-triggers ?This also means that I can't rely on a wrapper to pg_restore, as Keith suggested earlier.Moshe JacobsonManager of Systems Engineering, Nead Werx Inc.2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle