I created a trigger on a table and pg_trigger table will not show it as
enabled. Here is the illustration.

news_archive=# create trigger news_segments_ins
before insert on news.news_segments
for each row execute procedure news_segments_insert_trigger();
CREATE TRIGGER

news_archive=# alter table news.news_segments enable trigger
news_segments_ins;
ALTER TABLE
news_archive=# select tgenabled from pg_trigger where
tgname='news_segments_ins';
tgenabled
-----------
O
(1 row)


The trigger is functional. This is a partitioning trigger which look like
this:
CREATE OR REPLACE FUNCTION news_segments_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.created_at >= DATE '2009-09-01' AND
NEW.created_at < DATE '2009-10-01' ) THEN
INSERT INTO news.news_seg_data0557 VALUES (NEW.*);
ELSIF ( NEW.created_at >= DATE '2009-10-01' AND
NEW.created_at < DATE '2009-11-01' ) THEN
INSERT INTO news.news_seg_data0558 VALUES (NEW.*);
ELSIF ( NEW.created_at >= DATE '2009-11-01' AND
NEW.created_at < DATE '2009-12-01' ) THEN
INSERT INTO news.news_seg_data0559 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the
news_segments_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

I verified the functionality by inserting rows into the table and
checking that the row went to the right place. The trigger works but the
pg_trigger table shows it as not being enabled.



--
http://mgogala.byethost5.com

Mladen Gogala wrote on 26.01.2010 17:15:
This is not the right place to submit an issue report for Postgres.

You have to take this to the mailing list (-general) or use the bug submission form on the homepage:

http://www.postgresql.org/support/submitbug

Thomas

On Tue, 26 Jan 2010 20:35:21 +0100, Thomas Kellerer wrote:

Thanks. I was only interested whether someone else has experienced the
same thing.



--
http://mgogala.byethost5.com

Mladen Gogala wrote:
I guess there is a misunderstanding.

The result of your last query is the uppercase letter "O"
and not the number 0. The documentation of pg_trigger says:

Controls in which session_replication_role modes the trigger fires.
O = trigger fires in "origin" and "local" modes,
D = trigger is disabled,
R = trigger fires in "replica" mode,
A = trigger fires always.

So "O" means that the trigger is enabled, and everything is as
it should be.

See http://www.postgresql.org/docs/8.3/s...ltertable.html
for a discussion of the meaning of "origin", "local", "replica"
and "always". Also, see the documentation of the
session_replication_role parameter.
From what I understand, "origin" and "local" currently mean the
same thing.

Yours,
Laurenz Albe