Thread: Change the default [tgenabled] for new "internal" triggers ?

Today I learned that . . .
the firing of even "internally generated constraint triggers" is affected by the value of [session_replication_role].

Sadly, I had previously assumed that such "internal" triggers did not care about [s_r_r].

Also learned that . . .
when a FK constraint gets made, its pg_trigger.tgenabled = ' O'.

QUESTION:
Is the a way to configure Postgres such that tgenabled = ' A' automatically when the FK constraint gets made ?

Or must I !un-automatedly! do a . . .
  alter table MYTABLE    ENABLE ALWAYS TRIGGER "RI_ConstraintTrigger_18656"
whenever
somebody makes a FK ?!?

Thanks,
-dvs-

<david.sahagian@emc.com> writes:
> Is the a way to configure Postgres such that tgenabled = ' A' automatically when the FK constraint gets made ?

No.  Why do you think that would be a good idea?  ISTM it'd lead to the
action being taken twice on the slave.

            regards, tom lane

Scenario: (not slony, it is home-grown replication)

A change on the Primary db is Captured and then Propagated to the Secondary db.
Then the change is Applied to the Secondary db, with [session_replication_role] = 'replica'.

I agree that I don't want my "user triggers" to fire as part of the Apply.

But my email was about the "internally generated constraint triggers"
which implement checking for Foreign Key Constraint violations.

It is that checking that I want to be done on the Secondary.
Should I not want such checking to be done ?

Thanks,
-dvs-

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, March 23, 2012 8:35 PM
To: Sahagian, David
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Change the default [tgenabled] for new "internal" triggers ?

<david.sahagian@emc.com> writes:
> Is the a way to configure Postgres such that tgenabled = ' A' automatically when the FK constraint gets made ?

No.  Why do you think that would be a good idea?  ISTM it'd lead to the
action being taken twice on the slave.

            regards, tom lane

<david.sahagian@emc.com> writes:
> But my email was about the "internally generated constraint triggers"
> which implement checking for Foreign Key Constraint violations.

Yeah ...

> It is that checking that I want to be done on the Secondary.
> Should I not want such checking to be done ?

What for, when it was already checked on the master?  Perhaps more to
the point, FK triggers can make updates not only checks.

            regards, tom lane