Running query without trigger?

Good morning

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of hamann.w@t-
online.de
Sent: Samstag, 9. Juli 2016 08:20
To: pgsql-general@postgresql.org
Subject: [GENERAL] Running query without trigger?

Hi,

a table is associated with a trigger for normal use.
An admin (someone with ALTER privilege) can disable tthe trigger, run some bulk update, and then re-enable it. This
means, however, that normal user activity has to be locked out.

There are two possible scenarios: the bulk update would not cause trigger activity at all, because of the values and
columns involved.
or - the bulk update is followed by another bulk transaction that is equivalent to trigger invocations per row.
At least in the first case, running this particular query without triggering the trigger, but normal activity still
going on, would be really great Is there a way to achieve this?

In general it helps better if you provide the version of PostgreSQL that you are using.

If you have a way to identify the bulk update from a record field, e.g. a timestamp or something like this, you may use WHEN to exclude them from firing the trigger. I am not sure if it is possible to use a condition other than using the fields of the old or new record.

https://www.postgresql.org/docs/current/static/sql-createtrigger.html

Regards
Charles

Regards
Wolfgang Hamann

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

hamann.w@t-online.de wrote:

Hi,

a table is associated with a trigger for normal use.
An admin (someone with ALTER privilege) can disable tthe trigger, run some bulk update,
and then re-enable it. This means, however, that normal user activity has to be locked out.

There are two possible scenarios: the bulk update would not cause trigger activity at all,
because of the values and columns involved.
or - the bulk update is followed by another bulk transaction that is equivalent to trigger
invocations per row.
At least in the first case, running this particular query without triggering the trigger,
but normal activity still going on, would be really great
Is there a way to achieve this?

For UPDATE events, it is possible to specify a list of columns using this syntax:

UPDATE OF column_name1 [, column_name2 ... ]

The trigger will only fire if at least one of the listed columns is mentioned as a target of the
UPDATE command.

So, if you use that optional syntax, then since as you described it, the columns in your update
statement would not be included in the "OF" list and hence not fire this update trigger. Meanwhile,
normal updates, presumably which did involve columns in the "OF" list, would appropriately fire the
trigger as required.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2016-07-09 08:20, hamann.w@t-online.de wrote:

Hi,

a table is associated with a trigger for normal use.
An admin (someone with ALTER privilege) can disable tthe trigger, run some bulk update,
and then re-enable it. This means, however, that normal user activity has to be locked out.

There are two possible scenarios: the bulk update would not cause trigger activity at all,
because of the values and columns involved.
or - the bulk update is followed by another bulk transaction that is equivalent to trigger
invocations per row.
At least in the first case, running this particular query without triggering the trigger,
but normal activity still going on, would be really great
Is there a way to achieve this?

Regards
Wolfgang Hamann

Well for temporary disabling triggers the easiest is to run:
SET session_replication_role = replica;
UPDATE ...
SET session_replication_role = DEFAULT;

This only affects the current session i.e. you and not any other
sessions which is what you seemed to require. All this assuming you run
a supported version of postgres

Regards,
Christian Elmerot, Systems Engineer One.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2016-07-09 08:20, hamann.w@t-online.de wrote:

Hi,

a table is associated with a trigger for normal use.
An admin (someone with ALTER privilege) can disable tthe trigger, run some bulk update,
and then re-enable it. This means, however, that normal user activity has to be locked out.

There are two possible scenarios: the bulk update would not cause trigger activity at all,
because of the values and columns involved.
or - the bulk update is followed by another bulk transaction that is equivalent to trigger
invocations per row.
At least in the first case, running this particular query without triggering the trigger,
but normal activity still going on, would be really great
Is there a way to achieve this?

Regards
Wolfgang Hamann

Well for temporary disabling triggers the easiest is to run:
SET session_replication_role = replica;
UPDATE ...
SET session_replication_role = DEFAULT;

This only affects the current session i.e. you and not any other
sessions which is what you seemed to require. All this assuming you run
a supported version of postgres

Regards,
Christian Elmerot, Systems Engineer One.com

Hello Christian,

thanks for the tip.
Out of the suggestions I got, specifying a list of relevant columns seems to be the best one
for my specific case. I believe your suggestion will be helpful in other - more ad hoc -
situations

Regards
Wolfgang

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2016-07-09 08:20, hamann.w@t-online.de wrote:

Hi,

a table is associated with a trigger for normal use.
An admin (someone with ALTER privilege) can disable tthe trigger, run some bulk update,
and then re-enable it. This means, however, that normal user activity has to be locked out.

There are two possible scenarios: the bulk update would not cause trigger activity at all,
because of the values and columns involved.
or - the bulk update is followed by another bulk transaction that is equivalent to trigger
invocations per row.
At least in the first case, running this particular query without triggering the trigger,
but normal activity still going on, would be really great
Is there a way to achieve this?

Regards
Wolfgang Hamann

Well for temporary disabling triggers the easiest is to run:
SET session_replication_role = replica;
UPDATE ...
SET session_replication_role = DEFAULT;

This only affects the current session i.e. you and not any other
sessions which is what you seemed to require. All this assuming you run
a supported version of postgres

Regards,
Christian Elmerot, Systems Engineer One.com

Hello Christian,

thanks for the tip.
Out of the suggestions I got, specifying a list of relevant columns seems to be the best one
for my specific case. I believe your suggestion will be helpful in other - more ad hoc -
situations

Regards
Wolfgang

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general