pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddisabling triggers
8+ messages / 4 participants
[nested] [flat]
* disabling triggers
@ 2003-06-17 17:34 mallah@trade-india.com
0 siblings, 1 reply; 8+ messages in thread
From: mallah@trade-india.com @ 2003-06-17 17:34 UTC (permalink / raw)
To: pgsql-sql
Hi,
Is setting reltriggers=0 on pg_class an accepatble way of
disabling triggers on a table temporarily?
secondly is it worth having commands like
alter trigger <triggername> inactive;
alter trigger <triggername> active ;
i feel such commands exists on Oracle.
sometime we need to bulk update tables from backdoor
and we feel the trigger execution to be unneccessary.
regds
mallah.
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: disabling triggers
@ 2003-06-17 17:58 Josh Berkus <josh@agliodbs.com>
parent: mallah@trade-india.com
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2003-06-17 17:58 UTC (permalink / raw)
To: mallah@trade-india.com; pgsql-sql
Mallah,
> Is setting reltriggers=0 on pg_class an accepatble way of
> disabling triggers on a table temporarily?
Yes. pg_dump does this.
> secondly is it worth having commands like
> alter trigger <triggername> inactive;
> alter trigger <triggername> active ;
> i feel such commands exists on Oracle.
No, since DROP TRIGGER ... CREATE TRIGGER does the same thing in Postgres.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: disabling triggers
@ 2003-06-17 18:17 mallah@trade-india.com
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: mallah@trade-india.com @ 2003-06-17 18:17 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
> Mallah,
>
>> Is setting reltriggers=0 on pg_class an accepatble way of
>> disabling triggers on a table temporarily?
Ok , but someone on list was scary few months back.
>
> Yes. pg_dump does this.
>
>> secondly is it worth having commands like
>> alter trigger <triggername> inactive;
>> alter trigger <triggername> active ;
>> i feel such commands exists on Oracle.
>
> No, since DROP TRIGGER ... CREATE TRIGGER does the same thing in Postgres.
Agreed , but there is no easy way to view current trigger defination of
existing triggers before dropping from psql .
One has to go the long way of pg_dump -s -t tabname to view a list.
regds
mallah.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: disabling triggers
@ 2003-06-17 18:31 Josh Berkus <josh@agliodbs.com>
parent: mallah@trade-india.com
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2003-06-17 18:31 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
Mallah,
> >> Is setting reltriggers=0 on pg_class an accepatble way of
> >> disabling triggers on a table temporarily?
>
> Ok , but someone on list was scary few months back.
I've done it many times without a problem. The trick is re-setting the
triggers to the correct number when you're done. See the scripts that
pg_dump -a creates for a good example.
> Agreed , but there is no easy way to view current trigger defination of
> existing triggers before dropping from psql .
So? Create a perl script. It can:
1) query the system tables to find out the trigger definintion
(you'll need pg_trigger, pg_proc, and pg_type)
2) generate a script to restore all the triggers to be used later;
3) drop all the triggers
Of course, setting reltriggers=0 is probably a lot easier.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: disabling triggers
@ 2003-06-17 18:47 mallah@trade-india.com
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: mallah@trade-india.com @ 2003-06-17 18:47 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
> Mallah,
>
>> >> Is setting reltriggers=0 on pg_class an accepatble way of
>> >> disabling triggers on a table temporarily?
>>
>> Ok , but someone on list was scary few months back.
>
> I've done it many times without a problem. The trick is re-setting the triggers to the
> correct number when you're done. See the scripts that pg_dump -a creates for a good example.
>
>> Agreed , but there is no easy way to view current trigger defination of existing triggers
>> before dropping from psql .
>
> So? Create a perl script. It can:
> 1) query the system tables to find out the trigger definintion
> (you'll need pg_trigger, pg_proc, and pg_type)
> 2) generate a script to restore all the triggers to be used later; 3) drop all the triggers
>
> Of course, setting reltriggers=0 is probably a lot easier.
in past we tried creating a plpgsql function that takes relname and schema
and try setting reltriggers=0 on disabling and = (select count(*) from pg_trigger
for that relname and schema) on enabling .
we got stuck somewhere and abandoned it , do u think above approach
would work in principle ?
i will retry it tommorw though and post the results.
regds
mallah.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)--------------------------- TIP 3: if
> posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your message can get through
> to the mailing list cleanly
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: disabling triggers
@ 2003-06-17 18:49 Josh Berkus <josh@agliodbs.com>
parent: mallah@trade-india.com
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2003-06-17 18:49 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
Mallah,
> in past we tried creating a plpgsql function that takes relname and schema
> and try setting reltriggers=0 on disabling and = (select count(*) from
pg_trigger
> for that relname and schema) on enabling .
>
> we got stuck somewhere and abandoned it , do u think above approach
> would work in principle ?
Yes. As I said, I've used it before.
An additional safeguard you can use is enclosing everything in a transaction,
that is:
BEGIN
disable triggers
load data
enable triggers
END
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: disabling triggers
@ 2003-09-01 17:57 weigelt@metux.de
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: weigelt@metux.de @ 2003-09-01 17:57 UTC (permalink / raw)
To: pgsql-sql
On Tue, Jun 17, 2003 at 11:49:44AM -0700, Josh Berkus wrote:
<big_snip>
I also need something like that for my replication stuff:
When data from another node comes it, it should be written
directly to the tables, without any (unwanted) triggers firering.
On the other hand, in "normal" mode, also some missing fields
should be filled on create or update.
Perhaps some more details:
All tables have this layout:
inode_id int4
attr char
mtime timestamp
...
On INSERT an missing (NULL) inode_id should be taken from sequence
_inode_seq, attr can be set to an default value (currently not important)
and an NULL mtime should be replaced by current_timestamp().
When UPDATE-ing, the mtime should also be set to current_timestamp()
if not given.
But in the "raw" mode (when data from neighbours come in), the mtime
field may _not_ be touched (since it would produce an loop!).
Currently this is done by the middleware, but I'd like to have it
directly in the RDBMS. How could this be solved ?
regards
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT services
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: disabling triggers
@ 2003-09-01 20:35 chester c young <chestercyoung@yahoo.com>
parent: weigelt@metux.de
0 siblings, 0 replies; 8+ messages in thread
From: chester c young @ 2003-09-01 20:35 UTC (permalink / raw)
To: weigelt@metux.de; pgsql-sql
--- weigelt@metux.de wrote:
> On Tue, Jun 17, 2003 at 11:49:44AM -0700, Josh Berkus wrote:
>
> <big_snip>
>
> I also need something like that for my replication stuff:
>
> When data from another node comes it, it should be written
> directly to the tables, without any (unwanted) triggers firering.
>
> On the other hand, in "normal" mode, also some missing fields
> should be filled on create or update.
>
I have done this three ways:
1. have all client DML go through views and at the view level do all
checking etc.
+ very robust, very secure
- lots of work
2. have replication updates use the "replicate" user - in triggers, do
nothing if user is replicate, otherwise work as normal
+ very easy to do
- triggers fire always, even when doing nothing
3. have replicate triggers write data file for import directly into the
postgresql engine
+ transactions smaller, less rollback
+ can replicate to different databases, over net, etc
- lots of work (although easy to write trigger generator)
- difficult failure senarios
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-09-01 20:35 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-17 17:34 disabling triggers mallah@trade-india.com
2003-06-17 17:58 ` Josh Berkus <josh@agliodbs.com>
2003-06-17 18:17 ` mallah@trade-india.com
2003-06-17 18:31 ` Josh Berkus <josh@agliodbs.com>
2003-06-17 18:47 ` mallah@trade-india.com
2003-06-17 18:49 ` Josh Berkus <josh@agliodbs.com>
2003-09-01 17:57 ` weigelt@metux.de
2003-09-01 20:35 ` chester c young <chestercyoung@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox